SQL COMMANDS / VERSION COMPARISON
What changed?
SQL Commands · PostgreSQL 14 → 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_schemachangedCREATE 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_parameterchangedGRANT
--- 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_namechangedCOPY
--- 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