Terminology

createdb -T <dbname> Clone an existing database.

createdb -U postgres <dbname> Create a database.

dropdb -U postgres <dbname> Drop a database.

GENERATE_SERIES(<from>,<to>) Generate a series of numbers into a virtual table with one column named a.

JOIN <table> USING (<columnlist>) Join on the column(s) in <columnlist>.

NULLIF(<v1>,<v2>) If <v1> is NULL, replace with <v2>; otherwise <v1>.

ORDER BY RANDOM() LIMIT <n> Select <n> random rows.

psql -c <command> Run specific command in PostgreSQL interactive terminal.

psql <dbname> <sql> definition: 'PostgreSQL interactive terminal.'
ety: ''
variants: 'Add "-f <filename>" to pull the SQL from a file. Add "-U <user>" to specify a username. Add "-h <host>" to specify a host.'

STRING_AGG(<expression>,<delimiter>) Aggregate function that builds a string by concatenating pieces.

TO_DATE(<datestring>,<formatstring>) Convert string to date.

TO_NUMBER(<numberstring>,<formatstring>) Convert string to numeric.

TO_TIMESTAMP(<datestring>,<formatstring>) Convert string to timestamp.

TO_TIMESTAMP(<double>) Convert Unix epoch to time stamp.

TRIM(TO_CHAR(x,'00000')) Convert an integer to zero-padded string.

Facts, Thoughts and Opinions

Create a record set of sequential dates (PostgreSQL)

SELECT ('2000-01-01'::date + s.a) AS theday FROM GENERATE_SERIES(0,364) AS s(a)) AS calendar

Create a record set of sequential month ranges for a given year (PostgreSQL)

SELECT ((CAST('2011-01-01' AS DATE) + (months.n * interval '1 month'))::date) beginning_of_month,
((CAST('2011-01-01' AS DATE) + (months.n * interval '1 month' + interval '1 month' - interval '1 day'))::date) end_of_month
FROM GENERATE_SERIES(0,11) months(n)

Populate a column with pseudo-random values from an array

PostgreSQL version

It's psuedorandom because it uses the row ID as a seed rather than random value.

UPDATE tbl
SET value = values.valuearray[tbl.id % ARRAY_LENGTH(values.valuearray,1) + 1]
FROM (
SELECT ARRAY['1','2','3'] AS valuearray
) values

Populate a column with random values from a LOV table

PostgreSQL version

UPDATE    tbl_data 
SET    lov_id =  randomizer.lov_id
FROM    (
    SELECT DISTINCT
        data_id,
        FIRST_VALUE(lov_id) OVER (PARTITION BY data_id ORDER BY seed) AS lov_id
    FROM    (
        SELECT    tbl_data.data_id, tbl_lov.lov_id, RANDOM() AS seed
        FROM    tbl_data
        CROSS JOIN tbl_lov
    ) seeder
) randomizer 
WHERE    tbl_data.data_id = randomizer.data_id;

PostgreSQL literal subquery

A PostgreSQL subquery can also be a VALUES list. Table alias is required.

FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow')) AS names(first,last)

PostgreSQL query: List tables and columns in current database

SELECT b.relname AS table, a.attname AS column
FROM pg_attribute a, pg_class b
WHERE b.relfilenode=a.attrelid
AND a.attname NOT IN ('tableoid','cmax','xmax','cmin','xmin','ctid');

PostgreSQL query: List tables in current database

SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE';

Randomize a date column

UPDATE table SET stamp = TO_DATE('2014-03-01','YYYY-MM-DD') + CAST(RANDOM()*730 AS INTEGER)

Randomize a flag column defined as INTEGER

UPDATE table SET flag = RANDOM()*2;

Images

[[/div]]