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 14 → 15

Browse PG 15 →

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

changedALTER MATERIALIZED VIEW
--- 14
+++ 15
@@ -20,6 +20,7 @@
 ALTER [ COLUMN ] column_name SET COMPRESSION compression_method
 CLUSTER ON index_name
 SET WITHOUT CLUSTER
+SET ACCESS METHOD new_access_method
 SET TABLESPACE new_tablespace
 SET ( storage_parameter [= value] [, ... ] )
 RESET ( storage_parameter [, ... ] )
changedCREATE SEQUENCE
--- 14
+++ 15
@@ -1,4 +1,4 @@
-CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name
+CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] name
 [ AS data_type ]
 [ INCREMENT [ BY ] increment ]
 [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
changedALTER SEQUENCE
--- 14
+++ 15
@@ -6,6 +6,7 @@
 [ RESTART [ [ WITH ] restart ] ]
 [ CACHE cache ] [ [ NO ] CYCLE ]
 [ 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 }
 ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name
 ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema
changedCREATE TABLE
--- 14
+++ 15
@@ -44,7 +44,7 @@
 DEFAULT default_expr |
 GENERATED ALWAYS AS ( generation_expr ) STORED |
 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
-UNIQUE index_parameters |
+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 ] }
@@ -54,7 +54,7 @@
 
 [ CONSTRAINT constraint_name ]
 { CHECK ( expression ) [ NO INHERIT ] |
-UNIQUE ( column_name [, ... ] ) index_parameters |
+UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |
 PRIMARY KEY ( column_name [, ... ] ) index_parameters |
 EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
@@ -81,3 +81,7 @@
 exclude_element in an EXCLUDE constraint is:
 
 { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
+
+referential_action in a FOREIGN KEY/REFERENCES constraint is:
+
+{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( column_name [, ... ] ) ] | SET DEFAULT [ ( column_name [, ... ] ) ] }
changedALTER TABLE
--- 14
+++ 15
@@ -52,6 +52,7 @@
 CLUSTER ON index_name
 SET WITHOUT CLUSTER
 SET WITHOUT OIDS
+SET ACCESS METHOD new_access_method
 SET TABLESPACE new_tablespace
 SET { LOGGED | UNLOGGED }
 SET ( storage_parameter [= value] [, ... ] )
@@ -79,7 +80,7 @@
 DEFAULT default_expr |
 GENERATED ALWAYS AS ( generation_expr ) STORED |
 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
-UNIQUE index_parameters |
+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 ] }
@@ -89,7 +90,7 @@
 
 [ CONSTRAINT constraint_name ]
 { CHECK ( expression ) [ NO INHERIT ] |
-UNIQUE ( column_name [, ... ] ) index_parameters |
+UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |
 PRIMARY KEY ( column_name [, ... ] ) index_parameters |
 EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
@@ -111,3 +112,7 @@
 exclude_element in an EXCLUDE constraint is:
 
 { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
+
+referential_action in a FOREIGN KEY/REFERENCES constraint is:
+
+{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( column_name [, ... ] ) ] | SET DEFAULT [ ( column_name [, ... ] ) ] }
changedCREATE INDEX
--- 14
+++ 15
@@ -1,6 +1,7 @@
 CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
 ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
 [ INCLUDE ( column_name [, ...] ) ]
+[ NULLS [ NOT ] DISTINCT ]
 [ WITH ( storage_parameter [= value] [, ... ] ) ]
 [ TABLESPACE tablespace_name ]
 [ WHERE predicate ]
changedCREATE DATABASE
--- 14
+++ 15
@@ -2,10 +2,15 @@
 [ WITH ] [ OWNER [=] user_name ]
 [ TEMPLATE [=] template ]
 [ ENCODING [=] encoding ]
+[ STRATEGY [=] strategy ]
 [ LOCALE [=] locale ]
 [ LC_COLLATE [=] lc_collate ]
 [ LC_CTYPE [=] lc_ctype ]
+[ ICU_LOCALE [=] icu_locale ]
+[ LOCALE_PROVIDER [=] locale_provider ]
+[ COLLATION_VERSION = collation_version ]
 [ TABLESPACE [=] tablespace_name ]
 [ ALLOW_CONNECTIONS [=] allowconn ]
 [ CONNECTION LIMIT [=] connlimit ]
 [ IS_TEMPLATE [=] istemplate ]
+[ OID [=] oid ]
changedALTER DATABASE
--- 14
+++ 15
@@ -12,6 +12,8 @@
 
 ALTER DATABASE name SET TABLESPACE new_tablespace
 
+ALTER DATABASE name REFRESH COLLATION VERSION
+
 ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
 ALTER DATABASE name SET configuration_parameter FROM CURRENT
 ALTER DATABASE name RESET configuration_parameter
changedGRANT
--- 14
+++ 15
@@ -54,6 +54,11 @@
 TO role_specification [, ...] [ WITH GRANT OPTION ]
 [ GRANTED BY role_specification ]
 
+GRANT { { SET | ALTER SYSTEM } [, ... ] | ALL [ PRIVILEGES ] }
+ON PARAMETER configuration_parameter [, ...]
+TO role_specification [, ...] [ WITH GRANT OPTION ]
+[ GRANTED BY role_specification ]
+
 GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
 ON SCHEMA schema_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]
changedREVOKE
--- 14
+++ 15
@@ -75,6 +75,13 @@
 [ CASCADE | RESTRICT ]
 
 REVOKE [ GRANT OPTION FOR ]
+{ { SET | ALTER SYSTEM } [, ...] | ALL [ PRIVILEGES ] }
+ON PARAMETER configuration_parameter [, ...]
+FROM role_specification [, ...]
+[ GRANTED BY role_specification ]
+[ CASCADE | RESTRICT ]
+
+REVOKE [ GRANT OPTION FOR ]
 { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
 ON SCHEMA schema_name [, ...]
 FROM role_specification [, ...]
changedCREATE PUBLICATION
--- 14
+++ 15
@@ -1,4 +1,13 @@
 CREATE PUBLICATION name
-[ FOR TABLE [ ONLY ] table_name [ * ] [, ...]
-| FOR ALL TABLES ]
+[ FOR ALL TABLES
+| FOR publication_object [, ... ] ]
 [ WITH ( publication_parameter [= value] [, ... ] ) ]
+
+where publication_object is one of:
+
+TABLE table_and_columns [, ... ]
+TABLES IN SCHEMA { schema_name | CURRENT_SCHEMA } [, ... ]
+
+and table_and_columns is:
+
+[ ONLY ] table_name [ * ] [ ( column_name [, ... ] ) ] [ WHERE ( expression ) ]
changedALTER PUBLICATION
--- 14
+++ 15
@@ -1,6 +1,19 @@
-ALTER PUBLICATION name ADD TABLE [ ONLY ] table_name [ * ] [, ...]
-ALTER PUBLICATION name SET TABLE [ ONLY ] table_name [ * ] [, ...]
-ALTER PUBLICATION name DROP TABLE [ ONLY ] table_name [ * ] [, ...]
+ALTER PUBLICATION name ADD publication_object [, ...]
+ALTER PUBLICATION name SET publication_object [, ...]
+ALTER PUBLICATION name DROP publication_drop_object [, ...]
 ALTER PUBLICATION name SET ( publication_parameter [= value] [, ... ] )
 ALTER PUBLICATION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
 ALTER PUBLICATION name RENAME TO new_name
+where publication_object is one of:
+
+TABLE table_and_columns [, ... ]
+TABLES IN SCHEMA { schema_name | CURRENT_SCHEMA } [, ... ]
+
+and publication_drop_object is one of:
+
+TABLE [ ONLY ] table_name [ * ] [, ... ]
+TABLES IN SCHEMA { schema_name | CURRENT_SCHEMA } [, ... ]
+
+and table_and_columns is:
+
+[ ONLY ] table_name [ * ] [ ( column_name [, ... ] ) ] [ WHERE ( expression ) ]
changedALTER SUBSCRIPTION
--- 14
+++ 15
@@ -6,5 +6,6 @@
 ALTER SUBSCRIPTION name ENABLE
 ALTER SUBSCRIPTION name DISABLE
 ALTER SUBSCRIPTION name SET ( subscription_parameter [= value] [, ... ] )
+ALTER SUBSCRIPTION name SKIP ( skip_option = value )
 ALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
 ALTER SUBSCRIPTION name RENAME TO new_name
changedCOPY
--- 14
+++ 15
@@ -13,7 +13,7 @@
 FREEZE [ boolean ]
 DELIMITER 'delimiter_character'
 NULL 'null_string'
-HEADER [ boolean ]
+HEADER [ boolean | MATCH ]
 QUOTE 'quote_character'
 ESCAPE 'escape_character'
 FORCE_QUOTE { ( column_name [, ...] ) | * }
addedMERGE
--- 14
+++ 15
@@ -0,0 +1,30 @@
+[ WITH with_query [, ...] ]
+MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
+USING data_source ON join_condition
+when_clause [...]
+
+where data_source is:
+
+{ [ ONLY ] source_table_name [ * ] | ( source_query ) } [ [ AS ] source_alias ]
+
+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 } }
+
+and merge_insert is:
+
+INSERT [( column_name [, ...] )]
+[ OVERRIDING { SYSTEM | USER } VALUE ]
+{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }
+
+and merge_update is:
+
+UPDATE SET { column_name = { expression | DEFAULT } |
+( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
+( column_name [, ...] ) = ( sub-SELECT )
+} [, ...]
+
+and merge_delete is:
+
+DELETE