PostgreSQL Quick Reference

Connect

$ psql -h localhost -d mydb -U myuser

List Databases

From within a psql session:

l

List Tables

dt

Connect To DB

c myuser

Execute SQL in a File

$ psql -U myuser -d mydb -a -f myfile.sql

Run a Query

$ psql -U myuser -d mydb -c 'SELECT * FROM my_table'

Output to File

Example shows opening a file, listing tables to the file, and finally closing the file:

db=>o out.txt
db=>dt
db=>q

Get PostgreSQL Logs

db=> show log_destination;

See this reference.

Check Running Queries

SELECT pid, age(query_start, clock_timestamp()), usename, query 
FROM pg_stat_activity 
WHERE  query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

See also this reference.

Terminate a Running Query

select pg_terminate_backend(pid int)

Check Locks

See this reference.

Do Something for Every Child Table

DO
$$
DECLARE
    row record;
BEGIN
    FOR row IN select c.relname as tablename from pg_inherits JOIN pg_class AS c ON (inhrelid=c.oid) JOIN pg_class AS p ON (inhparent=p.oid) WHERE p.relname = 'my_table'
    LOOP
        EXECUTE 'drop TABLE ' || quote_ident(row.tablename);
    END LOOP;
END;
$$;

Insert Sample Data with Incrementing Values

insert into my_table (name, thing, id)
select 'user1', 'test' || x.id, 1
    from generate_series(40, 100, 1) as x(id);

Check the generate_series documentation for what the parameters mean.

Convert a String to a Timestamp

to_timestamp('2015-04-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

Partitioning

Refer to this example for how to partition tables for performance.