postgresql-knowledge-patch

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 rows
• RETURNING 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/LOCK
• transaction_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 output
• initdb 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 examples
• references/postgresql-18.md — Virtual generated columns, OLD/NEW in RETURNING, temporal constraints, NOT ENFORCED constraints, and more with detailed usage examples