PostgreSQL Cheatsheet
JSON
Access property from json object with "->" and "->>"
- -> operator returns JSON object field by key.
- ->> operator returns JSON object field by text.
-- 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:
- Checks if there are any product variants (product_variant) associated with the current product that match the search criteria
- Returns true if at least one variant is found that matches either:
- The variant's title matches the search query (case-insensitive)
- The variant's SKU matches the search query (case-insensitive)
The EXISTS operator is particulary useful because:
- It stops searching as soon as it finds a single matching record (more efficient than counting all matches)
- Returns a boolean value (true/false)
- Is commonly used for checking relationships without needing to return the actual matching records
In the context of your search, this means a product will be included in the results if either:
- The product's title matches the search query
- The product's description matches the search query
- OR any of its variants' titles or SKUs match the search query
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
- /l: List of databases
- /c {db-name}: Connect specific database
- /dS {table-name}: Detail properties of the table
- /dT+ {type/enum-name}: Detail type/enum with values
- psql -U {user_name} -d {database_name} < file.sql: Run SQL file to psql