Postgres Advanced SQL

Advanced SQL Techniques in PostgreSQL and Greenplum

I hold working expertise in six areas of advanced SQL: efficient json/jsonb extraction, POSIX regular-expression functions, multi-level grouping with GROUPING SETS, window functions, precise JOIN semantics, and recursive CTEs. I both write/repair such queries (Mode: Do) and explain them so a developer builds a correct mental model (Mode: Teach).

Before starting, consult references/reference.md — it contains the full catalogue of runnable examples, the complete list of common mistakes per area, the success-criteria checklist, the teaching analogies, and the 53 self-check questions.

Instructions

Step 0: Confirm the working environment

I verify or assume: PostgreSQL 9.5+ (JSON record functions as documented for 12–16) or Greenplum; an SQL client (psql, DBeaver, sqlfiddle); CREATE TABLE/INSERT rights for fixtures and SELECT on the targets; EXPLAIN / EXPLAIN ANALYZE access to inspect plans and spot repeated CTE Scan nodes. I keep in mind: ::type cast syntax, array literals {...}, dollar-quoting $$...$$ for embedded JSON, PostgreSQL arrays are 1-indexed, -> returns json/jsonb while ->> returns text, and Greenplum has no rlike (that is MySQL). I test on data containing duplicate keys and NULLs so row multiplication and NULL semantics become visible.

I also keep the logical execution order in front of me at all times: FROM (tables, joins, subqueries) → WHERE → GROUP BY → HAVING → SELECT (window functions computed here) → ORDER BY.

Step 1: Route the request

Mode: Do

I use this mode when the user wants working SQL, a refactor, or a performance fix.

Part A — Extract json/jsonb dictionary data without burning CPU

A1. Recognize the antipattern. Folding a whole CTE into one json[b] object to replace record scanning with instant key lookup is legitimate; extracting from it like this is not:

, (((TABLE jsd) -> src.id::text)::jsonb ->> 'Number')::text     "Number"
, (((TABLE jsd) -> src.id::text)::jsonb ->> 'Date')::date       "Date"
, (((TABLE jsd) -> src.id::text)::jsonb ->> 'Amount')::numeric  "Amount"
, replace(replace(((TABLE jsd) -> src.id::text)::jsonb ->> 'Flags', '[', '{'), ']', '}')::boolean[] "Flags"

Four defects: (1) multiple reads of the same CTE even when it holds one record; (2) extracting text per key and re-casting it to jsonb again and again; (3) each JSON key pulled into its own separate identically-named field — pure copy-paste; (4) manual conversion of a JSON array text into PostgreSQL array text with nested replace('[' → '{', ']' → '}'). With a small object this passes unnoticed; at ~1 MB of JSON and several dozen extracted fields one such query drives a PostgreSQL backend to 100% CPU.

A2. Classify the task: (a) only specific records by a given set of keys; (b) all records for all keys (JSON arrives as a query parameter); (c) best case — I control the source and can pass an array of record objects.

A3. By a set of keys → jsonb_to_record. Declare each name and type exactly once in the column definition list:

WITH jsd AS (
  SELECT $${
    "1" : {"Number":101,"Date":"2023-11-01","Amount":123.45,"Flags":[true,false,null]}
  , "2" : {"Number":202,"Date":"2023-11-02","Amount":321.54,"Flags":[false,null,true]}
  , "3" : {"Number":303,"Date":"2023-11-03","Amount":100.00,"Flags":[null,true,false]}
  }$$::jsonb
)
SELECT *
FROM
  unnest(ARRAY[1, 2]) id                  -- incoming set of keys
, jsonb_to_record((TABLE jsd) -> id::text) -- callable without LATERAL
  T(
    "Number" integer
  , "Date"   date
  , "Amount" numeric(32,2)
  , "Flags"  boolean[]
  );

No repeated text→jsonb casts and no replace-magic: PostgreSQL performs every conversion itself, including JSON array → boolean[].

A4. All keys → jsonb_each / json_each. Listing keys with jsonb_object_keys and reducing to A3 works, but iterating key–value pairs removes per-key lookup entirely:

SELECT jskey::integer, T.*
FROM
  jsonb_each((TABLE jsd)) js(jskey, jsval)
, jsonb_to_record(jsval) T(
    "Number" integer, "Date" date, "Amount" numeric(32,2), "Flags" boolean[]);

A5. Array of record objects → jsonb_to_recordset. If I control the transport format, one action solves everything:

WITH jsd AS (SELECT $$[
   {"id":1,"Number":101,"Date":"2023-11-01","Amount":123.45,"Flags":[true,false,null]}
 , {"id":2,"Number":202,"Date":"2023-11-02","Amount":321.54,"Flags":[false,null,true]}
 , {"id":3,"Number":303,"Date":"2023-11-03","Amount":100.00,"Flags":[null,true,false]}
]$$::jsonb)
SELECT * FROM jsonb_to_recordset((TABLE jsd)) T(
  id integer, "Number" integer, "Date" date, "Amount" numeric(32,2), "Flags" boolean[]);

A6. Treat copy-paste as a smell detector. When the same extraction expression starts "growing" in the query text or in the EXPLAIN plan, that visual repetition is itself the diagnosis of a repeated-computation antipattern. I stop and look for a record-returning/set-returning function that does the job once, and I check the documented JSON function list before inventing string surgery.

Part B — Regular expressions

Five recurring tasks, five tools:

-- filtering
select id, inn, date_from from schema_dds.clients
where inn similar to '(XX)?[0-9]{10,12}';   -- matches 1234567890, XX0987654321, 111222333444

-- extraction
select client, info, substring(info, '[0-9\-\+ ]{10,}') as number from schema_dds.clients;
-- 'Tall. 83912672222. House' → 83912672222 ; 'Number +79991112222' → +79991112222 ; 'Likes cats.' → empty/NULL

-- replacement: without 'g' only the FIRST occurrence reacts; 'i' ignores case
select regexp_replace('Room 402. The worst happened in room 402','[0-9]{3}','XXX');
-- 'Room XXX. The worst happened in room 402'
select regexp_replace('Room 402. The worst happened in room 402','[0-9]{3}','XXX','g');
-- 'Room XXX. The worst happened in room XXX'

-- split into array (arrays start at index 1); '\s+' absorbs accidental double spaces
select fio,
  regexp_split_to_array(fio,'\s+') as fio_arr,
  (regexp_split_to_array(fio,'\s+'))[1] as f,
  (regexp_split_to_array(fio,'\s+'))[2] as i,
  (regexp_split_to_array(fio,'\s+'))[3] as o
from (select 'Sidorov Ivan  Petrovich' as fio) as foo;  -- {Sidorov,Ivan,Petrovich}

-- split into rows: row count multiplies by the number of parts
select seller_id, seller, regexp_split_to_table(fruits,'\s+') as fruit
from the_market order by 1,2;   -- 2 sellers × 3 fruits = 6 rows

Debugging rule: when a regex function returns NULL, either the substring genuinely is absent or — far more often — the regex is written wrong. I verify the regex before blaming the data. I demonstrate/test these functions over an attribute supplied by a subquery rather than a bare literal — it is much easier to perceive. These five cover 99% of practical regex work in PostgreSQL/Greenplum.

Part C — Multi-level grouping with GROUPING SETS

1. Baseline: every non-aggregated selected field is listed in GROUP BY — SELECT district, region, count(smth) summ FROM table GROUP BY district, region;
2. Grand total: add the empty set () while keeping the full combination — GROUP BY GROUPING SETS ((district, region), ()). The extra row carries NULL in every grouped field.
3. Read the construct aloud. GROUPING SETS ((region),(),(district)) says: total per each region; total over everything; total per each district. Because the original combined (district, region) set was split, district becomes NULL on the region rows — a common trap.
4. Correct formulation for detail + subtotals + grand total:

SELECT district, region, count(smth) as summ,
CASE WHEN region is null and district is not null then CONCAT(district, ' TOTAL')
     else case when district is null and region is null then 'GRAND TOTAL'
     else district end end as district_new
FROM table
GROUP BY GROUPING SETS ((region, district), (), (district))
ORDER BY district nulls FIRST, region nulls FIRST;

5. If the data columns themselves can contain NULL, I refine the query so genuine NULLs are distinguishable from grouping-generated NULLs (via the GROUPING() indicator or a pre-coalesced sentinel value).
6. Reconciliation check: the grand total equals the sum of all subtotals.

Part D — Window functions

A window function operates over a designated set of rows (window/partition) and computes a value for that set in a separate column. A partition is the set of rows specified for the function by one column or a group of columns; each window function in a query may partition by different columns. Decisive contrast: GROUP BY with aggregates reduces the row count; window functions do not — every source row survives and gains an extra column. Windows are computed in the SELECT stage, so only ORDER BY may reference them; and when GROUP BY is present, partitions are formed after grouping has collapsed rows. Syntax: inline OVER (PARTITION BY ... ORDER BY ...), or a separate WINDOW clause giving the window an alias referenced in the select list.

Fixture:

create table student_grades (name varchar, subject varchar, grade int);
insert into student_grades values
('Petya','russian',4),('Petya','physics',5),('Petya','history',4),
('Masha','math',4),('Masha','russian',3),('Masha','physics',5),('Masha','history',3);

Class 1 — aggregate windows (SUM, AVG, COUNT, MIN, MAX):

select name, subject, grade,
  sum(grade) over (partition by name)   as sum_grade,
  avg(grade) over (partition by name)   as avg_grade,
  count(grade) over (partition by name) as count_grade,
  min(grade) over (partition by name)   as min_grade,
  max(grade) over (partition by name)   as max_grade
from student_grades;

Class 2 — ranking (ORDER BY inside OVER is MANDATORY): ROW_NUMBER() numbers rows sequentially independently of ties; RANK() gives tied rows the same rank and then skips the next number (1,1,3); DENSE_RANK() gives tied rows the same rank without gaps (1,1,2). NULLs are treated as equal and receive the same rank.

select name, subject, grade,
  row_number() over (partition by name order by grade desc),
  rank()       over (partition by name order by grade desc),
  dense_rank() over (partition by name order by grade desc)
from student_grades;

Class 3 — value/offset functions: LAG() = previous value in sort order, LEAD() = next value, FIRST_VALUE()/LAST_VALUE() = first/last value of the argument column within the partition (ORDER BY inside OVER mandatory).

create table grades_quartal (name varchar, quartal varchar, subject varchar, grade int);
insert into grades_quartal values
('Petya','quarter 1','physics',4),('Petya','quarter 2','physics',3),
('Petya','quarter 3','physics',4),('Petya','quarter 4','physics',5);

select name, quartal, subject, grade,
  lag(grade)  over (order by quartal) as previous_grade,
  lead(grade) over (order by quartal) as next_grade
from grades_quartal;

Out of baseline scope: window frame clauses.

Part E — JOIN semantics and performance

• t1 INNER JOIN t2 ON cond is logically syntactic sugar for t1 CROSS JOIN t2 WHERE cond — all row combinations filtered by a predicate. Disclaimer I always attach: logical equivalence does not mean the engine materializes every combination and filters it; real execution uses hash/merge/nested-loop algorithms.
• LEFT JOIN = the INNER JOIN result plus left rows that matched nothing, NULL-padded. It does not return "one row per left row". Equivalent form:

SELECT * FROM t1 CROSS JOIN t2 WHERE t1.id = t2.id
UNION ALL
SELECT t1.id, null FROM t1 WHERE NOT EXISTS (SELECT FROM t2 WHERE t2.id = t1.id)

• RIGHT JOIN is the mirror image.
• Predict row counts with duplicates: t1=(1),(1),(3) INNER JOIN t2=(1),(1),(2) → 4 rows (1,1); t1=(1),(1),(3) LEFT JOIN t2=(1),(1),(4),(5) → 5 rows (four (1,1) plus (3,NULL)).
• ON accepts any boolean expression, not just id = id: JOIN cities_ip_ranges c ON c.ip_range && s.ip (&& = overlap operator of the ip4r extension). table1 JOIN table2 ON true is exactly table1 CROSS JOIN table2.
• Anti-join: to find rows of one table absent from another I use EXISTS/NOT EXISTS, not LEFT JOIN ... WHERE ... IS NULL — more readable and faster.
• Performance: joins are not dangerous. Banning them project-wide and hand-joining two or three downloaded tables in application code is the real antipattern. With a modest join count and correct indexes joins are fast. Trouble starts around a dozen tables in one query: join-order search is O(n!), so past a threshold the planner stops searching and ships the best plan it managed to build. Remedy: extract a highly selective part into a CTE subquery when I know for certain that joining two specific tables yields very few rows, making the remaining joins cheap.

Part F — Recursive queries (WITH RECURSIVE)

WITH RECURSIVE is iteration, not self-call recursion: compute repeatedly until a condition stops it. Mandatory structure — an anchor (starting) part and a recursive part separated by UNION/UNION ALL, with identical column lists in count, order and types.

WITH RECURSIVE r AS (
    SELECT 1 AS i, 1 AS factorial              -- anchor
    UNION
    SELECT i+1 AS i, factorial * (i+1) AS factorial  -- recursive part
    FROM r WHERE i < 10
)
SELECT * FROM r;   -- 10 rows: 1,2,6,24,120,720,5040,40320,362880,3628800

FROM r does not re-execute the whole query: on the first pass it reads the anchor output, afterwards only the previous iteration's output. Algorithm: (1) take the starting data; (2) substitute it into the recursive part; (3) if the output is non-empty, append it to the result and reuse it as input for the next call — go to (2); if empty, terminate. I always include a terminating predicate (WHERE i < 10) or a structural limit (no more children). Factorial is a weak motivator — PostgreSQL computes factorials natively (SELECT 10000! yields an ~30 000-digit number); the real payoff is hierarchical (id, parent_id) data.

CREATE TABLE geo (id int not null primary key, parent_id int references geo(id), name varchar(1000));
INSERT INTO geo (id, parent_id, name) VALUES
(1,null,'Planet Earth'),(2,1,'Continent Eurasia'),(3,1,'Continent North America'),
(4,2,'Europe'),(5,4,'Russia'),(6,4,'Germany'),
(7,5,'Moscow'),(8,5,'Saint Petersburg'),(9,6,'Berlin');

Never reference the recursive CTE inside a subquery — WHERE parent_id IN (SELECT id FROM r) raises ERROR: recursive reference to query "r" must not appear within a subquery. Rewrite as a JOIN:

WITH RECURSIVE r AS (
   SELECT id, parent_id, name FROM geo WHERE parent_id = 4
   UNION
   SELECT geo.id, geo.parent_id, geo.name FROM geo JOIN r ON geo.parent_id = r.id
)
SELECT * FROM r;   -- 5 rows: Russia, Germany, Moscow, Saint Petersburg, Berlin

Include the root and compute depth by anchoring on id = 4 and carrying a level column:

WITH RECURSIVE r AS (
   SELECT id, parent_id, name, 1 AS level FROM geo WHERE id = 4
   UNION ALL
   SELECT geo.id, geo.parent_id, geo.name, r.level + 1 AS level
   FROM geo JOIN r ON geo.parent_id = r.id
)
SELECT * FROM r;   -- 6 rows: Europe 1; Russia, Germany 2; Moscow, St Petersburg, Berlin 3

Choose UNION vs UNION ALL deliberately: UNION deduplicates (may silently drop legitimately repeated rows and hide cycles), UNION ALL keeps everything (and loops forever on cyclic data).

Step 2: Verify against the success criteria

After writing or refactoring, I walk the checklist in references/reference.md ("Success criteria") — e.g. each JSON key/type declared exactly once and no replace() hacks; 'g' present when all occurrences must be masked; detail rows keep all fields while () produces the grand total; result row count equals source row count for window queries; predicted vs actual join row counts match; recursion terminates with exactly the expected descendants.

Mode: Teach

I switch to this mode when the user asks why, prepares for an interview, mentors others, or holds a wrong mental model ("INNER JOIN is the intersection of sets", "RANK is gapless", "window functions group rows"). Sequence I follow:

1. Destroy the wrong model first, with an experiment. For joins: predict two rows for table1=(1),(1),(3) INNER JOIN table2=(1),(1),(2), run it, get four. The surprise is the teaching moment. I explain why: a table is not a mathematical set (set elements are unique, table rows repeat), and the verb "intersection" itself misleads. I never draw Venn diagrams and avoid the word "intersection".
2. Build up from the primitive. CROSS JOIN (all combinations) → INNER JOIN (combinations filtered by a predicate) → LEFT/RIGHT JOIN (plus NULL-padded unmatched rows).
3. Use the Cartesian-product grid instead of circles. One relation along the x axis, the other along the y axis, every cell one row combination: CROSS JOIN = all cells; INNER JOIN = only cells satisfying ON (duplicate 1,1 × 1,1 visibly lights four cells — exactly why duplicates multiply); LEFT JOIN = those cells plus a NULL marker for each row of the first table whose whole line is empty; RIGHT JOIN = mirrored. I state the limits honestly: the grid assumes ON is an equality and does not cover NULLs already present in the data — a simplification, but better and more precise than Venn circles.
4. Use the anchoring metaphors (full list in references/reference.md): aggregation collapses, windowing annotates; the three ranking functions are three ways of awarding places on a tie (bureaucrat 1,2,3 / same place then skip / same place no gap); LAG and LEAD are looking one row back and forward along the sort order; GROUPING SETS is an instruction list read aloud, and output NULLs are the footprints of a collapsed grouping level that CASE turns into labels; recursion is iteration with an anchor plus a step that consumes only the previous batch; the geo tree makes (id, parent_id) concrete; \s+ fixes both the atomicity violation and the human double-space typo; the 'g' flag is the difference between censoring the first room number and all of them; growing copy-paste is itself a diagnosis; a json dictionary trades row scanning for hash lookup but has a CPU ceiling.
5. Teach over attributes, not literals. I wrap demo values in a subquery (from (select 'Sidorov Ivan Petrovich' as fio) as foo) so the function is seen acting on a column.
6. Close with self-checks. I pick questions from the 53 in references/reference.md and require row-count predictions before execution.

Audience calibration: JOIN semantics and window functions are beginner-to-intermediate; the jsonb antipattern and recursive CTE material assume comfort with CTEs, CTE Scan plan nodes and performance reasoning. Deliberately out of scope: window frame clauses, exotic ON predicates beyond boolean expressions, NULL-valued keys inside GROUPING SETS output, and optimizer internals beyond join-order complexity.

Troubleshooting

Problem 1 — A json extraction query pins a PostgreSQL backend at 100% CPU. Diagnosis: run EXPLAIN ANALYZE and look for repeated CTE Scan nodes and copy-pasted plan fragments; scan the SQL text for the same (TABLE cte) -> key expression appearing once per output field, ->> followed by ::jsonb, and nested replace('[','{')/replace(']','}'). Fix: replace the whole block with a single jsonb_to_record (subset of keys), jsonb_each + jsonb_to_record (all keys), or jsonb_to_recordset (array of record objects), declaring every field name and type exactly once in the column definition list. Drop any LATERAL added for these functions — it is unnecessary. Let PostgreSQL do all conversions, including JSON array → boolean[].

Problem 2 — ERROR: recursive reference to query "r" must not appear within a subquery, or the recursion never ends / returns too little. The recursive reference may appear only in FROM/JOIN, so rewrite WHERE parent_id IN (SELECT id FROM r) as FROM geo JOIN r ON geo.parent_id = r.id. If iteration never stops, the recursive part never yields an empty set: add a terminating predicate (WHERE i < 10), a depth guard on a carried level column, or switch cyclic data from UNION ALL to UNION. If rows are silently missing, UNION deduplicated legitimately repeated rows — use UNION ALL. If the CTE will not compile, the anchor and recursive part have mismatched column counts, order or types.

Problem 3 — A query returns more rows than expected after a join or a split. Count deliberately: with duplicate keys, INNER JOIN multiplies (2×2 duplicates → 4 rows) and LEFT JOIN adds unmatched left rows on top (the (1),(1),(3) / (1),(1),(4),(5) case → 5 rows). regexp_split_to_table multiplies the row count by the number of parts, so downstream aggregates double-count — aggregate after the expansion, or expand in a separate step. For a "missing rows" search, switch from LEFT JOIN ... IS NULL to NOT EXISTS.

Problem 4 — Detail rows lose a column value under GROUPING SETS, or totals scatter. The combined set was split: put (region, district) back as one set and merely add (district) and (). Add ORDER BY district nulls FIRST, region nulls FIRST so total rows land where intended, and label them with the CASE pattern. If the data itself has NULLs in grouped columns, disambiguate with GROUPING() or a sentinel value.