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_NUMBER(<numberstring>,<formatstring>) ¶ Convert string to numeric.
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
- Subtopics
- String Manipulation in PostgreSQL
- Writings
Sources & Bookmarks