PostgreSQL Commands
Create User
Create a Database
Connect to a PostgreSQL Database
List Available PostgreSQL Databases
Switch the connection to a new database
List Available Schemas
List Available Tables in a Database
Describe a Table
List Available Views in a Databases
List Available Functions in a Database
List users and their roles
Retrieve the current version of PostgreSQL
Get help on psql commands
Quit psql
In this tutorial, we will provide you with a list of commonly used commands in PostgreSQL (psql) that will assist you in querying data from the PostgreSQL database server in a quicker and more efficient manner.
These commands are designed to enhance your productivity and make your database interactions more effective.
1. Creating a User in PostgreSQL
To create a database named "db_user", connect to the PostgreSQL database server using a user with sufficient privileges (such as the "postgres" user or a superuser) and run the following command:
postgres=# CREATE USER db_user;
CREATE ROLE
-- Set a password for the user
postgres=# ALTER USER db_user WITH PASSWORD 'db_user';
ALTER ROLE
-- Grant CREATEROLE and CREATEDB permissions to the user
postgres=# ALTER USER db_user WITH CREATEROLE CREATEDB;
ALTER ROLE
postgres=#
2. Create a PostgreSQL Database
To create a database named "dbdocs" with the database owner "db_user", run the following command:
postgres=# create database dbdocs with owner db_user;
CREATE DATABASE
3. Connect to a PostgreSQL Database
To connect to the newly created database named "dbdocs" that we created in the previous step, run the following command:
psql -h HostName -p Port -d DatabaseName -U UserName
[postgres@dbdocs ~]$ psql -U db_user -h 127.0.0.1 -d dbdocs
Password for user db_user:
psql (15.2)
Type "help" for help.
dbdocs=>
4. List Available PostgreSQL Databases
To list the available databases in PostgreSQL, you can use the following command:
The command will display a list of all the databases along with additional details such as the owner, encoding, and access privileges.
dbdocs=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
dbdocs | db_user | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(4 rows)
Alternatively, you can also use the SELECT command on the pg_database system catalog table to achieve the same result:
dbdocs=> SELECT datname,encoding,datctype FROM pg_database;
datname | encoding | datctype
-----------+----------+-------------
postgres | 6 | en_US.UTF-8
dbdocs | 6 | en_US.UTF-8
template1 | 6 | en_US.UTF-8
template0 | 6 | en_US.UTF-8
(4 rows)
dbdocs=>
5. Switch the connection to a new database
Once you establish a connection to a database, you have the ability to switch the connection to a different database using a specified user.
This action will close the previous connection. If you do not provide the user parameter, the current user will be assumed.
\c dbname username
postgres-> \c dbdocs db_user
You are now connected to database "dbdocs" as user "db_user".
dbdocs->
6. List Available Schemas
Connect to the target database and use the following command to list the schemas:
This command will display a list of all the schemas (namespaces) in the currently selected database.
dbdocs=> \dn
List of schemas
Name | Owner
--------+-------------------
dbdocs | db_user
public | pg_database_owner
(2 rows)
dbdocs=>
Alternatively, you can use the SQL query below to achieve the same result:
dbdocs=> SELECT schema_name,schema_owner FROM information_schema.schemata;
schema_name | schema_owner
--------------------+-------------------
public | pg_database_owner
dbdocs | db_user
information_schema | postgres
pg_catalog | postgres
(4 rows)
dbdocs=>
7. List Available Tables in a Database
Connect to the target database and use the following command to list the tables:
dbdocs=> \dt
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+---------
public | dbdocssales | table | db_user
public | users | table | db_user
(2 rows)
dbdocs=>
Alternatively, you can use the SQL query below to achieve the same result:
dbdocs=> SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
table_name
-------------
dbdocssales
users
(2 rows)
dbdocs=>
8. Describe a table
Connect to the target database and use the following command to describe the table:
\d table_name
dbdocs=> \d dbdocssales
Table "public.dbdocssales"
Column | Type | Collation | Nullable | Default
--------------+-------------------+-----------+----------+---------
sales_id | uuid | | not null |
sales_number | integer | | not null |
first_name | character varying | | not null |
last_name | character varying | | not null |
email | character varying | | not null |
phone | character varying | | |
Indexes:
"dbdocssales_pkey" PRIMARY KEY, btree (sales_id)
dbdocs=>
Alternatively, you can use the SQL query below to achieve the same result:
dbdocs=> SELECT column_name, data_type, character_maximum_length, is_nullable FROM information_schema.columns WHERE table_name = 'dbdocssales';
column_name | data_type | character_maximum_length | is_nullable
--------------+-------------------+--------------------------+-------------
sales_id | uuid | | NO
sales_number | integer | | NO
first_name | character varying | | NO
last_name | character varying | | NO
email | character varying | | NO
phone | character varying | | YES
(6 rows)
dbdocs=>
9. List Available Views in a Database
Connect to the target database and use the following command to list the views:
dbdocs=> \dv
List of relations
Schema | Name | Type | Owner
--------+-------------------+------+---------
public | v_confirmed_orders | view | db_user
public | v_projected_sales | view | db_user
public | v_sales | view | db_user
(3 rows)
dbdocs=>
Alternatively, you can use the SQL query below to achieve the same result:
dbdocs=> select table_catalog,table_schema,table_name from information_schema.views WHERE table_schema = 'public';
table_catalog | table_schema | table_name
---------------+--------------+-------------------
dbdocs | public | v_sales
dbdocs | public | v_projected_sales
dbdocs | public | v_confirmed_orders
(3 rows)
dbdocs=>
10. List Available Functions in a Database
Connect to the target database and use the following command to list the functions:
dbdocs=> \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-------------------------------------------+------------------+---------------------------------------------------------------------------+------
public | gtrgm_options | void | internal | func
public | gtrgm_out | cstring | gtrgm | func
public | gtrgm_penalty | internal | internal, internal, internal | func
public | gtrgm_picksplit | internal | internal, internal | func
public | gtrgm_same | internal | gtrgm, gtrgm, internal | func
public | gtrgm_union | gtrgm | internal, internal | func
public | sales_total_price | numeric | quantity integer, unit_price numeric | func
----
---
dbdocs=>
Alternatively, you can use the SQL query below to achieve the same result:
dbdocs=> select proname,proowner,protrftypes FROM pg_proc WHERE pronamespace = 'public'::regnamespace;
proname | proowner | protrftypes
-------------------------------------------+----------+-------------
gtrgm_consistent | 10 |
gtrgm_distance | 10 |
gtrgm_compress | 10 |
gtrgm_decompress | 10 |
gtrgm_penalty | 10 |
gtrgm_picksplit | 10 |
----
---
11. List users and their roles
Connect to the target database and use the following command to list the users and their roles:
postgres=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
db_user | Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=>
Alternatively, you can use the SQL query below to achieve the same result:
postgres=> SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin FROM pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin
---------------------------+----------+------------+---------------+-------------+-------------
db_user | f | t | t | t | t
postgres | t | t | t | t | t
12. Retrieve the current version of PostgreSQL
Connect to the target database and use the following command to retrieve the current version of PostgreSQL:
postgres=> \g
version
-------------------------------------------------------------------------------------------------------------
PostgreSQL 15.2 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit
(1 row)
postgres=>
Alternatively, you can use the SQL query below to achieve the same result:
postgres=> SELECT version();
version
-------------------------------------------------------------------------------------------------------------
PostgreSQL 15.2 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit
(1 row)
13. Get help on psql commands
To get all available psql commands, you use the \? command:
postgres=>\?
--To get help for creating a table
postgres=> \h CREATE TABLE
14. Quit psql
To exit from psql, run the following command:
postgres=>\q
Related content