PostgreSQL Cheat Sheet

The PostgreSQL cheat sheet provides you with the common PostgreSQL commands and statements.

Database

Rdbms

Postgresql Cheat Sheet

Also see

Posgres-cheatsheet (gist.github.com)

Miscellaneous

Import/Export CSV

Export table into CSV file

\copy table TO '<path>' CSV \copy table(col1,col1) TO '<path>' CSV \copy (SELECT...) TO '<path>' CSV

Import CSV file into table

\copy table FROM '<path>' CSV \copy table(col1,col1) FROM '<path>' CSV

Remote access

Get location of postgresql.conf

$ psql -U postgres -c 'SHOW config\_file'

Append to postgresql.conf

listen_addresses = '*'

Append to pg_hba.conf (Same location as postgresql.conf)

host all all 0.0.0.0/0 md5 host all all ::/0 md5

Restart PostgreSQL server

$ sudo systemctl restart postgresql

Restore

Restore a database with psql

$ psql -U user mydb < mydb\_backup.sql

Restore a database with pg_restore

$ pg\_restore -d mydb mydb\_backup.sql -c

Â -U Â Specify a database user
Â -c Â Drop database before recreating
Â -C Â Create database before restoring
Â -e Â Exit if an error has encountered
Â -F Â Format (c: custom, d: directory, t: tar, p: plain text sql(default)) Use pg_restore -? to get the full list of options

Backup

Use pg_dumpall to backup all databases

$ pg\_dumpall -U postgres > all.sql

Use pg_dump to backup a database

$ pg\_dump -d mydb -f mydb\_backup.sql

Â -a Â Dump only the data, not the schema
Â -s Â Dump only the schema, no data
Â -c Â Drop database before recreating
Â -C Â Create database before restoring
Â -t Â Dump the named table(s) only
Â -F Â Format (c: custom, d: directory, t: tar) Use pg_dump -? to get the full list of options

PostgreSQL Commands

Large Objects

\lo_export LOBOID FILE
\lo_import FILE [COMMENT]
\lo_list
\lo_unlink LOBOID

Misc

-	-
`\cd [DIR]`	Change the directory
`\timing [on\|off]`	Toggle timing
`\! [COMMAND]`	Execute in shell
`\! ls -l`	List all in shell

Variables

-	-
`\prompt [TEXT] NAME`	Set variable
`\set [NAME [VALUE]]`	Set variable (or list all if no parameters)
`\unset NAME`	Delete variable

Input/Output

-	-
`\copy ...`	Import/export table See also: copy
`\echo [STRING]`	Print string
`\i FILE`	Execute file
`\o [FILE]`	Export all results to file
`\qecho [STRING]`	String to output stream

Formatting

- -

\a Toggle between unaligned and aligned

\C [STRING] Set table title, or unset if none

\f [STRING] Show or set field separator for unaligned

\H Toggle HTML output mode

\t [on|off] Show only rows

\T [STRING]

Set or unset HTML tag attributes

\x [on|off] Toggle expanded output

Connection

-	-
`\c [DBNAME]`	Connect to new database
`\encoding [ENCODING]`	Show or set client encoding
`\password [USER]`	Change the password
`\conninfo`	Display information

Informational

-	-
`\l[+]`	List all databases
`\dn[S+]`	List schemas
`\di[S+]`	List indexes
`\du[+]`	List roles
`\ds[S+]`	List sequences
`\df[antw][S+]`	List functions
`\deu[+]`	List user mappings
`\dv[S+]`	List views
`\dl`	List large objects
`\dT[S+]`	List data types
`\da[S]`	List aggregates
`\db[+]`	List tablespaces
`\dc[S+]`	List conversions
`\dC[+]`	List casts
`\ddp`	List default privileges
`\dd[S]`	Show object descriptions
`\dD[S+]`	List domains
`\des[+]`	List foreign servers
`\dew[+]`	List foreign-data wrappers
`\dF[+]`	List text search configurations
`\dFd[+]`	List text search dictionaries
`\dFp[+]`	List text search parsers
`\dFt[+]`	List text search templates
`\dL[S+]`	List procedural languages
`\do[S]`	List operators
`\dO[S+]`	List collations
`\drds`	List per-database role settings
`\dx[+]`	List extensions
`S`: show system objects, `+`: additional detail

Query buffer

-	-
`\e [FILE]`	Edit the query buffer (or file)
`\ef [FUNC]`	Edit function definition
`\p`	Show the contents
`\r`	Reset (clear) the query buffer
`\s [FILE]`	Display history or save it to file
`\w FILE`	Write query buffer to file

PostgreSQL Working

Dates

Show current date YYYY-MM-DD

SELECT current\_date;

Calculate age between two dates

SELECT age(timestamp, timestamp);

Show current time with time zone

SELECT current\_time;

Make dates using integers

SELECT make_date(2021,03,25);

Schema

List schemas

\dn SELECT schema_name FROM information_schema.schemata; SELECT nspname FROM pg_catalog.pg_namespace;

Create schema

CREATE SCHEMA IF NOT EXISTS <schema_name>;

Drop schema

DROP SCHEMA IF EXISTS <schema_name> CASCADE;

Users

List roles

SELECT rolname FROM pg_roles;

Create user

CREATE USER <user_name> WITH PASSWORD '<password>';

Drop user

DROP USER IF EXISTS <user_name>;

Alter user password

ALTER ROLE <user_name> WITH PASSWORD '<password>';

Data

Select all data

SELECT \* FROM <table_name>;

Read one row of data

SELECT \* FROM <table_name> LIMIT 1;

Search for data

SELECT \* FROM <table_name> WHERE <column_name> = <value>;

Insert data

INSERT INTO <table_name> VALUES( <value_1>, <value_2> );

Update data

UPDATE <table_name> SET <column_1> = <value_1>, <column_2> = <value_2> WHERE <column_1> = <value>;

Delete all data

DELETE FROM <table_name>;

Delete specific data

DELETE FROM <table_name> WHERE <column_name> = <value>;

Columns

Add column

ALTER TABLE <table_name> IF EXISTS ADD <column_name> <data_type> [<constraints>];

Update column

ALTER TABLE <table_name> IF EXISTS ALTER <column_name> TYPE <data_type> [<constraints>];

Delete column

ALTER TABLE <table_name> IF EXISTS DROP <column_name>;

Update column to be an auto-incrementing primary key

ALTER TABLE <table_name> ADD COLUMN <column_name> SERIAL PRIMARY KEY;

Insert into a table, with an auto-incrementing primary key

INSERT INTO <table_name> VALUES (DEFAULT, <value1>); INSERT INTO <table_name> (<column1_name>,<column2_name>) VALUES ( <value1>,<value2> );

Permissions

Become the postgres user, if you have permission errors

sudo su - postgres psql

Grant all permissions on database

GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user_name>;

Grant connection permissions on database

GRANT CONNECT ON DATABASE <db_name> TO <user_name>;

Grant permissions on schema

GRANT USAGE ON SCHEMA public TO <user_name>;

Grant permissions to functions

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user_name>;

Grant permissions to select, update, insert, delete, on a all tables

GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user_name>;

Grant permissions, on a table

GRANT SELECT, UPDATE, INSERT ON <table_name> TO <user_name>;

Grant permissions, to select, on a table

GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;

Tables

List tables, in current db

\dt SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;

List tables, globally

\dt \*.\*. SELECT \* FROM pg_catalog.pg_tables

List table schema

\d <table_name> \d+ <table_name> SELECT column_name, data_type, character_maximum_length FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '<table\_name>';

Create table

CREATE TABLE <table_name>( <column_name> <column_type>, <column_name> <column_type> );

Create table, with an auto-incrementing primary key

CREATE TABLE <table_name> ( <column_name> SERIAL PRIMARY KEY );

Delete table

DROP TABLE IF EXISTS <table_name> CASCADE;

Databases

List databases

\l

Connect to database

\c <database_name>

Show current database

SELECT current_database();

Create database

CREATE DATABASE <database_name> WITH OWNER <username>;

Drop database

DROP DATABASE IF EXISTS <database_name>;

Rename database

ALTER DATABASE <old_name> RENAME TO <new_name>;

Recon

Show version

SHOW SERVER_VERSION;

Show system status

\conninfo

Show environmental variables

SHOW ALL;

List users

SELECT rolname FROM pg_roles;

Show current user

SELECT current\_user;

Show current user's permissions

\du

Show current database

SELECT current_database();

Show all tables in database

\dt

List functions

\df <schema>

Getting Started

Switch and connect

$ sudo -u postgres psql

List all databases

postgres=# \l

Connect to the database named postgres

postgres=# \c postgres

Disconnect

postgres=# \q postgres=# \!

Getting help

-	-
`\h`	Help on syntax of SQL commands
`\h` DELETE	DELETE SQL statement syntax
`\?`	List of PostgreSQL command
Run in PostgreSQL console

psql commands

Option	Example	Description
`[-d] <database>`	psql -d mydb	Connecting to database
`-U`	psql -U john mydb	Connecting as a specific user
`-h` `-p`	psql -h localhost -p 5432 mydb	Connecting to a host/port
`-U` `-h` `-p` `-d`	psql -U admin -h 192.168.1.5 -p 2506 -d mydb	Connect remote PostgreSQL
`-W`	psql -W mydb	Force password
`-c`	psql -c '\c postgres' -c '\dt'	Execute a SQL query or command
`-H`	psql -c "\l+" -H postgres > database.html	Generate HTML report
`-l`	psql -l	List all databases
`-f`	psql mydb -f file.sql	Execute commands from a file
`-V`	psql -V	Print the psql version

PostgreSQL Cheat Sheet

Table of contents