psql 常用命令

1) psql common commands

1-1) Connect to postgresql database

psql -d dbname -U username -h host "sslmode=require" -W

1-2) Switch connection to a new database

if you omit the user parameter, the current user is assumed.

\c dbname username

1-3) List available databases

To list all database in the current PostgreSQL database server

\list 
\l      # shortcut

1-4) List available tables

To list all tables in the current database

\dt

1-5) Describe a table

To describe a table such as a column, type, modifiers of columns, etc…

\d table_name

1-6) List available schema

To list all schema of the currently connected database,

\dn

1-7) List available functions

To list all functions in the current database

\df

1-8) List available views

To list all views in the current database

\dv

1-9) List users and their roles

To list all users and their assign roles

\du

1-10) Execution the previous command

To retrieve the current version of PostgreSQL server

select version();

Now, you want to save time typing the previous command again, you can use \g command to execution the previous command

\g

1-11) Command history

To display command history

\s

if you want to save the command history to a file , you need to specify the file name followed the \s command as follows:

\s filename

1-12) Execution sql commands from a file

\i filename

1-13) Get help on psql command

To know all available psql command, you use the \? command:

\?

To get help on specific PostgreSQL statement, you use the following command:

\h alter table

1-14) Turn on query execution time

To turn on query execution time, you use the following command:

\timing

you use the same command to turn it off.

1-15) Edit command in your own editor

It is very handy if you can type the command in your favorite editor. To do this in psql, you \e command. 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

After you type the command in the editor, save it and close the editor, psql will execute the command and return the result.

It is more useful when you edit a function in the editor:

\ef [function name]

Switch output options
psql supports some type of output format and allows your to customize how to output is formatted on fly:

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

1-16) Quit psql

\q