SQL COMMANDS / VERSION COMPARISON

What changed?

SQL Commands · PostgreSQL 12 → 13

Historical documentation for a PostgreSQL version that is no longer supported.

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

--- 12
+++ 13
@@ -1,7 +1,7 @@
 ALTER MATERIALIZED VIEW [ IF EXISTS ] name
 action [, ... ]
 ALTER MATERIALIZED VIEW name
-DEPENDS ON EXTENSION extension_name
+[ NO ] DEPENDS ON EXTENSION extension_name
 ALTER MATERIALIZED VIEW [ IF EXISTS ] name
 RENAME [ COLUMN ] column_name TO new_column_name
 ALTER MATERIALIZED VIEW [ IF EXISTS ] name

changedCREATE TABLE

--- 12
+++ 13
@@ -80,4 +80,4 @@
 
 exclude_element in an EXCLUDE constraint is:
 
-{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
+{ column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

changedALTER TABLE

--- 12
+++ 13
@@ -22,6 +22,7 @@
 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 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 ]
@@ -107,4 +108,4 @@
 
 exclude_element in an EXCLUDE constraint is:
 
-{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
+{ column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

changedALTER VIEW

--- 12
+++ 13
@@ -1,6 +1,7 @@
 ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression
 ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT
 ALTER VIEW [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
+ALTER VIEW [ IF EXISTS ] name RENAME [ COLUMN ] column_name TO new_column_name
 ALTER VIEW [ IF EXISTS ] name RENAME TO new_name
 ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema
 ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )

changedREINDEX

--- 12
+++ 13
@@ -1 +1,4 @@
-REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name
+REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name
+where option can be one of:
+
+VERBOSE

changedCREATE INDEX

--- 12
+++ 13
@@ -1,5 +1,5 @@
 CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
-( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
+( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
 [ INCLUDE ( column_name [, ...] ) ]
 [ WITH ( storage_parameter [= value] [, ... ] ) ]
 [ TABLESPACE tablespace_name ]

changedALTER INDEX

--- 12
+++ 13
@@ -1,7 +1,7 @@
 ALTER INDEX [ IF EXISTS ] name RENAME TO new_name
 ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name
 ALTER INDEX name ATTACH PARTITION index_name
-ALTER INDEX name DEPENDS ON EXTENSION extension_name
+ALTER INDEX name [ NO ] DEPENDS ON EXTENSION extension_name
 ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter [= value] [, ... ] )
 ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] )
 ALTER INDEX [ IF EXISTS ] name ALTER [ COLUMN ] column_number

changedALTER STATISTICS

--- 12
+++ 13
@@ -1,3 +1,4 @@
 ALTER STATISTICS name OWNER TO { new_owner | 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

changedALTER FUNCTION

--- 12
+++ 13
@@ -7,7 +7,7 @@
 ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
 SET SCHEMA new_schema
 ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
-DEPENDS ON EXTENSION extension_name
+[ NO ] DEPENDS ON EXTENSION extension_name
 where action is one of:
 
 CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT

changedCREATE LANGUAGE

--- 12
+++ 13
@@ -1,3 +1,3 @@
-CREATE [ OR REPLACE ] [ PROCEDURAL ] LANGUAGE name
 CREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
 HANDLER call_handler [ INLINE inline_handler ] [ VALIDATOR valfunction ]
+CREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name

changedALTER PROCEDURE

--- 12
+++ 13
@@ -7,7 +7,7 @@
 ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
 SET SCHEMA new_schema
 ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
-DEPENDS ON EXTENSION extension_name
+[ NO ] DEPENDS ON EXTENSION extension_name
 where action is one of:
 
 [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER

changedALTER ROUTINE

--- 12
+++ 13
@@ -7,7 +7,7 @@
 ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
 SET SCHEMA new_schema
 ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
-DEPENDS ON EXTENSION extension_name
+[ NO ] DEPENDS ON EXTENSION extension_name
 where action is one of:
 
 IMMUTABLE | STABLE | VOLATILE

changedALTER TYPE

--- 12
+++ 13
@@ -1,10 +1,12 @@
-ALTER TYPE name action [, ... ]
 ALTER TYPE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
-ALTER TYPE name RENAME ATTRIBUTE attribute_name TO new_attribute_name [ CASCADE | RESTRICT ]
 ALTER TYPE name RENAME TO new_name
 ALTER TYPE name SET SCHEMA new_schema
+ALTER TYPE name RENAME ATTRIBUTE attribute_name TO new_attribute_name [ CASCADE | RESTRICT ]
+ALTER TYPE name action [, ... ]
 ALTER TYPE name ADD VALUE [ IF NOT EXISTS ] new_enum_value [ { BEFORE | AFTER } neighbor_enum_value ]
 ALTER TYPE name RENAME VALUE existing_enum_value TO new_enum_value
+ALTER TYPE name SET ( property = value [, ... ] )
+
 where action is one of:
 
 ADD ATTRIBUTE attribute_name data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]

changedCREATE DATABASE

--- 12
+++ 13
@@ -2,6 +2,7 @@
 [ WITH ] [ OWNER [=] user_name ]
 [ TEMPLATE [=] template ]
 [ ENCODING [=] encoding ]
+[ LOCALE [=] locale ]
 [ LC_COLLATE [=] lc_collate ]
 [ LC_CTYPE [=] lc_ctype ]
 [ TABLESPACE [=] tablespace_name ]

changedDROP DATABASE

--- 12
+++ 13
@@ -1 +1,5 @@
-DROP DATABASE [ IF EXISTS ] name
+DROP DATABASE [ IF EXISTS ] name [ [ WITH ] ( option [, ...] ) ]
+
+where option can be:
+
+FORCE

changedALTER TRIGGER

--- 12
+++ 13
@@ -1,2 +1,2 @@
 ALTER TRIGGER name ON table_name RENAME TO new_name
-ALTER TRIGGER name ON table_name DEPENDS ON EXTENSION extension_name
+ALTER TRIGGER name ON table_name [ NO ] DEPENDS ON EXTENSION extension_name

changedCREATE EXTENSION

--- 12
+++ 13
@@ -1,5 +1,4 @@
 CREATE EXTENSION [ IF NOT EXISTS ] extension_name
 [ WITH ] [ SCHEMA schema_name ]
 [ VERSION version ]
-[ FROM old_version ]
 [ CASCADE ]

changedEXPLAIN

--- 12
+++ 13
@@ -7,6 +7,7 @@
 COSTS [ boolean ]
 SETTINGS [ boolean ]
 BUFFERS [ boolean ]
+WAL [ boolean ]
 TIMING [ boolean ]
 SUMMARY [ boolean ]
 FORMAT { TEXT | XML | JSON | YAML }

changedSELECT

--- 12
+++ 13
@@ -10,7 +10,7 @@
 [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
 [ LIMIT { count | ALL } ]
 [ OFFSET start [ ROW | ROWS ] ]
-[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
+[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]
 [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
 
 where from_item can be one of:

changedVACUUM

--- 12
+++ 13
@@ -11,6 +11,6 @@
 SKIP_LOCKED [ boolean ]
 INDEX_CLEANUP [ boolean ]
 TRUNCATE [ boolean ]
-
+PARALLEL integer
 and table_and_columns is:
 table_name [ ( column_name [, ...] ) ]