PostgreSQL Snippets

1. Unnest array

WITH nganu AS (
  SELECT unnest(
    ARRAY[
      '23013be3-0316-4c7e-8afd-a1b7f46bca3e',
    ]
  ) AS id
)

SELECT x.id FROM nganu x

2. Combine columns in one string

SELECT
  x.id,
  string_agg(x.first_name, x.middle_name, x.last_name, ' ') AS xcombined
FROM xschema.employee emp
GROUP BY emp.id

3. Check if all true

select
  COUNT(*) = SUM(case when is_exp then 1 else 0 end)
from
	goods

4. Iterate value/object of an array of JSONB

SELECT jsonb_data
FROM your_table x,
     jsonb_array_elements(x.jsonb_column->'array_property') AS jsonb_data;