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, 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 by 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 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 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 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 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 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 PostgreSQL server
Below is the syntax and example to check the all sequences from the database server.
Syntax
# \ds OR \ds+
Example
\ds
\ds+
10. List all views from PostgreSQL server
Below is the syntax and example to check the all views from the database server.
Syntax
# \dv OR \dv+
Example
\dv
\dv+
11. List extensions from 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 of 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
This is a guide to PostgreSQL Cheat Sheet. Here we discuss the basic commands of the cheat sheet for an overview of the PostgreSQL database with respective query examples. You may also have a look at the following articles to learn more –
