7 Advanced SQL Techniques with Tested PostgreSQL Examples

Advanced SQL is useful when it preserves the intended grain, ordering, and update semantics. These seven PostgreSQL 16 techniques each have a complete fictional fixture and a result you can check. Read-only examples use VALUES. Examples that create objects are isolated in transactions ending with ROLLBACK; run them in a disposable practice database with the required creation privileges.

1. CTEs: separate measures before combining them

Compute advertising cost and revenue independently before joining them. Revenue divided by advertising cost is return on ad spend, or ROAS. The percentage (revenue − advertising cost) / advertising cost is an ad-cost-only return; it is not full profit ROI unless all relevant costs are represented. Zero cost leaves both ratios undefined.

WITH costs(advertiser_id,cost) AS (VALUES (1,20::numeric),(1,30),(2,0),(3,20)),
revenue(advertiser_id,amount) AS (VALUES (1,80::numeric),(2,10)),
c AS (SELECT advertiser_id,SUM(cost) AS cost FROM costs GROUP BY advertiser_id),
r AS (SELECT advertiser_id,SUM(amount) AS revenue FROM revenue GROUP BY advertiser_id)
SELECT c.advertiser_id,c.cost,COALESCE(r.revenue,0) AS revenue,
 ROUND(COALESCE(r.revenue,0)/NULLIF(c.cost,0),2) AS roas,
 ROUND(100*(COALESCE(r.revenue,0)-c.cost)/NULLIF(c.cost,0),2) AS ad_cost_return_pct
FROM c LEFT JOIN r USING(advertiser_id) ORDER BY c.advertiser_id;

advertiser_id | cost | revenue | roas | ad_cost_return_pct
1 | 50 | 80 | 1.60 | 60.00
2 | 0 | 10 | NULL | NULL
3 | 20 | 0 | 0.00 | -100.00

The zero-revenue substitution assumes this complete fixture has no revenue row for advertiser 3. A missing production feed would need an unknown/freshness flag. CTEs clarify each intermediate grain; they do not automatically guarantee better performance.

2. Window functions: define the tie policy

WITH plays(country,artist_id,plays) AS (
 VALUES ('CA',1,10),('CA',2,10),('US',3,8),('US',4,5)
), ranked AS (
 SELECT *,ROW_NUMBER() OVER(PARTITION BY country ORDER BY plays DESC,artist_id) AS one_winner,
 RANK() OVER(PARTITION BY country ORDER BY plays DESC) AS tied_rank
 FROM plays
)
SELECT country,artist_id,one_winner,tied_rank FROM ranked ORDER BY country,one_winner;

country | artist_id | one_winner | tied_rank
CA | 1 | 1 | 1
CA | 2 | 2 | 1
US | 3 | 1 | 1
US | 4 | 2 | 2

Filter one_winner = 1 for exactly one artist per country, using ID as a documented tie-breaker. Filter tied_rank = 1 to retain all tied leaders. Adding the ID to RANK’s ordering would eliminate the intended tie. For running totals, also choose an explicit frame and stable transaction order.

3. UPSERT: conflict handling is not retry deduplication

BEGIN;
CREATE TEMP TABLE advanced_views(page text PRIMARY KEY,views integer NOT NULL) ON COMMIT DROP;
INSERT INTO advanced_views VALUES ('home',1);
INSERT INTO advanced_views AS existing VALUES ('home',1),('docs',1)
ON CONFLICT(page) DO UPDATE SET views=existing.views+EXCLUDED.views;
SELECT page,views FROM pg_temp.advanced_views ORDER BY page;
ROLLBACK;

page | views
docs | 1
home | 2

The unique page key identifies the conflict. This operation adds the incoming increment, so submitting the same request twice increments twice. If a retried event must count once, use a unique event/idempotency key and an appropriate transactional design. ON CONFLICT alone does not infer which requests are duplicates.

4. Recursion: expose and stop cycles

WITH RECURSIVE employees(id,manager_id) AS (VALUES (1,2),(2,1),(3,NULL)),
chain(id,manager_id,depth) AS (
 SELECT id,manager_id,0 FROM employees WHERE id=2
 UNION ALL
 SELECT e.id,e.manager_id,c.depth+1
 FROM chain c JOIN employees e ON e.id=c.manager_id
) CYCLE id SET is_cycle USING path
SELECT id,manager_id,depth,is_cycle FROM chain ORDER BY depth;

id | manager_id | depth | is_cycle
2 | 1 | 0 | false
1 | 2 | 1 | false
2 | 1 | 2 | true

The fictional manager relationship contains a cycle. PostgreSQL marks the repeated node and stops expanding that path. This prevents an unbounded traversal while making the defect visible; it does not repair the hierarchy. For large graphs, also bound the scope and execution resources. A depth limit needs an explicit truncated-result flag if it can cut off valid paths.

5. Dynamic SQL: allow identifiers and bind values separately

Preserve three separate protections: an identifier allowlist, identifier quoting with FORMAT %I, and value binding with EXECUTE USING. The following functions return real rows, use caller privileges, and reference only this session’s temporary fixture. Mixed-case DisplayName proves that identifier quoting matters. Unknown or NULL column names raise before execution; a NULL comparison value matches no row under =.

BEGIN;
CREATE TEMP TABLE advanced_employees (
 id integer PRIMARY KEY,"DisplayName" text,department text,salary numeric
) ON COMMIT DROP;
INSERT INTO advanced_employees VALUES
 (1,'O''Brien','Data',100),(2,'Ada','Data',100),(3,'Ben','Sales',80);
CREATE FUNCTION pg_temp.filter_employees(p_column text,p_value text)
RETURNS SETOF pg_temp.advanced_employees LANGUAGE plpgsql AS $$
BEGIN
 IF p_column IS NULL OR p_column NOT IN ('DisplayName','department') THEN
  RAISE EXCEPTION 'Unsupported filter column' USING ERRCODE='22023';
 END IF;
 RETURN QUERY EXECUTE format(
  'SELECT e.* FROM pg_temp.advanced_employees e WHERE e.%I=$1 ORDER BY e.id',
  p_column) USING p_value;
END $$;
CREATE FUNCTION pg_temp.sort_data(p_column text)
RETURNS SETOF pg_temp.advanced_employees LANGUAGE plpgsql AS $$
BEGIN
 IF p_column IS NULL OR p_column NOT IN ('id','DisplayName','salary') THEN
  RAISE EXCEPTION 'Unsupported sort column' USING ERRCODE='22023';
 END IF;
 RETURN QUERY EXECUTE format(
  'SELECT e.* FROM pg_temp.advanced_employees e ORDER BY e.%I,e.id',p_column);
END $$;
SELECT id,"DisplayName" FROM pg_temp.filter_employees('DisplayName','O''Brien');
SELECT id,salary FROM pg_temp.sort_data('salary');
SELECT COUNT(*) AS null_value_matches FROM pg_temp.filter_employees('department',NULL);
ROLLBACK;

id | DisplayName
1 | O'Brien

id | salary
3 | 80
1 | 100
2 | 100

null_value_matches
0

The sort direction is fixed ascending, and id resolves salary ties. SQL-looking filter values remain data. A placeholder cannot substitute for a column name, and quoting an identifier does not authorize it. Application calls must also use the driver’s parameter binding. See dynamic command parameters.

6. Materialized views: make staleness observable

A materialized view stores query results until refreshed. This isolated schema example intentionally uses ordinary tables because PostgreSQL materialized views cannot depend on temporary tables. CREATE SCHEMA fails safely if the name already exists; use a fresh practice database, and run the entire transaction.

BEGIN;
CREATE SCHEMA advanced_mv_demo;
CREATE TABLE advanced_mv_demo.sales(amount numeric NOT NULL);
INSERT INTO advanced_mv_demo.sales VALUES (10),(20);
CREATE MATERIALIZED VIEW advanced_mv_demo.total AS
 SELECT SUM(amount) AS revenue FROM advanced_mv_demo.sales;
INSERT INTO advanced_mv_demo.sales VALUES (5);
SELECT revenue AS stale_revenue FROM advanced_mv_demo.total;
REFRESH MATERIALIZED VIEW advanced_mv_demo.total;
SELECT revenue AS refreshed_revenue FROM advanced_mv_demo.total;
ROLLBACK;

stale_revenue
30

refreshed_revenue
35

A production report needs a refresh owner, schedule, failure status, and freshness expectation. A regular view stores a query definition rather than this result snapshot; what it sees still follows the transaction’s visibility rules. Neither choice is universally faster.

7. UNNEST: retain empty parents when required

WITH posts(id,tags) AS (
 VALUES (1,ARRAY['sql','sql','python']),(2,ARRAY[]::text[]),(3,NULL::text[])
)
SELECT p.id,t.position,t.tag
FROM posts p LEFT JOIN LATERAL UNNEST(p.tags) WITH ORDINALITY AS t(tag,position) ON true
ORDER BY p.id,t.position;

id | position | tag
1 | 1 | sql
1 | 2 | sql
1 | 3 | python
2 | NULL | NULL
3 | NULL | NULL

WITH ORDINALITY records element position. The LEFT lateral join preserves posts with empty or NULL arrays; a cross join would omit them. Repeated sql elements remain repeated. Decide whether the question counts tag occurrences or distinct posts before aggregating, and do not mistake the null-extended row for a real tag.

Choose the technique that enforces the contract

Use each feature to make one rule explicit: intermediate grain, ties, update conflicts, recursion termination, identifier access, freshness, or parent preservation. Predict edge-case output before running a larger query. Primary references: CTEs and cycles, ON CONFLICT, materialized views, and arrays.