PostgreSQL

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;