postgresql: cheat-sheet

30 April 2015

Show Tables

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

Or simply type \d

Show Databases

SELECT datname FROM pg_database;

Or simply type \l

Show columns

SELECT column_name FROM information_schema.columns WHERE table_name ='table';

Or simply type \d table

Describe Table

SELECT column_name FROM information_schema.columns WHERE table_name ='table';

Or simply type \d+ table

Backup a database (command line)

pg_dump dbName > dbName.sql

Backup all databases (command line)

pg_dumpall > pgbackup.sql

Create user

CREATE USER tom WITH PASSWORD 'myPassword';

Create database

CREATE DATABASE mydatabase;

Grant all privileges on database

GRANT ALL PRIVILEGES ON DATABASE mydatabase to tom;

[ Sysadmin  Database  Postgresql  ]