SQL COMMANDS / VERSION COMPARISON
What changed?
SQL Commands · PostgreSQL 16 → 17
0 added · 0 removed · 16 changed · 0 coverage changes. These are changes in the pinned reference snapshots.
changedALTER TABLE
--- 16
+++ 17
@@ -23,11 +23,12 @@
ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
+ALTER [ COLUMN ] column_name SET EXPRESSION AS ( expression )
ALTER [ COLUMN ] column_name DROP EXPRESSION [ IF EXISTS ]
ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]
ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]
-ALTER [ COLUMN ] column_name SET STATISTICS integer
+ALTER [ COLUMN ] column_name SET STATISTICS { integer | DEFAULT }
ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }
@@ -52,7 +53,7 @@
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITHOUT OIDS
-SET ACCESS METHOD new_access_method
+SET ACCESS METHOD { new_access_method | DEFAULT }
SET TABLESPACE new_tablespace
SET { LOGGED | UNLOGGED }
SET ( storage_parameter [= value] [, ... ] )changedALTER STATISTICS
--- 16
+++ 17
@@ -1,4 +1,4 @@
ALTER STATISTICS name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER STATISTICS name RENAME TO new_name
ALTER STATISTICS name SET SCHEMA new_schema
-ALTER STATISTICS name SET STATISTICS new_target
+ALTER STATISTICS name SET STATISTICS { new_target | DEFAULT }changedCREATE DOMAIN
--- 16
+++ 17
@@ -1,9 +1,9 @@
CREATE DOMAIN name [ AS ] data_type
[ COLLATE collation ]
[ DEFAULT expression ]
-[ constraint [ ... ] ]
+[ domain_constraint [ ... ] ]
-where constraint is:
+where domain_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }changedALTER OPERATOR
--- 16
+++ 17
@@ -7,4 +7,8 @@
ALTER OPERATOR name ( { left_type | NONE } , right_type )
SET ( { RESTRICT = { res_proc | NONE }
| JOIN = { join_proc | NONE }
+| COMMUTATOR = com_op
+| NEGATOR = neg_op
+| HASHES
+| MERGES
} [, ... ] )changedCREATE DATABASE
--- 16 +++ 17 @@ -6,6 +6,7 @@ [ LOCALE [=] locale ] [ LC_COLLATE [=] lc_collate ] [ LC_CTYPE [=] lc_ctype ] +[ BUILTIN_LOCALE [=] builtin_locale ] [ ICU_LOCALE [=] icu_locale ] [ ICU_RULES [=] icu_rules ] [ LOCALE_PROVIDER [=] locale_provider ]
changedGRANT
--- 16
+++ 17
@@ -1,4 +1,4 @@
-GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
+GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }changedREVOKE
--- 16
+++ 17
@@ -1,5 +1,5 @@
REVOKE [ GRANT OPTION FOR ]
-{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
+{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }changedALTER DEFAULT PRIVILEGES
--- 16
+++ 17
@@ -4,7 +4,7 @@
abbreviated_grant_or_revoke
where abbreviated_grant_or_revoke is one of:
-GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
+GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
@@ -28,7 +28,7 @@
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ]
-{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
+{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
FROM { [ GROUP ] role_name | PUBLIC } [, ...]changedCREATE ROLE
--- 16 +++ 17 @@ -13,8 +13,6 @@ | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...] -| IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] -| USER role_name [, ...] | SYSID uid
changedCOPY
--- 16
+++ 17
@@ -18,6 +18,8 @@
QUOTE 'quote_character'
ESCAPE 'escape_character'
FORCE_QUOTE { ( column_name [, ...] ) | * }
-FORCE_NOT_NULL ( column_name [, ...] )
-FORCE_NULL ( column_name [, ...] )
+FORCE_NOT_NULL { ( column_name [, ...] ) | * }
+FORCE_NULL { ( column_name [, ...] ) | * }
+ON_ERROR error_action
ENCODING 'encoding_name'
+LOG_VERBOSITY verbositychangedEXPLAIN
--- 16
+++ 17
@@ -1,5 +1,4 @@
EXPLAIN [ ( option [, ...] ) ] statement
-EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
where option can be one of:
ANALYZE [ boolean ]
@@ -8,7 +7,9 @@
SETTINGS [ boolean ]
GENERIC_PLAN [ boolean ]
BUFFERS [ boolean ]
+SERIALIZE [ { NONE | TEXT | BINARY } ]
WAL [ boolean ]
TIMING [ boolean ]
SUMMARY [ boolean ]
+MEMORY [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }changedMERGE
--- 16
+++ 17
@@ -2,6 +2,7 @@
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
USING data_source ON join_condition
when_clause [...]
+[ RETURNING { * | output_expression [ [ AS ] output_name ] } [, ...] ]
where data_source is:
@@ -10,7 +11,8 @@
and when_clause is:
{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
-WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING } }
+WHEN NOT MATCHED BY SOURCE [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
+WHEN NOT MATCHED [ BY TARGET ] [ AND condition ] THEN { merge_insert | DO NOTHING } }
and merge_insert is:
changedSELECT
--- 16
+++ 17
@@ -11,7 +11,7 @@
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]
-[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
+[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF from_reference [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
where from_item can be one of:
@@ -38,7 +38,7 @@
GROUPING SETS ( grouping_element [, ...] )
and with_query is:
-with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( select | values | insert | update | delete )
+with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( select | values | insert | update | delete | merge )
[ SEARCH { BREADTH | DEPTH } FIRST BY column_name [, ...] SET search_seq_col_name ]
[ CYCLE column_name [, ...] SET cycle_mark_col_name [ TO cycle_mark_value DEFAULT cycle_mark_default ] USING cycle_path_col_name ]
changedANALYZE
--- 16 +++ 17 @@ -1,5 +1,4 @@ ANALYZE [ ( option [, ...] ) ] [ table_and_columns [, ...] ] -ANALYZE [ VERBOSE ] [ table_and_columns [, ...] ] where option can be one of:
changedCLUSTER
--- 16 +++ 17 @@ -1,6 +1,4 @@ -CLUSTER [VERBOSE] table_name [ USING index_name ] -CLUSTER ( option [, ...] ) table_name [ USING index_name ] -CLUSTER [VERBOSE] +CLUSTER [ ( option [, ...] ) ] [ table_name [ USING index_name ] ] where option can be one of:
changedVACUUM
--- 16 +++ 17 @@ -1,5 +1,4 @@ VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ] -VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ] where option can be one of: