Updated May 22, 2023
Introduction to PostgreSQL Cheat Sheet
PostgreSQL cheat sheet is defined as how to use the basic commands of PostgreSQL, we have created our basic cheat sheet of PostgreSQL to take a quick overview of the PostgreSQL database. We can create a cheat sheet like how to list the databases, how to list the tables, how to list all users, how to create a database, how to drop the database, and how to drop the table. We can also check how to access a specific database by a specific user.
Cheat Sheet of PostgreSQL
Below are the basic commands of the cheat sheet for an overview of the PostgreSQL database.
1. Access the PostgreSQL server using psql command
Below is the syntax and example to access the database server using the psql command.
Syntax
psql –U [username] –W [password] –d [database_name]Example
psql -U postgres -W -d testing
2. Connect to the specific database
Below is the syntax and example of connecting to the specific database. We have connected to the testing database.
Syntax
\c name_of_database;Example
\c testing;
3. Check the version of the PostgreSQL database
Below is the syntax and example of checking the version of PostgreSQL.
Syntax
select version();Example
select version();
4. List all databases from the PostgreSQL server
Below is the syntax and example to check all databases from the database server.
Syntax
# \l OR \l+Example
\l+
5. List all schema from the PostgreSQL server
Below is the syntax and example to check all schema from the database server.
Syntax
# \dn OR \dn+Example
\dn
\dn+
6. List all tablespaces from the PostgreSQL server
Below is the syntax and example to check all tablespaces from the database server.
Syntax
# \db OR \db+Example
\db
\db+
7. List all indexes from the PostgreSQL server
Below is the syntax and example to check all indexes from the database server.
Syntax
# \di OR \di+Example
\di
\di+
8. List all tables from a specific database
Below is the syntax and example to check all tables from a specific database.
Syntax
# \dt OR \dt+Example
\dt
\dt+
9. List all sequences from the PostgreSQL server
Below is the syntax and example to check all sequences from the database server.
Syntax
# \ds OR \ds+Example
\ds
\ds+
10. List all views from the PostgreSQL server
Below is the syntax and example to check all views from the database server.
Syntax
# \dv OR \dv+Example
\dv
\dv+
11. List extensions from the PostgreSQL server
Below is the syntax and example to check the extensions from the database server.
Syntax
# \dx OR \dx+Example
\dx
\dx+
12. Show detailed information on a database table
Below is the syntax and example to show detailed information about the database table.
Syntax
\d name_of_table;
\d+ name_of_table;Example
\d stud1;
\d+ stud1;
13. Create a new user in PostgreSQL
Below is the example and syntax to create a new user in PostgreSQL. We have created a test user.
Syntax
Create user name_of_user;Example
Create user test password 'abc@123';
14. Create a new database in PostgreSQL
Below is the example and syntax to create a new database in PostgreSQL. We have created a test database.
Syntax
Create database name_of_database;Example
Create database test;
15. Drop database in PostgreSQL
Below is the example and syntax to drop the database in PostgreSQL. We have deleted the test database.
Syntax
Drop database name_of_database;Example
Drop database test;
16. Create a new table in PostgreSQL
Below is the example and syntax to create a new table in PostgreSQL. We have created a test table.
Syntax
Create table name_of_table (name_of_column1 data_type, name_of_column1 data_type,name_of_column1 data_type, …, name_of_columnN data_type,);Example
Create table test (id int, name varchar, address varchar, phone int);
17. Create a new view in PostgreSQL
Below is the example and syntax to create a new view in PostgreSQL. We have created a test view.
Syntax
Create view name_of_viewAS select column_name1, column_name2, column_name3, …, column_nameN from table_name;Example
Create view test AS select * from stud1;
18. Create a new index in PostgreSQL
Below is the example and syntax to create a new index in PostgreSQL. We have created a test_idx index.
Syntax
Create index name_of_index AS on name_of_table (name_of_column);Example
create index test_idx on stud1 (id);
19. Drop index in PostgreSQL
Below is the example and syntax to drop the index in PostgreSQL. We have to delete the test_idx index.
Syntax
Drop index name_of_index;Example
Drop index test_idx;
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL Cheat Sheet” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
