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!)

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 t1
SET t1.c1 = new_value
FROM t2
WHERE t1.c2 = t2.c2;

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

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

(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