PostgreSQL 17+ Knowledge Patch
Claude's baseline knowledge covers PostgreSQL through 16. This skill provides features from 17 (Sep 2024) onwards.
Source: PostgreSQL release notes at https://www.postgresql.org/docs/release/
PostgreSQL 17 (Sep 2024)
SQL/JSON (Major)
| Function |
Purpose |
Example |
JSON_TABLE() |
JSON → table rows |
FROM JSON_TABLE(data, '$.items[*]' COLUMNS (id int PATH '$.id')) |
JSON() |
Cast text → json |
JSON('{"a":1}') |
JSON_SCALAR() |
Scalar → JSON |
JSON_SCALAR(42) |
JSON_SERIALIZE() |
JSON → text |
JSON_SERIALIZE(jsonb_col) |
JSON_EXISTS() |
Path exists? boolean |
JSON_EXISTS(data, '$.key') |
JSON_VALUE() |
Extract scalar as SQL type |
JSON_VALUE(data, '$.key' RETURNING int) |
JSON_QUERY() |
Extract JSON fragment |
JSON_QUERY(data, '$.arr') |
jsonpath type methods: .bigint(), .boolean(), .date(), .decimal(), .integer(), .number(), .string(), .time(), .time_tz(), .timestamp(), .timestamp_tz()
MERGE Enhancements
WHEN NOT MATCHED BY SOURCE THEN DELETE/UPDATE — act on unmatched target rowsRETURNING merge_action(), * — returns 'INSERT'/'UPDATE'/'DELETE' per row- Works on updatable views
New SQL Syntax
| Feature |
Syntax |
| COPY error skip |
COPY t FROM file WITH (ON_ERROR ignore) |
| Change generated expr |
ALTER TABLE t ALTER COLUMN c SET EXPRESSION AS (expr) |
| Random in range |
random(1, 100) — works for int, bigint, numeric |
| Interval infinity |
'infinity'::interval, '-infinity'::interval |
| Session timezone |
timestamp_col AT LOCAL |
| Optimizer memory |
EXPLAIN (MEMORY) |
| Serialization cost |
EXPLAIN (SERIALIZE) |
New Functions
to_bin(int), to_oct(int), uuid_extract_version(uuid), uuid_extract_timestamp(uuid)
DDL Changes
- Identity columns on partitioned tables (previously unsupported)
- Exclusion constraints on partitioned tables (partition key must use equality)
MAINTAIN privilege for VACUUM/ANALYZE/REINDEX/REFRESH/CLUSTER/LOCKtransaction_timeout GUC — limits total transaction duration
For detailed examples and code samples, consult references/postgresql-17.md.
PostgreSQL 18 (Sep 2025)
Virtual Generated Columns (Major)
Generated columns are now virtual by default (computed at read time, no disk storage). Use STORED for write-time storage.
CREATE TABLE t (
a int,
b int,
total int GENERATED ALWAYS AS (a + b)
);
-- virtual (PG18 default)
CREATE TABLE t (
a int,
b int,
total int GENERATED ALWAYS AS (a + b) STORED
);
-- stored (PG16-17 behavior)
OLD/NEW in RETURNING (Major)
UPDATE t SET val = val + 1 RETURNING old.val AS before, new.val AS after;
DELETE FROM t WHERE id = 1 RETURNING old.*;
MERGE INTO t USING s ON t.id = s.id ... RETURNING merge_action(), old.*, new.*;
Temporal Constraints (WITHOUT OVERLAPS)
| Feature |
Syntax |
| Temporal PK |
PRIMARY KEY (id, range_col WITHOUT OVERLAPS) |
| Temporal UNIQUE |
UNIQUE (id, range_col WITHOUT OVERLAPS) |
| Temporal FK |
FOREIGN KEY (id, PERIOD range_col) REFERENCES parent (id, PERIOD range_col) |
Requires btree_gist extension.
NOT ENFORCED Constraints
ALTER TABLE t
ADD CHECK (val > 0) NOT ENFORCED;
ALTER TABLE t
ADD FOREIGN KEY (x) REFERENCES r NOT ENFORCED;
New Functions
| Function |
Purpose |
Example |
uuidv7() |
Timestamp-ordered UUID |
SELECT uuidv7() |
casefold(text) |
Unicode case folding |
casefold('Straße') = casefold('STRASSE') |
array_sort(anyarray) |
Sort array |
array_sort(ARRAY[3,1,2]) → {1,2,3} |
array_reverse(anyarray) |
Reverse array |
array_reverse(ARRAY[1,2,3]) → {3,2,1} |
crc32(bytea) |
CRC32 checksum |
crc32('hello'::bytea) |
crc32c(bytea) |
CRC32C checksum |
crc32c('hello'::bytea) |
Data Type Changes
- jsonb null casting:
('null'::jsonb)::int → NULL (was error pre-18)
- Integer ↔ bytea casting:
255::int2::bytea → \x00ff, '\x00ff'::bytea::int2 → 255
json{b}_strip_nulls(json, strip_in_arrays) — optional array null stripping
New SQL Syntax
| Feature |
Syntax |
| COPY reject limit |
COPY t FROM file WITH (ON_ERROR ignore, REJECT_LIMIT 100) |
| VACUUM only parent |
VACUUM (ONLY) partitioned_table |
| ANALYZE only parent |
ANALYZE (ONLY) partitioned_table |
Breaking Changes
EXPLAIN ANALYZE now auto-includes BUFFERS outputinitdb enables data checksums by default (--no-data-checksums to disable)COPY FROM CSV no longer treats \. as EOF marker- Generated columns default to virtual (not stored)
- NOT NULL constraints now in
pg_constraint, can have names
For detailed examples and code samples, consult references/postgresql-18.md.
Reference Files
For extended documentation with full code examples:
references/postgresql-17.md — JSON_TABLE, SQL/JSON functions, MERGE, COPY ON_ERROR, and more with detailed usage examplesreferences/postgresql-18.md — Virtual generated columns, OLD/NEW in RETURNING, temporal constraints, NOT ENFORCED constraints, and more with detailed usage examples
Converted and distributed by TomeVault — claim your Tome and manage your conversions.
1---2name: nevaberry-nevaberry-plugins-postgresql-knowledge-patch3description: PostgreSQL 17+ Knowledge Patch4---56# PostgreSQL 17+ Knowledge Patch78Claude's baseline knowledge covers PostgreSQL through 16. This skill provides features from 17 (Sep 2024) onwards.910**Source**: PostgreSQL release notes at https://www.postgresql.org/docs/release/1112## PostgreSQL 17 (Sep 2024)1314### SQL/JSON (Major)1516| Function | Purpose | Example |17|----------|---------|---------|18| `JSON_TABLE()` | JSON → table rows | `FROM JSON_TABLE(data, '$.items[*]' COLUMNS (id int PATH '$.id'))` |19| `JSON()` | Cast text → json | `JSON('{"a":1}')` |20| `JSON_SCALAR()` | Scalar → JSON | `JSON_SCALAR(42)` |21| `JSON_SERIALIZE()` | JSON → text | `JSON_SERIALIZE(jsonb_col)` |22| `JSON_EXISTS()` | Path exists? boolean | `JSON_EXISTS(data, '$.key')` |23| `JSON_VALUE()` | Extract scalar as SQL type | `JSON_VALUE(data, '$.key' RETURNING int)` |24| `JSON_QUERY()` | Extract JSON fragment | `JSON_QUERY(data, '$.arr')` |2526jsonpath type methods: `.bigint()`, `.boolean()`, `.date()`, `.decimal()`, `.integer()`, `.number()`, `.string()`, `.time()`, `.time_tz()`, `.timestamp()`, `.timestamp_tz()`2728### MERGE Enhancements2930- `WHEN NOT MATCHED BY SOURCE THEN DELETE/UPDATE` — act on unmatched target rows31- `RETURNING merge_action(), *` — returns 'INSERT'/'UPDATE'/'DELETE' per row32- Works on updatable views3334### New SQL Syntax3536| Feature | Syntax |37|---------|--------|38| COPY error skip | `COPY t FROM file WITH (ON_ERROR ignore)` |39| Change generated expr | `ALTER TABLE t ALTER COLUMN c SET EXPRESSION AS (expr)` |40| Random in range | `random(1, 100)` — works for int, bigint, numeric |41| Interval infinity | `'infinity'::interval`, `'-infinity'::interval` |42| Session timezone | `timestamp_col AT LOCAL` |43| Optimizer memory | `EXPLAIN (MEMORY)` |44| Serialization cost | `EXPLAIN (SERIALIZE)` |4546### New Functions4748`to_bin(int)`, `to_oct(int)`, `uuid_extract_version(uuid)`, `uuid_extract_timestamp(uuid)`4950### DDL Changes5152- Identity columns on partitioned tables (previously unsupported)53- Exclusion constraints on partitioned tables (partition key must use equality)54- `MAINTAIN` privilege for VACUUM/ANALYZE/REINDEX/REFRESH/CLUSTER/LOCK55- `transaction_timeout` GUC — limits total transaction duration5657For detailed examples and code samples, consult **`references/postgresql-17.md`**.5859## PostgreSQL 18 (Sep 2025)6061### Virtual Generated Columns (Major)6263Generated columns are now **virtual by default** (computed at read time, no disk storage). Use `STORED` for write-time storage.6465```sql66CREATE TABLE t (67 a int,68 b int,69 total int GENERATED ALWAYS AS (a + b)70);7172-- virtual (PG18 default)73CREATE TABLE t (74 a int,75 b int,76 total int GENERATED ALWAYS AS (a + b) STORED77);7879-- stored (PG16-17 behavior)80```8182### OLD/NEW in RETURNING (Major)8384```sql85UPDATE t SET val = val + 1 RETURNING old.val AS before, new.val AS after;86DELETE FROM t WHERE id = 1 RETURNING old.*;87MERGE INTO t USING s ON t.id = s.id ... RETURNING merge_action(), old.*, new.*;88```8990### Temporal Constraints (WITHOUT OVERLAPS)91| Feature | Syntax |92|---------|--------|93| Temporal PK | `PRIMARY KEY (id, range_col WITHOUT OVERLAPS)` |94| Temporal UNIQUE | `UNIQUE (id, range_col WITHOUT OVERLAPS)` |95| Temporal FK | `FOREIGN KEY (id, PERIOD range_col) REFERENCES parent (id, PERIOD range_col)` |9697Requires `btree_gist` extension.9899### NOT ENFORCED Constraints100101```sql102ALTER TABLE t103ADD CHECK (val > 0) NOT ENFORCED;104105ALTER TABLE t106ADD FOREIGN KEY (x) REFERENCES r NOT ENFORCED;107```108109### New Functions110111| Function | Purpose | Example |112|----------|---------|---------|113| `uuidv7()` | Timestamp-ordered UUID | `SELECT uuidv7()` |114| `casefold(text)` | Unicode case folding | `casefold('Straße') = casefold('STRASSE')` |115| `array_sort(anyarray)` | Sort array | `array_sort(ARRAY[3,1,2])` → `{1,2,3}` |116| `array_reverse(anyarray)` | Reverse array | `array_reverse(ARRAY[1,2,3])` → `{3,2,1}` |117| `crc32(bytea)` | CRC32 checksum | `crc32('hello'::bytea)` |118| `crc32c(bytea)` | CRC32C checksum | `crc32c('hello'::bytea)` |119120### Data Type Changes121122- **jsonb null casting**: `('null'::jsonb)::int` → `NULL` (was error pre-18)123- **Integer ↔ bytea casting**: `255::int2::bytea` → `\x00ff`, `'\x00ff'::bytea::int2` → `255`124- `json{b}_strip_nulls(json, strip_in_arrays)` — optional array null stripping125126### New SQL Syntax127128| Feature | Syntax |129|---------|--------|130| COPY reject limit | `COPY t FROM file WITH (ON_ERROR ignore, REJECT_LIMIT 100)` |131| VACUUM only parent | `VACUUM (ONLY) partitioned_table` |132| ANALYZE only parent | `ANALYZE (ONLY) partitioned_table` |133134### Breaking Changes135136- `EXPLAIN ANALYZE` now auto-includes `BUFFERS` output137- `initdb` enables data checksums by default (`--no-data-checksums` to disable)138- `COPY FROM` CSV no longer treats `\.` as EOF marker139- Generated columns default to virtual (not stored)140- NOT NULL constraints now in `pg_constraint`, can have names141142For detailed examples and code samples, consult **`references/postgresql-18.md`**.143144## Reference Files145146For extended documentation with full code examples:147- **`references/postgresql-17.md`** — JSON_TABLE, SQL/JSON functions, MERGE, COPY ON_ERROR, and more with detailed usage examples148- **`references/postgresql-18.md`** — Virtual generated columns, OLD/NEW in RETURNING, temporal constraints, NOT ENFORCED constraints, and more with detailed usage examples149150---151> Converted and distributed by [TomeVault](https://tomevault.io/claim/nevaberry) — claim your Tome and manage your conversions.152<!-- tomevault:4.0:skill_md:2026-04-11 -->
tomevault-io