PostgreSQL
From Torben's Wiki
Contents
date handling
select count(*), DATE_TRUNC('hour', created) as hour from table group by 2 desc select * from table where created > '"2019-10-20 00:00:00+02"' select * from table where created > current_timestamp - '3 hours'::interval select * from table where created > CURRENT_DATE - 91 -- days
Date formatting
see [1]
SELECT to_char(last_login, 'DD.MM.YYYY') as last_login FROM mytable
cast types
select t.id, 'matched filter1'::character varying(16) from table t
EXCEPT (=MINUS in Oracle)
from [2]
SELECT column_list FROM A WHERE condition_a EXCEPT SELECT column_list FROM B WHERE condition_b;
sprintf-alternative
goal
sprintf 'u%07d', 1
achieved via
SELECT replace( format('u%7s', 1), ' ', '0');
random item of list
SELECT (array['Yes', 'No', 'Maybe'])[floor(random() * 3 + 1)];
pseudonymization
UPDATE user SET username = replace( format('u%7s', id), ' ', '0') -- u0000001 for id=1 ,first_name = (array['Max', 'Egon', 'Lisa']) [floor(random() * 3 + 1)] ,last_name = (array['Meier', 'Müller', 'Schmidt')[floor(random() * 3 + 1)] ,email = 'no-mail@schaeffler.com' WHERE id = 1;