====== SQL snippets useful or not ... you decide ======



===== Raw epoch timestamp to something fancy =====

<code>
to_char(to_timestamp((metadata->'last_visit')::int), 'YYYYWW'::text)
</code>
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 =====

<code>
select DISTINCT ON (shop_id) shop_id FROM filters;
</code>
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 =====

<code>
UPDATE t1
SET t1.c1 = new_value
FROM t2
WHERE t1.c2 = t2.c2;
</code>

==== Hex Juggling ====

<code>
select ('x' ||substring(md5('picalike_category'), 32-5, 32))::bit(24)::int | (x1<<24);
</code>
Convert a hash part into a 24-bit integer and also add some value


=== Find Tuples without a Match ===

<code>
select filters.picalike_id, x1 from filters left join sim_clusters on filters.picalike_id = sim_clusters.picalike_id where x1 is null;
</code>
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 ==

<code>
(CASE WHEN  array_length(f.picalike_gender,1) IS NOT NULL THEN f.picalike_gender && s.picalike_gender END)
</code>
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