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)
MAINTAINprivilege for VACUUM/ANALYZE/REINDEX/REFRESH/CLUSTER/LOCKtransaction_timeoutGUC — 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 ANALYZEnow auto-includesBUFFERSoutputinitdbenables data checksums by default (--no-data-checksumsto disable)COPY FROMCSV 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