PostgreSQL Cheat Sheet Query Page

Posted on

This is a cheat sheet command page for everything related to PostgreSQL Database Server.

List all existing database :

postgres=# \l


postgres=# \list

List all roles with the privileges :

postgres=# \du

List all role names :

postgres=# SELECT rolname FROM pg_roles;

Quit from PostgreSQL Command Console :

postgres=# \q


postgres=# \quit

Create Database :

postgres=# create database db_name

Drop Database :

postgres=# drop database db_name

Restore Database from a plain file backup database :

psql -Upostgres_username db_name < db_dump_file_location_and_name

psql -Upostgres_username db_name < db_dump_file_location_and_name.dump 

Restore Database from a PostgreSQL custom-format dump file backup database :

pg_restore -Upostgres_username -d db_name db_dump_file_location_and_name.pgdump

Select and Use Database :

postgres=# \connect db_name


postgres=# \c db_name

Show All Tables in a Database :

dbname=# \d


dbname=# \dt

Create User in PostgreSQL Database Server

postgres=# CREATE USER username WITH PASSWORD 'password'

Connect to a database

postgres=# \c database_name

How to Add Column of a Table in PostgreSQL Database

postgres=# alter table schema_name.table_name add column column_name column_type;

Grant All Privileges to a specific schema to a specific user

postgres=# grant all privileges on schema schema_name to user_name;

Grant All Privileges to All Tables in a Certain Schema in PostgreSQL Database

postgres=# grant all privileges on all tables on schema schema_name to user_name;/pre>

Add or Change Role Attribute to User in PostgreSQL Database

postgres=#alter user user_name [with] role_attribute_name;

Leave a Reply