PostgreSQL 17+ Knowledge Patch Skill

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

Agent Skills: PostgreSQL 17+ Knowledge Patch

Install this agent skill to your local

Skill Files