John Jerald De Chavez

PostgreSQL Cheatsheet

JSON

Access property from json object with "->" and "->>" 

-- get stakeholder on courier, get nested object
SELECT
  c."courierName",
  c."data"->'stakeholder'->>'primaryEmail' as "courierEmail"
FROM couriers c
WHERE c.id = '891e6dc3-141a-4bf6-bbcf-5407009ecefc'

Query from json column that has array of object value 

SELECT q.id, j
FROM quotes q
   CROSS JOIN LATERAL JSON_ARRAY_ELEMENTS(q."jsonResponse") as j
WHERE j->>'shipmentMode' LIKE 'Roa%';

Append array of object on array of object column 

-- Append new delta_listing_boxes
UPDATE order_line_item
SET delta_listing_boxes = (
  CASE
    WHEN delta_listing_boxes IS NULL THEN '[]'::JSONB
    ELSE delta_listing_boxes
  END
) || '[{"id": "d9923cc3-dd17-44f7-9f9b-ce4740725ed9","count": null,"weight": 5,"quantity": 4}]'::JSONB
WHERE id = '1e7db4b9-658f-4322-9115-a685b3e1946d';

Relations

Get latest left join row 

...
LEFT JOIN
  order_adjustment oa ON o.id = oa.order_id
  AND oa.created_at = (
    SELECT MAX(created_at) -- Filter with latest created_at
    FROM order_adjustment oa2
    WHERE oa2.order_id  = o.id
  )
...

Filtering

Filter with EXISTS 

EXISTS (
  SELECT 1 FROM product_variant pv 
  WHERE pv.product_id = product.id 
  AND (
    pv.title ILIKE $3 OR 
    pv.sku ILIKE $3
  )
)

This is a SQL EXISTS subquery that:

  1. Checks if there are any product variants (product_variant) associated with the current product that match the search criteria
  2. Returns true if at least one variant is found that matches either:

The EXISTS operator is particulary useful because:

In the context of your search, this means a product will be included in the results if either:

The SELECT 1 inside the EXISTS clause is just a convention - it could be any value since EXISTS only cares whether any rows are returned, not what values those rows contain.

Tips

Working with duplicate data and how to update the recent/latest row 

-- Common Table Expression or CTE
WITH LatestSubscriptions AS (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY cs.company_id, cs.subscription_plan_id ORDER BY cs.starts_at DESC) AS row_num,
	c.name as company_name, sc.stripe_customer, sp.alias as plan_name, cs.*
  FROM
    stripe_customer sc
    LEFT JOIN company_subscription cs ON cs.company_id = sc.company_id
    LEFT JOIN subscription_plan sp ON sp.id = cs.subscription_plan_id
    LEFT JOIN company c ON cs.company_id = c.id
  WHERE
    sp.alias IN ('BASE', 'FEATURE_REVERSED_MARKETPLACE', 'FEATURE_REVERSED_MARKETPLACE_SELLER')
)
SELECT * FROM LatestSubscriptions;

-- Use this when trying to update the rows
UPDATE company_subscription
SET renews_at = NULL
FROM LatestSubscriptions
WHERE LatestSubscriptions.row_num > 1 and company_subscription.id = LatestSubscriptions.id;

Types/Enums

Common pattern when working with types or Enum

-- Create enum packaging_type
CREATE TYPE packaging_type AS ENUM ('BOX', 'CARTON', 'TRAY');

-- Adding value on enum
ALTER TYPE packaging_type ADD VALUE IF NOT EXISTS 'PACKET';

-- Rename carton enum
ALTER TYPE packaging_type RENAME VALUE 'CARTON' TO 'CARTONS';

-- List value by array
select enum_range(null::packaging_type);

-- List value by rows
select unnest(enum_range(null, null::packaging_type));

Listing Enums with pg_type 

-- Query oid of enum_listing_packaging_type
SELECT oid FROM pg_type WHERE typname = 'enum_listing_box_packaging_type'

-- Query all data about enum
SELECT * FROM pg_type WHERE typname = 'enum_listing_packaging_type'

-- List of enum values with oid from pg_type
SELECT * FROM pg_enum pe WHERE pe.enumtypid = 94076

Enums Aggregation Patterns

Group by column and aggregate all results 

-- Create ENUM Type
CREATE TYPE packaging_type AS ENUM ('BOX', 'CARTON', 'TRAY');

-- Create Table
CREATE TABLE listing_packaging (
	id SERIAL PRIMARY KEY,
	label VARCHAR(255),
	type packaging_type,
	company_id UUID
);

-- Group by type and aggregate labels
SELECT
  lp."type",
  array_agg(lp.label) as "labels"
FROM listing_packaging lp
GROUP BY lp.type;

-- Result: Group by type and aggregate labels
|type   |labels                     |
|-------|---------------------------|
|PACKET |{1kg}                      |
|CARTON |{"22L Carton","32L Carton"}|
|BOX    |{"2kg Box","1kg Box"}      |
|TRAY   |{"18L Tray"}               |

Group by column and aggregate if column has value 

-- Group by type and aggregate has custom packaging by sum() and case
SELECT
  lp."type",
  SUM(CASE WHEN lp.company_id IS NOT NULL THEN 1 ELSE 0 END) > 0 AS "has_custom"
FROM listing_packaging lp
GROUP BY lp.type;

-- Result: Group by type and aggregate has custom packaging by sum() and case
|type  |has_custom|
|------|----------|
|TRAY  |false     |
|CARTON|false     |
|PACKET|false     |
|BOX   |true      |

Dump and Restore

How to create a copy of your database with pg_dump.

pg_dump --verbose --host=0.0.0.0 --port=5432 --username=postgres -f /database/dump.sql --dbname=my_database

To restore/use your dump database we can use psql.

psql -U postgres -h 0.0.0.0 -d my_new_db -f ./dump.sql

psql

psql is the PostgreSQL interactive terminal.

Here are the common command I use

References