SQL for Anarchists

Eventually I would like to write an actual SQL for Anarchists article, but for now I will be putting some of the tips, tricks, and things I've figured out the hard way here as snippets so I can find them again more readily than hunting through old migrations and/or Postico history.

wildcard search

Use the % much like * in regex or globbing

select id
from users
where email LIKE 'dethe%'
;

You can use multiple wildcards

select count(name) from users where name LIKE '%@%';

String manipulation

Functions like splitpart(string, delimiter, whichpart) and replace(string, original, replacement) can go a long way towards making SQL feel like a real language.

select initcap(replace(split_part(name, '@', 1), '.', ' ')), email from users where name LIKE '%@%'

ltrim(string, characters) and rtrim(string, characters) are handy too.

UPDATE cases SET scope_of_influence = ltrim(rtrim(scope_of_influence, '}'), '{');

You can use split_part(string, separator, index) to get one part of a split string, indexes start at 1.

split_part(key, '_value_', 1)

Manipulating array values

Use unnest to turn arrays into rows

select distinct general_issue from (select unnest(general_issues) as general_issue from cases) as a order by general_issue;

A nice property of array_remove is that it will not fail if the item you're removing is not in the array.

update cases set general_issues = array_remove(general_issues, 'other');

Set Methods

Find items that are in one table, but not another.

select key from rotate_case_edit_localized('en') where key not in (select key from rotate_case_view_localized('en'));

Working with composite types

Composite types are the PostgreSQL term for nested structures, like dictionaries in Python or Objects in JavaScript. Every table is also a composite type, but you can define your own and we use a bunch of them in PPedia.

https://www.postgresql.org/docs/10/rowtypes.html

You can extract just the desired fields with ->.

select to_json(tags_localized.*)->'accessibility' as lookup from tags_localized where language = 'en';

Deep Magic

List locally defined functions

SELECT  quote_ident(p.proname) as function 
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace 
WHERE  n.nspname not like 'pg%' and p.proname not like '\_pg%';

List locally defined types

select typname from pg_type, pg_user where typowner = usesysid and usename = 'dethe' and not typname LIKE 'pg_%' and not typname LIKE '\_%';