PostgreSQL supports most of the major features of SQL:2003. Out of 164 mandatory features required for full Core conformance, PostgreSQL conforms to at least 150. No current version of any database management system claims full conformance to Core SQL:2003. Below are some PSQL (Postgres’s SQL) commands for beginners to getting started with PostgreSQL.
1. Connecting to PostgreSQL database server.
psql -U username -h hostname -d databasename
e.g.
[postgres@demunix ~]$ psql -U huzefa -h localhost -d postgres
Password for user huzefa:
psql (10.11)
Type "help" for help.
postgres=> These arguments can be exported in environment variables similar to above connection commands as PGUSER, PGHOST and PGDATABASE respectively.
2. Create Database.
createdb demunix (on OS cli with proper LD_LIBRARY_PATH path supplied or exported)
OR
Create database demunix; (in psql CLI)
e.g.
demunix=# create database demunix;
CREATE DATABASE3. List down all databases.
\l or \l+
e.g.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
demunix | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
huzefa | huzefa | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/huzefa +
| | | | | huzefa=CTc/huzefa
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
demunix | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 298 MB | pg_default |
huzefa | huzefa | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/huzefa +| 7871 kB | pg_default |
| | | | | huzefa=CTc/huzefa | | |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7983 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7489 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7847 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(5 rows)4. Switch between the databases (Provided you have access to the database and whitelisted in pg_hba.conf)
\c databasename or \connect databasename
e.g.
postgres=# \c demunix
You are now connected to database "demunix" as user "huzefa"
OR
postgres=# \connect demunix
You are now connected to database "demunix" as user "huzefa".5. Show the information of your connection to the database.
\conninfo
e.g.
demunix=# \conninfo
You are connected to database "demunix" as user "huzefa" via socket in "/tmp" at port "5432".6. List down tablespaces.
\db or \db+
e.g.
demunix=# \db
List of tablespaces
Name | Owner | Location
------------+----------+----------
pg_default | postgres |
pg_global | postgres |
(2 rows)
demunix=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+----------+-------------------+---------+--------+-------------
pg_default | postgres | | | | 328 MB |
pg_global | postgres | | | | 574 kB |
(2 rows)
7. List down schemas.
\dn or \dn+
e.g.
demunix=# \dn
List of schemas
Name | Owner
---------+----------
app | postgres
public | postgres
website | postgres
(3 rows)
demunix=# \dn+
List of schemas
Name | Owner | Access privileges | Description
---------+----------+----------------------+------------------------
app | postgres | |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
website | postgres | |
(3 rows)8. List down tables.
\dt schema.table or \dt+ schema.table
e.g.
demunix=# \dt app.tbl*
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
app | tbl1 | table | postgres
app | tbl2 | table | postgres
app | tbl3 | table | postgres
app | tbl4 | table | postgres
app | tbl5 | table | postgres
app | tbl6 | table | postgres
(6 rows)
demunix=# \dt+ app.tbl*
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+-------+-------------
app | tbl1 | table | postgres | 36 MB |
app | tbl2 | table | postgres | 36 MB |
app | tbl3 | table | postgres | 36 MB |
app | tbl4 | table | postgres | 36 MB |
app | tbl5 | table | postgres | 36 MB |
app | tbl6 | table | postgres | 36 MB |
(6 rows)9. List down views.
\dv or \dv+
e.g.
demunix=# \dv app.vw*
List of relations
Schema | Name | Type | Owner
--------+------+------+----------
app | vw1 | view | postgres
app | vw2 | view | postgres
app | vw3 | view | postgres
app | vw4 | view | postgres
app | vw5 | view | postgres
app | vw7 | view | postgres
(6 rows)
demunix=# \dv+ app.vw*
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+------+----------+---------+-------------
app | vw1 | view | postgres | 0 bytes |
app | vw2 | view | postgres | 0 bytes |
app | vw3 | view | postgres | 0 bytes |
app | vw4 | view | postgres | 0 bytes |
app | vw5 | view | postgres | 0 bytes |
app | vw7 | view | postgres | 0 bytes |
(6 rows)10. View structure of table or view.
\d schema.table or \d+ schema.table | \d schema.view or \d+ schema.view
e.g.
Table
demunix=# \d app.tbl7
Table "app.tbl7"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+--------------------------------------
sr | integer | | not null | nextval('app.tbl7_sr_seq'::regclass)
name | character varying(15) | | |
doj | date | | |
demunix=# \d+ app.tbl7
Table "app.tbl7"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
sr | integer | | not null | nextval('app.tbl7_sr_seq'::regclass) | plain | |
name | character varying(15) | | | | extended | |
doj | date | | | | plain | | View
demunix=# \d app.vw7
View "app.vw7"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
sr | integer | | |
name | character varying(15) | | |
doj | date | | |
demunix=# \d+ app.vw7
View "app.vw7"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+-----------------------+-----------+----------+---------+----------+-------------
sr | integer | | | | plain |
name | character varying(15) | | | | extended |
doj | date | | | | plain |
View definition:
SELECT tbl7.sr,
tbl7.name,
tbl7.doj
FROM app.tbl7;11. List users / roles.
\du or \du+
e.g.
demunix=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
app | | {}
huzefa | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
read_only | Cannot login | {}
website | | {}
demunix=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
app | | {} |
huzefa | | {} |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
read_only | Cannot login | {} |
website | | {} | 12. Size of table.
\dt+ schemaname.tablename or select pg_size_pretty(pg_total_relation_size(‘schemaname.tablename’));
e.g.
demunix=# select pg_size_pretty(pg_total_relation_size('app.tbl1'));
pg_size_pretty
----------------
36 MB
(1 row)
demunix=# \dt+ app.tbl1
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+-------+-------------
app | tbl1 | table | postgres | 36 MB |
(1 row)13. Size of database.
\l+ databasename or select pg_size_pretty(pg_database_size(‘databasename’));
e.g.
demunix=# select pg_size_pretty(pg_database_size('demunix'));
pg_size_pretty
----------------
298 MB
(1 row)
demunix=# \l+ demunix
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
---------+----------+----------+-------------+-------------+-------------------+--------+------------+-------------
demunix | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 298 MB | pg_default |
(1 row)14. Show server parameter.
show parameter_name or select name,setting from pg_settings where name = ‘parameter_name’;
e.g.
demunix=# show archive_mode ;
archive_mode
--------------
on
(1 row)
demunix=# select name,setting from pg_settings where name = 'archive_mode';
name | setting
--------------+---------
archive_mode | on
(1 row)15. Show pg_hba entries from psql.
select * from pg_hba_file_rules;
e.g.
demunix=# select * from pg_hba_file_rules;
line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+-------+---------------+------------+---------+-----------------------------------------+-------------+---------+-------
85 | local | {all} | {postgres} | | | peer | |
86 | local | {demunix} | {huzefa} | | | md5 | |
88 | host | {demunix} | {huzefa} | 0.0.0.0 | 0.0.0.0 | md5 | |
91 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | |
94 | local | {replication} | {postgres} | | | peer | |
(5 rows)16. Grant and Revoke privileges.
grant select,insert,update on schemaname.tablename to user;
or
revoke insert,update on schemaname.tablename from user;
e.g.
demunix=# grant select,insert,update on app.tbl2 to app;
GRANT
demunix=# revoke insert,update on app.tbl2 from app;
REVOKE17. Change password.
\password
e.g.
demunix=# \password
Enter new password: (enter password when prompts)
Enter it again: (confirm)18. Set timing to on to see the time taken by a query to complete the execution.
\timing
e.g.
demunix=# \timing
Timing is on.
demunix=# select count(*) from app.tbl1 ;
count
---------
1048578
(1 row)
Time: 62.686 ms19. Enable expanded output of the query.
\x
e.g.
demunix=# select * from pg_settings limit 1;
name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_
val | reset_val | sourcefile | sourceline | pending_restart
-------------------------+---------+------+-------------------+---------------------------------------------------------+------------+------------+---------+---------+---------+---------+----------+------
----+-----------+------------+------------+-----------------
allow_system_table_mods | off | | Developer Options | Allows modifications of the structure of system tables. | | postmaster | bool | default | | | | off
| off | | | f
(1 row)
Time: 1.121 ms
demunix=# \x
Expanded display is on.
demunix=# select * from pg_settings limit 1;
-[ RECORD 1 ]---+--------------------------------------------------------
name | allow_system_table_mods
setting | off
unit |
category | Developer Options
short_desc | Allows modifications of the structure of system tables.
extra_desc |
context | postmaster
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | off
reset_val | off
sourcefile |
sourceline |
pending_restart | f
Time: 0.947 ms20. Exit the psql CLI
\q
e.g.
demunix=# \q
[postgres@demunix ~]$
There you go, Peace 🙂 Share with someone who might benefit from this.