Most Useful PostgreSQL Commands

This article describes you a list of common psql commands that helps you query data from PostgreSQL database server faster and more effective.

1. Access the PostgreSQL server from psql with a specific user like "corpus":

psql -U corpus
Password for user corpus:
psql (10.4)
Type "help" for help.

postgres=>

 2. If you want to connect a specific database like corpus from "postgres" user by psql then use the following command:

\c corpus
You are now connected to database "corpus" as user "postgres":

3. To retrieve the current version of PostgreSQL database server then use the version() function as follow by connecting psql:

select version();

4. To see the list of the all available databases then use the following command by connecting psql:

\l

If you want to see some more columns details then use the following command by connecting psql:

\l+

5. To see the result in proper format then use the following command by connecting psql:

\pset format wrapped \pset linestyle unicod \pset columns 180;

6. To see the list of all the schemas then use the following command by connecting psql:

\dn

7. To see the list of all the users then use the following command by connecting psql:

\du

8. To see the list of all the views then use the following command by connecting psql:

\dv

9. If you want to see a stored procedure or function code then use the following command by connecting psql:

\pset format wrapped \pset linestyle unicod \pset columns 180;
\df+ <<function name>>;

for example: 
\df+ pg_stat_statements;

10. Show query output in the pretty-format then use the following command by connecting psql:

\x
Expanded display is on.
\df+ pg_stat_statements;

11. To see the list of all available tables then use the following command by connecting psql:

\dt

Want to see some more columns details then use the following command by connecting psql:

\dt+

12. To describe a table such as a column, type, modifiers of columns, etc. then use the following command by connecting psql with the database.

\d  

13. To see the list of all available schema's then use the following command by connecting psql:

df 

Want to see some more columns details then use the following command by connecting psql:

df+

14. To see the list of all the users and their roles then use the following command by connecting psql:

\du+

15. If you want to save time typing the previous command again and see the result of old command then use the following command to execute the previous command by connecting psql:

\g

16. To display commands history then use the following command by connecting psql:

\s my_all_commands

18. To turn on query execution time then use the following command by connecting psql:

\timing
Timing is on.

19. If you want to edit any command in your own editor. It is very handy, if you can type the command in your favorite editor. After issuing the command, "psql" will open the text editor defined by your EDITOR environment variable and place the most recent command that you entered in "psql" into the editor:

 \e

20. Switch output options: "psql" supports some types of output format and allows you to customize how the output is formatted on fly then use the following command by connecting psql:

\a : command switches from aligned to non-aligned column output.
\H : command formats the output to HTML format.

\a

\H

21. To create a new user then use the following command by connecting psql:

CREATE USER << user name >> WITH PASSWORD ' << password name >>';

If you want to alter the password then use the following command by connecting psql:

ALTER USER  << user name >> WITH PASSWORD ' << new password name >>';

22. If you want to save psql command result into any file then use the following method by connecting psql:

\o output.txt
select * from pg_stat_activity;
\q

Above command, will create output.txt file in the /home directory of postgres with the result of the query "select * from pg_stat_activity".

23. If you wants to quit from "postgres" then use the following command by connecting psql:

\q