Postgres can do that

I've used Postgres on and off for a few years but mainly for side projects or in a team where somebody else did most of the database 'stuff'.

On a recent project I've been using Postgres daily and being the one doing 'stuff' I'll learnt lots of new things that it can do and thought I'd document them here so I can find them later. I'll keep adding to this as I find new things.

string_to_table

SELECT string_to_table('a,b,c', ',');

Find all the values for an enum type

If you create a type like this

CREATE TYPE contact_type AS ENUM (
'workemail', 
'personalemail', 
'mobile'
);

You can later retrieve all the values using this query

SELECT enumlabel FROM pg_enum JOIN pg_type ON pg_enum.enumtypid = pg_type.oid WHERE pg_type.typname = 'contact_type';

BOOL_OR

The BOOL_OR() is an aggregate function that allows you to aggregate boolean values across rows within a group.

So for example if you have a user table with an flag (is_active) you can find out if there are any active users like this

SELECT bool_or(is_active) FROM users;