Table of Contents

SQL snippets useful or not ... you decide

Raw epoch timestamp to something fancy

to_char(to_timestamp((metadata->'last_visit')::int), 'YYYYWW'::text)

Hint: a common anti pattern is that for the first 10 weeks, the generated condensed dates look like '20221' instead of '202201' which is avoid by this snippet (in sql!)

Distinct list of column values

select DISTINCT ON (shop_id) shop_id FROM filters;

Hint: never use “SELECT DISTINCT” without ON. GROUP BY is much faster but “DISTINCT ON” is even faster.

Update a column in one table with a column from another table

UPDATE t1
SET t1.c1 = new_value
FROM t2
WHERE t1.c2 = t2.c2;

Hex Juggling

select ('x' ||substring(md5('picalike_category'), 32-5, 32))::bit(24)::int | (x1<<24);

Convert a hash part into a 24-bit integer and also add some value

Find Tuples without a Match

select filters.picalike_id, x1 from filters left join sim_clusters on filters.picalike_id = sim_clusters.picalike_id where x1 is null;

An alternative to EXCEPT which is horribly slow. The idea is a left outer join that fills x1 with null if no matching partner is found. This can be used as a filter. In our setup 18 secs vs >150 secs runtime, but depending on the result set memory consumption can be significant + parallel workers

Conditional WHERE filters
(CASE WHEN  array_length(f.picalike_gender,1) IS NOT NULL THEN f.picalike_gender && s.picalike_gender END)

Without a reference lookup, it is not clear whether there is a gender or not, this we need a conditional filter to use the gender if present and 'True' otherwise.

Keywords: sql psql snippets postgres