postgresql-knowledge-patch

📁 nevaberry/nevaberry-plugins 📅 4 days ago

总安装量

周安装量

#44407

全站排名

安装命令

npx skills add https://github.com/nevaberry/nevaberry-plugins --skill postgresql-knowledge-patch

Agent 安装分布

amp 1

opencode 1

kimi-cli 1

github-copilot 1

claude-code 1

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

GitHub 仓库 ↗ ← 返回陌讯 Skills 聚合平台