# Postgres Advanced SQL > Advanced SQL Techniques in PostgreSQL and Greenplum - Skill: `mikerrr/postgres-advanced-sql` (Agent Skill, multi-file: 2 files) - Install (CLI): `npx skillmds@latest add mikerrr/postgres-advanced-sql` - Raw SKILL.md: https://api.skillmd.com/api/skills/mikerrr/postgres-advanced-sql/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Data & Analytics - Author: mikerrr (https://skillmd.com/u/mikerrr) - Updated: 2026-09-22 - Page: https://skillmd.com/skills/mikerrr/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 | Symptom / request | Part | |---|---| | Repeated `->>` + `::type` chains, `replace('[','{')`, high CPU on JSON | A | | Match / extract / split / replace by pattern | B | | Subtotals, grand totals, one query instead of UNIONs | C | | Per-group calculation without losing rows, ranks, previous/next value | D | | Unexpected row counts from joins, "intersection" confusion, anti-joins, join performance | E | | Trees `(id, parent_id)`, iterative computation, `WITH RECURSIVE` errors | F | --- ## 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: ```sql , (((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: ```sql 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: ```sql 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: ```sql 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: | Task | Tool | |---|---| | Match a simplified pattern | `LIKE` (`'Ivan%'`) — **no regex support** | | Filter rows by a regex | `SIMILAR TO` | | Pull out a matching substring | `substring(attr, pattern)` | | Split into an array | `regexp_split_to_array(str, pattern)` | | Split and expand into rows | `regexp_split_to_table(str, pattern)` | | Replace a matching substring | `regexp_replace(str, pattern, repl [, flags])` | ```sql -- 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: ```sql 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: ```sql 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): ```sql 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. ```sql 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). ```sql 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: ```sql 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. ```sql 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. ```sql 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: ```sql 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: ```sql 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.