Quick PostgreSQL Reference

quick-postgresql-reference.md

Quick PostgreSQL Reference

Shell Commands

Create

createuser <username>
createdb <database>

Drop

dropuser <username>
dropdb <database>

Connect

psql -U <username> -d <database>   # mysql <database> -u <username>

Dump & Restore

pg_dump <database> -h <host> -p <port> -U <username> -F t -v > <filename>
pg_restore -h <hostname> -p <port> -U <username> -d <database> -v <filename>

psql Commands

Use + after many of these commands to get more information.

\c <database>                       " Connect to <database>
\l                                  " List the names, owners, character set encodings, and access privileges of all the databases
\d  [<pattern>]                     " Show all columns, their types, etc. for any table, view, index, or sequence
\di [<pattern>]                     " Same as above but restricted to Indexes
\ds [<pattern>]                     " Same as above but restricted to Sequences
\dt [<pattern>]                     " Same as above but restricted to Tables
\dv [<pattern>]                     " Same as above but restricted to Views
\dn [<pattern>]                     " Lists available schemas (namespaces)
\du [<pattern>]                     " Lists all database roles

SQL

ALTER DATABASE <name> RENAME TO <newname>;
SET search_path TO <schema>;