DEV Community

Cover image for PostgreSQL Cheat Sheet
Matthieu Blandineau for Forest Admin

Posted on • Originally published at forestadmin.com

PostgreSQL Cheat Sheet

We gathered a list of handy PostgreSQL commands and queries when working with PostgreSQL databases. Please ping us @ForestAdmin if you’d like to add something to the cheat sheet!

Getting started with PostgreSQL

Login and enter PostgreSQL command line utility psql

$ sudo su - postgres psql
Enter fullscreen mode Exit fullscreen mode

List postgres databases

\l
Enter fullscreen mode Exit fullscreen mode

Connect to postgres database

\c databaseName
Enter fullscreen mode Exit fullscreen mode

Exit postgreSQL command line utility psql

\q
Enter fullscreen mode Exit fullscreen mode

Managing PostgreSQL

Check PostgreSQL version

SELECT version();
Enter fullscreen mode Exit fullscreen mode

Check if PostgreSQL is installed

$ which psql
Enter fullscreen mode Exit fullscreen mode

Check if PostgreSQL is running

$ pgrep -fa -- -D | grep postgres
Enter fullscreen mode Exit fullscreen mode

Restart PostgreSQL on Linux

# systemctl restart postgresql
// or
# service postgresql restart
// or
# /etc/init.d/postgresql restart
// or
# /sbin/service postgresql restart
Enter fullscreen mode Exit fullscreen mode

Restart PostgreSQL on OSX

# brew services restart postgres
// or
# pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log restart
Enter fullscreen mode Exit fullscreen mode

Restart PostgreSQL on Windows

Winkey + R
Type "services.msc"
Click "restart"
Enter fullscreen mode Exit fullscreen mode

Change PostgreSQL user password

$ sudo -u postgres psql
\password postgres
Enter fullscreen mode Exit fullscreen mode

Exit from psql

\q
Enter fullscreen mode Exit fullscreen mode

Interacting with databases in PostgreSQL

List postgres databases

\l
Enter fullscreen mode Exit fullscreen mode

Connect or switch to postgres database

\c databaseName
Enter fullscreen mode Exit fullscreen mode

Create new postgres database

CREATE DATABASE databaseName
Enter fullscreen mode Exit fullscreen mode

Delete postgres database

DROP DATABASE databaseName
Enter fullscreen mode Exit fullscreen mode

Rename postgres database

ALTER DATABASE old_databaseName TO new_databaseName
Enter fullscreen mode Exit fullscreen mode

Query postgres JSON data

SELECT *
FROM mytable
WHERE myfield @> '{"key1":"valueA"}'
Enter fullscreen mode Exit fullscreen mode

Interacting with tables in PostgreSQL

List postgres tables

\dt
Enter fullscreen mode Exit fullscreen mode

Describe postgres table

\d tableName
Enter fullscreen mode Exit fullscreen mode

Create postgres table

CREATE TABLE tableName( 
    columnName columnType,
    columnName columnType
);
Enter fullscreen mode Exit fullscreen mode

Delete postgres table

DROP TABLE tableName CASCADE
Enter fullscreen mode Exit fullscreen mode

Backup and restore PostgreSQL database

Backup postgres database

$ pg_dump -d databaseName -U userName > backupFile
Enter fullscreen mode Exit fullscreen mode

Restore postgres database

psql databaseName < backupFile
Enter fullscreen mode Exit fullscreen mode

Managing roles and permissions in PostgreSQL

List postgres roles

SELECT rolname FROM pg_roles;
Enter fullscreen mode Exit fullscreen mode

Create postgres user

CREATE USER userName WITH PASSWORD 'userPassword';
Enter fullscreen mode Exit fullscreen mode

Delete postgres user

DROP USER userName;
Enter fullscreen mode Exit fullscreen mode

Change postgres user password

ALTER ROLE userName WITH PASSWORD 'userPassword';
Enter fullscreen mode Exit fullscreen mode

List all assigned roles postgres

SELECT
    r.rolname,
    r.rolsuper,
    r.rolinherit,
    r.rolcreaterole,
    r.rolcreatedb,
    r.rolcanlogin,
    r.rolconnlimit,
    r.rolvaliduntil,
    ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof,
    r.rolreplication
FROM pg_catalog.pg_roles r
ORDER BY 1;
Enter fullscreen mode Exit fullscreen mode

Grant all permissions on postgres database

GRANT ALL PRIVILEGES ON DATABASE databaseName TO userName;
Enter fullscreen mode Exit fullscreen mode

Grant connection permissions on postgres database

GRANT CONNECT ON DATABASE databaseName TO userName;
Enter fullscreen mode Exit fullscreen mode

List permissions for specific role postgres

SELECT table_catalog, table_schema, table_name, privilege_type
FROM   information_schema.table_privileges
WHERE  grantee = userName ORDER BY table_name;
Enter fullscreen mode Exit fullscreen mode

ProstgreSQL psql commands recap

\? List all available psql commands

\h COMMAND Get help on specific command

\l List databases

\c databaseName Connect to database

\dt List tables

\d tableName Describe table

\d+ tableName Describe table with details

\dn List schemas

\df List functions

\dv List views

\du List users

\dy List events

\di List indexes

\q Exit


Need to build admin panels or a GUI tool for PostgreSQL? Check out Forest Admin for PostgreSQL

Latest comments (1)

Collapse
 
xgastaud profile image
Xavier Gastaud

Who doesn't like a good cheat sheet? This one is for you all bootcamp students! (cc Le Wagon, Ironhack) 😍