PostgreSQL psql Common Commands
This article introduces the commonly used commands in the psql tool provided by PostgreSQL.
The psql tool is a client program provided by PostgreSQL. You can use the psql tool to manage the PostgreSQL database server. This article organizes commonly used psql commands so that you can manage your PostgreSQL database server more efficiently.
Connect to PostgreSQL
To manage a PostgreSQL server with the psql tool, please connect to the PostgreSQL server first. The command is as follows:
psql -d dbname -U user -W
If you need to connect to a remote PostgreSQL server, use the following command:
psql -h host -p port -d dbname -U user -W
in:
- The
-hoption is used to specify the hostname or IP address of the remote PostgreSQL server. The default value islocalhost. - The
-poption is used to specify the port number of the remote PostgreSQL server. The default value is5432.
psql common commands
Once you have logged into the PostgreSQL server using psql, you can use the following commands to manage the server.
List all databases
To list all databases in the current PostgreSQL database server, use the \l or \l+ command:
\l
or
\l+
Connect to a database
Use the \c or \connect command to connect to a database.
To connect to a database with the current user, use the following command:
\c dbname
To connect to the current database with a new user, use the following command:
\c - username
You can replace \c in the above command with \connect, they are equivalent.
List tables in database
To list the tables in the current database, use the \dt or \dt+ command:
\dt
or
\dt+
Describe a table
To display a table’s structure or definition, such as columns, constraints, etc., use the \d command:
\d table_name
For example, to view the structure of the product table, use the following command:
\d product
testdb=# \d product
Table "public.product"
Column | Type | Collation | Nullable | Default
--------------+-------------------+-----------+----------+------------------------------
id | integer | | not null | generated always as identity
product_name | character varying | | not null |
attributes | hstore | | |
Indexes:
"product_pkey" PRIMARY KEY, btree (id)List available schemas
To list all schemas of the currently connected database, use this \dn command.
\dn
List of schemas
Name | Owner
--------+----------
public | postgresList available functions
To list all functions available in the current database, use this \df command.
\df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------------------------+--------------------+---------------------------------------------------------+------
public | akeys | text[] | hstore | func
public | avals | text[] | hstore | func
public | defined | boolean | hstore, text | func
public | delete | hstore | hstore, hstore | func
public | delete | hstore | hstore, text | func
public | delete | hstore | hstore, text[] | func
public | each | SETOF record | hs hstore, OUT key text, OUT value text | func
public | exist | boolean | hstore, text | func
public | exists_all | boolean | hstore, text[] | func
public | exists_any | boolean | hstore, text[] | func
public | fetchval | text | hstore, text | func
public | ghstore_compress | internal | internal | func
public | ghstore_consistent | boolean | internal, hstore, smallint, oid, internal | func
public | ghstore_decompress | internal | internal | func
public | ghstore_in | ghstore | cstring | func
public | ghstore_options | void | internal | func
public | ghstore_out | cstring | ghstore | func
public | ghstore_penalty | internal | internal, internal, internal | func
public | ghstore_picksplit | internal | internal, internal | func
public | ghstore_same | internal | ghstore, ghstore, internal | func
public | ghstore_union | ghstore | internal, internal | func
public | gin_consistent_hstore | boolean | internal, smallint, hstore, integer, internal, internal | func
public | gin_extract_hstore | internal | hstore, internal | func
public | gin_extract_hstore_query | internal | hstore, internal, smallint, internal, internal | func
public | hs_concat | hstore | hstore, hstore | func
public | hs_contained | boolean | hstore, hstore | func
public | hs_contains | boolean | hstore, hstore | func
public | hstore | hstore | record | func
public | hstore | hstore | text, text | func
public | hstore | hstore | text[] | func
public | hstore | hstore | text[], text[] | func
public | hstore_cmp | integer | hstore, hstore | func
public | hstore_eq | boolean | hstore, hstore | func
public | hstore_ge | boolean | hstore, hstore | func
public | hstore_gt | boolean | hstore, hstore | func
public | hstore_hash | integer | hstore | func
public | hstore_hash_extended | bigint | hstore, bigint | func
public | hstore_in | hstore | cstring | func
public | hstore_le | boolean | hstore, hstore | func
public | hstore_lt | boolean | hstore, hstore | func
public | hstore_ne | boolean | hstore, hstore | func
public | hstore_out | cstring | hstore | func
public | hstore_recv | hstore | internal | func
public | hstore_send | bytea | hstore | func
public | hstore_subscript_handler | internal | internal | func
public | hstore_to_array | text[] | hstore | func
public | hstore_to_json | json | hstore | func
public | hstore_to_json_loose | json | hstore | func
public | hstore_to_jsonb | jsonb | hstore | func
public | hstore_to_jsonb_loose | jsonb | hstore | func
public | hstore_to_matrix | text[] | hstore | func
public | hstore_version_diag | integer | hstore | func
public | isdefined | boolean | hstore, text | func
public | isexists | boolean | hstore, text | func
public | my_time_multirange | my_time_multirange | | func
public | my_time_multirange | my_time_multirange | VARIADIC my_time_range[] | func
public | my_time_multirange | my_time_multirange | my_time_range | func
public | my_time_range | my_time_range | time without time zone, time without time zone | func
public | my_time_range | my_time_range | time without time zone, time without time zone, text | func
public | populate_record | anyelement | anyelement, hstore | func
public | skeys | SETOF text | hstore | func
public | slice | hstore | hstore, text[] | func
public | slice_array | text[] | hstore, text[] | func
public | svals | SETOF text | hstore | func
public | tconvert | hstore | text, text | func
(65 rows)List available views
To list all available views in the current database, use this \dv command.
\dv
List users and roles
To list all users and roles, use the \du command:
\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}Enable query execution time
To turn on query execution time, use this \timing command.
\timing
select * from product;
id | product_name | attributes
----+--------------+--------------------------------------------------------------
2 | Shirt B | "Color"=>"White", "Style"=>"Business", "Season"=>"Spring"
1 | Computer A | "CPU"=>"2.5", "Disk"=>"1T", "Brand"=>"Dell", "Memory"=>"16G"
(2 rows)
Time: 0.281 msWhen you run \timing the command again, the query execution time is turned off.
View command history
To display the command history, use this \s command.
\s
If you want to save the command history to a file, you need to specify the filename after the \s command as follows:
\s filename
Execute the last command
To execute the last command, use the \g command:
\g
The \g allows you to avoid retyping the last command.
Get help of SQL commands
To get a help document of an SQL command, use the \h command as follows:
\h sql_command
For example, to get help document for TRUNCATE, use the following command:
\h TRUNCATE
Command: TRUNCATE
Description: empty a table or set of tables
Syntax:
TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
URL: https://www.postgresql.org/docs/14/sql-truncate.htmlget help of psql
For detailed usage of psql command, use the \? command
\?
Import a file
If you want to import data from a file, use the \i command as follows:
\i filename
Open extended display
To turn on extended display for the result set of a SELECT statement, use the \x command.
\x
select * from product;
-[ RECORD 1 ]+-------------------------------------------------------------
id | 2
product_name | Shirt B
attributes | "Color"=>"White", "Style"=>"Business", "Season"=>"Spring"
-[ RECORD 2 ]+-------------------------------------------------------------
id | 1
product_name | Computer A
attributes | "CPU"=>"2.5", "Disk"=>"1T", "Brand"=>"Dell", "Memory"=>"16G"Extended display is helpful for displaying those very long columns.
If you run \x the command again, you will turn off the extended display.
exit psql
To exit psql, you can use \q command and press enter to exit psql.
\q
Conclusion
This article shows you the common commands of the psql tool.