↑↓ select ↵ open ⌫ change scope Open full search

PG.CENTER connects PostgreSQL documentation, reference, and ecosystem knowledge. Maintained by Pigsty.

SQL COMMANDS / VERSION COMPARISON

What changed?

SQL Commands · PostgreSQL 17 → 18

Browse PG 18 →

0 added · 0 removed · 13 changed · 0 coverage changes. These are changes in the pinned reference snapshots.

changedCREATE SEQUENCE
--- 17
+++ 18
@@ -2,5 +2,7 @@
 [ AS data_type ]
 [ INCREMENT [ BY ] increment ]
 [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
-[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
+[ [ NO ] CYCLE ]
+[ START [ WITH ] start ]
+[ CACHE cache ]
 [ OWNED BY { table_name.column_name | NONE } ]
changedALTER SEQUENCE
--- 17
+++ 18
@@ -2,9 +2,10 @@
 [ AS data_type ]
 [ INCREMENT [ BY ] increment ]
 [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
+[ [ NO ] CYCLE ]
 [ START [ WITH ] start ]
 [ RESTART [ [ WITH ] restart ] ]
-[ CACHE cache ] [ [ NO ] CYCLE ]
+[ CACHE cache ]
 [ OWNED BY { table_name.column_name | NONE } ]
 ALTER SEQUENCE [ IF EXISTS ] name SET { LOGGED | UNLOGGED }
 ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
changedCREATE TABLE
--- 17
+++ 18
@@ -38,28 +38,29 @@
 where column_constraint is:
 
 [ CONSTRAINT constraint_name ]
-{ NOT NULL |
+{ NOT NULL [ NO INHERIT ] |
 NULL |
 CHECK ( expression ) [ NO INHERIT ] |
 DEFAULT default_expr |
-GENERATED ALWAYS AS ( generation_expr ) STORED |
+GENERATED ALWAYS AS ( generation_expr ) [ STORED | VIRTUAL ] |
 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
 UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters |
 PRIMARY KEY index_parameters |
 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
 [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
-[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]
 
 and table_constraint is:
 
 [ CONSTRAINT constraint_name ]
 { CHECK ( expression ) [ NO INHERIT ] |
-UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |
-PRIMARY KEY ( column_name [, ... ] ) index_parameters |
+NOT NULL column_name [ NO INHERIT ] |
+UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters |
+PRIMARY KEY ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters |
 EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
-FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
+FOREIGN KEY ( column_name [, ... ] [, PERIOD column_name ] ) REFERENCES reftable [ ( refcolumn [, ... ] [, PERIOD refcolumn ] ) ]
 [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
-[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]
 
 and like_option is:
changedALTER TABLE
--- 17
+++ 18
@@ -35,7 +35,8 @@
 ALTER [ COLUMN ] column_name SET COMPRESSION compression_method
 ADD table_constraint [ NOT VALID ]
 ADD table_constraint_using_index
-ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]
+ALTER CONSTRAINT constraint_name [ INHERIT | NO INHERIT ]
 VALIDATE CONSTRAINT constraint_name
 DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]
 DISABLE TRIGGER [ trigger_name | ALL | USER ]
@@ -75,28 +76,29 @@
 and column_constraint is:
 
 [ CONSTRAINT constraint_name ]
-{ NOT NULL |
+{ NOT NULL [ NO INHERIT ] |
 NULL |
 CHECK ( expression ) [ NO INHERIT ] |
 DEFAULT default_expr |
-GENERATED ALWAYS AS ( generation_expr ) STORED |
+GENERATED ALWAYS AS ( generation_expr ) [ STORED | VIRTUAL ] |
 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
 UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters |
 PRIMARY KEY index_parameters |
 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
 [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
-[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]
 
 and table_constraint is:
 
 [ CONSTRAINT constraint_name ]
 { CHECK ( expression ) [ NO INHERIT ] |
-UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |
-PRIMARY KEY ( column_name [, ... ] ) index_parameters |
+NOT NULL column_name [ NO INHERIT ] |
+UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters |
+PRIMARY KEY ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters |
 EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
-FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
+FOREIGN KEY ( column_name [, ... ] [, PERIOD column_name ] ) REFERENCES reftable [ ( refcolumn [, ... ] [, PERIOD refcolumn ] ) ]
 [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
-[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]
 
 and table_constraint_using_index is:
changedALTER DEFAULT PRIVILEGES
--- 17
+++ 18
@@ -25,6 +25,11 @@
 GRANT { { USAGE | CREATE }
 [, ...] | ALL [ PRIVILEGES ] }
 ON SCHEMAS
+TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+
+GRANT { { SELECT | UPDATE }
+[, ...] | ALL [ PRIVILEGES ] }
+ON LARGE OBJECTS
 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
 REVOKE [ GRANT OPTION FOR ]
@@ -59,3 +64,10 @@
 ON SCHEMAS
 FROM { [ GROUP ] role_name | PUBLIC } [, ...]
 [ CASCADE | RESTRICT ]
+
+REVOKE [ GRANT OPTION FOR ]
+{ { SELECT | UPDATE }
+[, ...] | ALL [ PRIVILEGES ] }
+ON LARGE OBJECTS
+FROM { [ GROUP ] role_name | PUBLIC } [, ...]
+[ CASCADE | RESTRICT ]
changedCREATE FOREIGN TABLE
--- 17
+++ 18
@@ -1,6 +1,7 @@
 CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
 { column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
-| table_constraint }
+| table_constraint
+| LIKE source_table [ like_option ... ] }
 [, ... ]
 ] )
 [ INHERITS ( parent_table [, ... ] ) ]
@@ -20,16 +21,23 @@
 where column_constraint is:
 
 [ CONSTRAINT constraint_name ]
-{ NOT NULL |
+{ NOT NULL [ NO INHERIT ] |
 NULL |
 CHECK ( expression ) [ NO INHERIT ] |
 DEFAULT default_expr |
-GENERATED ALWAYS AS ( generation_expr ) STORED }
+GENERATED ALWAYS AS ( generation_expr ) [ STORED | VIRTUAL ] }
+[ ENFORCED | NOT ENFORCED ]
 
 and table_constraint is:
 
 [ CONSTRAINT constraint_name ]
-CHECK ( expression ) [ NO INHERIT ]
+{ NOT NULL column_name [ NO INHERIT ] |
+CHECK ( expression ) [ NO INHERIT ] }
+[ ENFORCED | NOT ENFORCED ]
+
+and like_option is:
+
+{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | ALL }
 
 and partition_bound_spec is:
changedCOPY
--- 17
+++ 18
@@ -21,5 +21,6 @@
 FORCE_NOT_NULL { ( column_name [, ...] ) | * }
 FORCE_NULL { ( column_name [, ...] ) | * }
 ON_ERROR error_action
+REJECT_LIMIT maxerror
 ENCODING 'encoding_name'
 LOG_VERBOSITY verbosity
changedDELETE
--- 17
+++ 18
@@ -2,4 +2,5 @@
 DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
 [ USING from_item [, ...] ]
 [ WHERE condition | WHERE CURRENT OF cursor_name ]
-[ RETURNING { * | output_expression [ [ AS ] output_name ] } [, ...] ]
+[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]
+{ * | output_expression [ [ AS ] output_name ] } [, ...] ]
changedINSERT
--- 17
+++ 18
@@ -3,7 +3,8 @@
 [ OVERRIDING { SYSTEM | USER } VALUE ]
 { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
 [ ON CONFLICT [ conflict_target ] conflict_action ]
-[ RETURNING { * | output_expression [ [ AS ] output_name ] } [, ...] ]
+[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]
+{ * | output_expression [ [ AS ] output_name ] } [, ...] ]
 
 where conflict_target can be one of:
changedMERGE
--- 17
+++ 18
@@ -2,7 +2,8 @@
 MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
 USING data_source ON join_condition
 when_clause [...]
-[ RETURNING { * | output_expression [ [ AS ] output_name ] } [, ...] ]
+[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]
+{ * | output_expression [ [ AS ] output_name ] } [, ...] ]
 
 where data_source is:
changedUPDATE
--- 17
+++ 18
@@ -6,4 +6,5 @@
 } [, ...]
 [ FROM from_item [, ...] ]
 [ WHERE condition | WHERE CURRENT OF cursor_name ]
-[ RETURNING { * | output_expression [ [ AS ] output_name ] } [, ...] ]
+[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]
+{ * | output_expression [ [ AS ] output_name ] } [, ...] ]
changedANALYZE
--- 17
+++ 18
@@ -6,4 +6,5 @@
 SKIP_LOCKED [ boolean ]
 BUFFER_USAGE_LIMIT size
 and table_and_columns is:
-table_name [ ( column_name [, ...] ) ]
+
+[ ONLY ] table_name [ * ] [ ( column_name [, ...] ) ]
changedVACUUM
--- 17
+++ 18
@@ -17,4 +17,5 @@
 ONLY_DATABASE_STATS [ boolean ]
 BUFFER_USAGE_LIMIT size
 and table_and_columns is:
-table_name [ ( column_name [, ...] ) ]
+
+[ ONLY ] table_name [ * ] [ ( column_name [, ...] ) ]