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 16 → 17

Browse PG 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 verbosity
changedEXPLAIN
--- 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: