PostgreSQL Commands
corpline

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

Online Tests
Read more

Oracle Database
Read more

MSSQL Database
Read more

PostGres Database
Read more

Linux
Read more

ASP/C#
Read more

Navigation Panel