SQL Commands / Version comparison
What changed?
SQL Commands · PostgreSQL 9.4 → 9.5
Historical documentation for a PostgreSQL version that is no longer supported.
6 added · 0 removed · 54 changed · 0 coverage changes. These are changes between the pinned reference snapshots.
changedCREATE MATERIALIZED VIEW
--- 9.4 +++ 9.5 @@ -1,4 +1,4 @@ -CREATE MATERIALIZED VIEW table_name +CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name [ (column_name [, ...] ) ] [ WITH ( storage_parameter [= value] [, ... ] ) ] [ TABLESPACE tablespace_name ]
changedALTER MATERIALIZED VIEW
--- 9.4
+++ 9.5
@@ -17,6 +17,6 @@
ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
CLUSTER ON index_name
SET WITHOUT CLUSTER
-SET ( storage_parameter = value [, ... ] )
+SET ( storage_parameter [= value] [, ... ] )
RESET ( storage_parameter [, ... ] )
-OWNER TO new_owner
+OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
changedCREATE SEQUENCE
--- 9.4
+++ 9.5
@@ -1,4 +1,4 @@
-CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
+CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
[ OWNED BY { table_name.column_name | NONE } ]
changedALTER SEQUENCE
--- 9.4
+++ 9.5
@@ -4,6 +4,6 @@
[ RESTART [ [ WITH ] restart ] ]
[ CACHE cache ] [ [ NO ] CYCLE ]
[ OWNED BY { table_name.column_name | NONE } ]
-ALTER SEQUENCE [ IF EXISTS ] name OWNER TO new_owner
+ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name
ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema
changedALTER TABLE
--- 9.4
+++ 9.5
@@ -36,19 +36,24 @@
ENABLE RULE rewrite_rule_name
ENABLE REPLICA RULE rewrite_rule_name
ENABLE ALWAYS RULE rewrite_rule_name
+DISABLE ROW LEVEL SECURITY
+ENABLE ROW LEVEL SECURITY
+FORCE ROW LEVEL SECURITY
+NO FORCE ROW LEVEL SECURITY
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITH OIDS
SET WITHOUT OIDS
-SET ( storage_parameter = value [, ... ] )
+SET TABLESPACE new_tablespace
+SET { LOGGED | UNLOGGED }
+SET ( storage_parameter [= value] [, ... ] )
RESET ( storage_parameter [, ... ] )
INHERIT parent_table
NO INHERIT parent_table
OF type_name
NOT OF
-OWNER TO new_owner
-SET TABLESPACE new_tablespace
-REPLICA IDENTITY {DEFAULT | USING INDEX index_name | FULL | NOTHING}
+OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
+REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }
and table_constraint_using_index is:
changedCREATE TABLE AS
--- 9.4
+++ 9.5
@@ -1,4 +1,4 @@
-CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE table_name
+CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
[ (column_name [, ...] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
changedALTER VIEW
--- 9.4
+++ 9.5
@@ -1,6 +1,6 @@
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
+ALTER VIEW [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
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
--- 9.4
+++ 9.5
@@ -1 +1 @@
-REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]
+REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name
changedCREATE INDEX
--- 9.4
+++ 9.5
@@ -1,5 +1,5 @@
-CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table_name [ USING method ]
+CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
-[ WITH ( storage_parameter = value [, ... ] ) ]
+[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]
changedALTER INDEX
--- 9.4 +++ 9.5 @@ -1,6 +1,6 @@ ALTER INDEX [ IF EXISTS ] name RENAME TO new_name ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name -ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter = value [, ... ] ) +ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter [= value] [, ... ] ) ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] ) ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ] SET TABLESPACE new_tablespace [ NOWAIT ]
changedALTER AGGREGATE
--- 9.4
+++ 9.5
@@ -1,5 +1,6 @@
ALTER AGGREGATE name ( aggregate_signature ) RENAME TO new_name
-ALTER AGGREGATE name ( aggregate_signature ) OWNER TO new_owner
+ALTER AGGREGATE name ( aggregate_signature )
+OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER AGGREGATE name ( aggregate_signature ) SET SCHEMA new_schema
where aggregate_signature is:
changedCREATE FUNCTION
--- 9.4
+++ 9.5
@@ -3,6 +3,7 @@
[ RETURNS rettype
| RETURNS TABLE ( column_name column_type [, ...] ) ]
{ LANGUAGE lang_name
+| TRANSFORM { FOR TYPE type_name } [, ... ]
| WINDOW
| IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
changedALTER FUNCTION
--- 9.4
+++ 9.5
@@ -3,7 +3,7 @@
ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
RENAME TO new_name
ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
-OWNER TO new_owner
+OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
SET SCHEMA new_schema
where action is one of:
changedALTER LANGUAGE
--- 9.4
+++ 9.5
@@ -1,2 +1,2 @@
ALTER [ PROCEDURAL ] LANGUAGE name RENAME TO new_name
-ALTER [ PROCEDURAL ] LANGUAGE name OWNER TO new_owner
+ALTER [ PROCEDURAL ] LANGUAGE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
addedCREATE TRANSFORM
--- 9.4 +++ 9.5 @@ -0,0 +1,4 @@ +CREATE [ OR REPLACE ] TRANSFORM FOR type_name LANGUAGE lang_name ( +FROM SQL WITH FUNCTION from_sql_function_name (argument_type [, ...]), +TO SQL WITH FUNCTION to_sql_function_name (argument_type [, ...]) +);
addedDROP TRANSFORM
--- 9.4 +++ 9.5 @@ -0,0 +1 @@ +DROP TRANSFORM [ IF EXISTS ] FOR type_name LANGUAGE lang_name
changedALTER COLLATION
--- 9.4
+++ 9.5
@@ -1,3 +1,3 @@
ALTER COLLATION name RENAME TO new_name
-ALTER COLLATION name OWNER TO new_owner
+ALTER COLLATION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER COLLATION name SET SCHEMA new_schema
changedALTER CONVERSION
--- 9.4
+++ 9.5
@@ -1,3 +1,3 @@
ALTER CONVERSION name RENAME TO new_name
-ALTER CONVERSION name OWNER TO new_owner
+ALTER CONVERSION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER CONVERSION name SET SCHEMA new_schema
changedALTER DOMAIN
--- 9.4
+++ 9.5
@@ -11,7 +11,7 @@
ALTER DOMAIN name
VALIDATE CONSTRAINT constraint_name
ALTER DOMAIN name
-OWNER TO new_owner
+OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER DOMAIN name
RENAME TO new_name
ALTER DOMAIN name
changedALTER OPERATOR
--- 9.4
+++ 9.5
@@ -1,2 +1,5 @@
-ALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } ) OWNER TO new_owner
-ALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } ) SET SCHEMA new_schema
+ALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } )
+OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
+
+ALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } )
+SET SCHEMA new_schema
changedALTER OPERATOR CLASS
--- 9.4
+++ 9.5
@@ -1,3 +1,8 @@
-ALTER OPERATOR CLASS name USING index_method RENAME TO new_name
-ALTER OPERATOR CLASS name USING index_method OWNER TO new_owner
-ALTER OPERATOR CLASS name USING index_method SET SCHEMA new_schema
+ALTER OPERATOR CLASS name USING index_method
+RENAME TO new_name
+
+ALTER OPERATOR CLASS name USING index_method
+OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
+
+ALTER OPERATOR CLASS name USING index_method
+SET SCHEMA new_schema
changedALTER OPERATOR FAMILY
--- 9.4
+++ 9.5
@@ -1,11 +1,20 @@
ALTER OPERATOR FAMILY name USING index_method ADD
-{ OPERATOR strategy_number operator_name ( op_type, op_type ) [ FOR SEARCH | FOR ORDER BY sort_family_name ]
-| FUNCTION support_number [ ( op_type [ , op_type ] ) ] function_name ( argument_type [, ...] )
+{ OPERATOR strategy_number operator_name ( op_type, op_type )
+[ FOR SEARCH | FOR ORDER BY sort_family_name ]
+| FUNCTION support_number [ ( op_type [ , op_type ] ) ]
+function_name ( argument_type [, ...] )
} [, ... ]
+
ALTER OPERATOR FAMILY name USING index_method DROP
{ OPERATOR strategy_number ( op_type [ , op_type ] )
| FUNCTION support_number ( op_type [ , op_type ] )
} [, ... ]
-ALTER OPERATOR FAMILY name USING index_method RENAME TO new_name
-ALTER OPERATOR FAMILY name USING index_method OWNER TO new_owner
-ALTER OPERATOR FAMILY name USING index_method SET SCHEMA new_schema
+
+ALTER OPERATOR FAMILY name USING index_method
+RENAME TO new_name
+
+ALTER OPERATOR FAMILY name USING index_method
+OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
+
+ALTER OPERATOR FAMILY name USING index_method
+SET SCHEMA new_schema
changedALTER TYPE
--- 9.4
+++ 9.5
@@ -1,5 +1,5 @@
ALTER TYPE name action [, ... ]
-ALTER TYPE name OWNER TO new_owner
+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
changedCREATE DATABASE
--- 9.4 +++ 9.5 @@ -5,4 +5,6 @@ [ LC_COLLATE [=] lc_collate ] [ LC_CTYPE [=] lc_ctype ] [ TABLESPACE [=] tablespace_name ] -[ CONNECTION LIMIT [=] connlimit ] ] +[ ALLOW_CONNECTIONS [=] allowconn ] +[ CONNECTION LIMIT [=] connlimit ] +[ IS_TEMPLATE [=] istemplate ] ]
changedALTER DATABASE
--- 9.4
+++ 9.5
@@ -2,11 +2,13 @@
where option can be:
+ALLOW_CONNECTIONS allowconn
CONNECTION LIMIT connlimit
+IS_TEMPLATE istemplate
ALTER DATABASE name RENAME TO new_name
-ALTER DATABASE name OWNER TO new_owner
+ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER DATABASE name SET TABLESPACE new_tablespace
changedCREATE SCHEMA
--- 9.4 +++ 9.5 @@ -1,4 +1,8 @@ -CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [ schema_element [ ... ] ] -CREATE SCHEMA AUTHORIZATION user_name [ schema_element [ ... ] ] -CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION user_name ] -CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user_name +CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ] +CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ] +CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ] +CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification +where role_specification can be: +user_name +| CURRENT_USER +| SESSION_USER
changedALTER SCHEMA
--- 9.4
+++ 9.5
@@ -1,2 +1,2 @@
ALTER SCHEMA name RENAME TO new_name
-ALTER SCHEMA name OWNER TO new_owner
+ALTER SCHEMA name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
changedCREATE TABLESPACE
--- 9.4
+++ 9.5
@@ -1,4 +1,4 @@
CREATE TABLESPACE tablespace_name
-[ OWNER user_name ]
+[ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
LOCATION 'directory'
[ WITH ( tablespace_option = value [, ... ] ) ]
changedALTER TABLESPACE
--- 9.4
+++ 9.5
@@ -1,4 +1,4 @@
ALTER TABLESPACE name RENAME TO new_name
-ALTER TABLESPACE name OWNER TO new_owner
+ALTER TABLESPACE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER TABLESPACE name SET ( tablespace_option = value [, ... ] )
ALTER TABLESPACE name RESET ( tablespace_option [, ... ] )
changedGRANT
--- 9.4
+++ 9.5
@@ -2,58 +2,67 @@
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
-TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
-TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
-TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
-TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
-TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
-TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
-TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]
| ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
-TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
-TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
-TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
-TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
-TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
-TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO role_specification [, ...] [ WITH GRANT OPTION ]
-GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]
+GRANT role_name [, ...] TO role_specification [, ...]
+[ WITH ADMIN OPTION ]
+[ GRANTED BY role_specification ]
+
+where role_specification can be:
+
+[ GROUP ] role_name
+| PUBLIC
+| CURRENT_USER
+| SESSION_USER
changedREVOKE
--- 9.4
+++ 9.5
@@ -3,14 +3,14 @@
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
-FROM { [ GROUP ] role_name | PUBLIC } [, ...]
+FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
-FROM { [ GROUP ] role_name | PUBLIC } [, ...]
+FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
@@ -18,70 +18,78 @@
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
-FROM { [ GROUP ] role_name | PUBLIC } [, ...]
+FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
-FROM { [ GROUP ] role_name | PUBLIC } [, ...]
+FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
-FROM { [ GROUP ] role_name | PUBLIC } [, ...]
+FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
-FROM { [ GROUP ] role_name | PUBLIC } [, ...]
+FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
-FROM { [ GROUP ] role_name | PUBLIC } [, ...]
+FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]
| ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
-FROM { [ GROUP ] role_name | PUBLIC } [, ...]
+FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
-FROM { [ GROUP ] role_name | PUBLIC } [, ...]
+FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
-FROM { [ GROUP ] role_name | PUBLIC } [, ...]
+FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
-FROM { [ GROUP ] role_name | PUBLIC } [, ...]
+FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
-FROM { [ GROUP ] role_name | PUBLIC } [, ...]
+FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
-FROM { [ GROUP ] role_name | PUBLIC } [, ...]
+FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ ADMIN OPTION FOR ]
-role_name [, ...] FROM role_name [, ...]
+role_name [, ...] FROM role_specification [, ...]
+[ GRANTED BY role_specification ]
[ CASCADE | RESTRICT ]
+
+where role_specification can be:
+
+[ GROUP ] role_name
+| PUBLIC
+| CURRENT_USER
+| SESSION_USER
changedCREATE GROUP
--- 9.4 +++ 9.5 @@ -8,6 +8,9 @@ | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN +| REPLICATION | NOREPLICATION +| BYPASSRLS | NOBYPASSRLS +| CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...]
changedALTER GROUP
--- 9.4 +++ 9.5 @@ -1,4 +1,9 @@ -ALTER GROUP group_name ADD USER user_name [, ... ] -ALTER GROUP group_name DROP USER user_name [, ... ] +ALTER GROUP role_specification ADD USER user_name [, ... ] +ALTER GROUP role_specification DROP USER user_name [, ... ] + +where role_specification can be: +role_name +| CURRENT_USER +| SESSION_USER ALTER GROUP group_name RENAME TO new_name
changedDROP OWNED
--- 9.4
+++ 9.5
@@ -1 +1 @@
-DROP OWNED BY name [, ...] [ CASCADE | RESTRICT ]
+DROP OWNED BY { name | CURRENT_USER | SESSION_USER } [, ...] [ CASCADE | RESTRICT ]
changedREASSIGN OWNED
--- 9.4
+++ 9.5
@@ -1 +1,2 @@
-REASSIGN OWNED BY old_role [, ...] TO new_role
+REASSIGN OWNED BY { old_role | CURRENT_USER | SESSION_USER } [, ...]
+TO { new_role | CURRENT_USER | SESSION_USER }
addedCREATE POLICY
--- 9.4
+++ 9.5
@@ -0,0 +1,5 @@
+CREATE POLICY name ON table_name
+[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
+[ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
+[ USING ( using_expression ) ]
+[ WITH CHECK ( check_expression ) ]
addedALTER POLICY
--- 9.4
+++ 9.5
@@ -0,0 +1,6 @@
+ALTER POLICY name ON table_name RENAME TO new_name
+
+ALTER POLICY name ON table_name
+[ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
+[ USING ( using_expression ) ]
+[ WITH CHECK ( check_expression ) ]
addedDROP POLICY
--- 9.4 +++ 9.5 @@ -0,0 +1 @@ +DROP POLICY [ IF EXISTS ] name ON table_name
changedCREATE ROLE
--- 9.4 +++ 9.5 @@ -9,6 +9,7 @@ | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION +| BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp'
changedALTER ROLE
--- 9.4
+++ 9.5
@@ -1,4 +1,4 @@
-ALTER ROLE name [ [ WITH ] option [ ... ] ]
+ALTER ROLE role_specification [ WITH ] option [ ... ]
where option can be:
@@ -9,13 +9,19 @@
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
+| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'
ALTER ROLE name RENAME TO new_name
-ALTER ROLE { name | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
-ALTER ROLE { name | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
-ALTER ROLE { name | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
-ALTER ROLE { name | ALL } [ IN DATABASE database_name ] RESET ALL
+ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
+ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
+ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
+ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL
+
+where role_specification can be:
+role_name
+| CURRENT_USER
+| SESSION_USER
changedCREATE USER
--- 9.4 +++ 9.5 @@ -9,6 +9,7 @@ | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION +| BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp'
changedALTER USER
--- 9.4
+++ 9.5
@@ -1,4 +1,4 @@
-ALTER USER name [ [ WITH ] option [ ... ] ]
+ALTER USER role_specification [ WITH ] option [ ... ]
where option can be:
@@ -9,13 +9,19 @@
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
+| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'
ALTER USER name RENAME TO new_name
-ALTER USER { name | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
-ALTER USER { name | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
-ALTER USER { name | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
-ALTER USER { name | ALL } [ IN DATABASE database_name ] RESET ALL
+ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
+ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
+ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
+ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL
+
+where role_specification can be:
+role_name
+| CURRENT_USER
+| SESSION_USER
changedALTER EVENT TRIGGER
--- 9.4
+++ 9.5
@@ -1,4 +1,4 @@
ALTER EVENT TRIGGER name DISABLE
ALTER EVENT TRIGGER name ENABLE [ REPLICA | ALWAYS ]
-ALTER EVENT TRIGGER name OWNER TO new_owner
+ALTER EVENT TRIGGER name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER EVENT TRIGGER name RENAME TO new_name
changedCREATE TRIGGER
--- 9.4
+++ 9.5
@@ -1,7 +1,7 @@
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
ON table_name
[ FROM referenced_table_name ]
-[ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } ]
+[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE PROCEDURE function_name ( arguments )
changedALTER EXTENSION
--- 9.4 +++ 9.5 @@ -26,6 +26,7 @@ TEXT SEARCH DICTIONARY object_name | TEXT SEARCH PARSER object_name | TEXT SEARCH TEMPLATE object_name | +TRANSFORM FOR type_name LANGUAGE lang_name | TYPE object_name | VIEW object_name and aggregate_signature is:
changedALTER TEXT SEARCH CONFIGURATION
--- 9.4
+++ 9.5
@@ -9,5 +9,5 @@
ALTER TEXT SEARCH CONFIGURATION name
DROP MAPPING [ IF EXISTS ] FOR token_type [, ... ]
ALTER TEXT SEARCH CONFIGURATION name RENAME TO new_name
-ALTER TEXT SEARCH CONFIGURATION name OWNER TO new_owner
+ALTER TEXT SEARCH CONFIGURATION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER TEXT SEARCH CONFIGURATION name SET SCHEMA new_schema
changedALTER TEXT SEARCH DICTIONARY
--- 9.4
+++ 9.5
@@ -2,5 +2,5 @@
option [ = value ] [, ... ]
)
ALTER TEXT SEARCH DICTIONARY name RENAME TO new_name
-ALTER TEXT SEARCH DICTIONARY name OWNER TO new_owner
+ALTER TEXT SEARCH DICTIONARY name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER TEXT SEARCH DICTIONARY name SET SCHEMA new_schema
changedALTER FOREIGN DATA WRAPPER
--- 9.4
+++ 9.5
@@ -2,5 +2,5 @@
[ HANDLER handler_function | NO HANDLER ]
[ VALIDATOR validator_function | NO VALIDATOR ]
[ OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ]) ]
-ALTER FOREIGN DATA WRAPPER name OWNER TO new_owner
+ALTER FOREIGN DATA WRAPPER name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER FOREIGN DATA WRAPPER name RENAME TO new_name
addedIMPORT FOREIGN SCHEMA
--- 9.4
+++ 9.5
@@ -0,0 +1,5 @@
+IMPORT FOREIGN SCHEMA remote_schema
+[ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
+FROM SERVER server_name
+INTO local_schema
+[ OPTIONS ( option 'value' [, ... ] ) ]
changedCREATE FOREIGN TABLE
--- 9.4
+++ 9.5
@@ -1,7 +1,9 @@
CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
-column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
+{ column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
+| table_constraint }
[, ... ]
] )
+[ INHERITS ( parent_table [, ... ] ) ]
SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]
@@ -10,4 +12,10 @@
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
+CHECK ( expression ) [ NO INHERIT ] |
DEFAULT default_expr }
+
+and table_constraint is:
+
+[ CONSTRAINT constraint_name ]
+CHECK ( expression ) [ NO INHERIT ]
changedALTER FOREIGN TABLE
--- 9.4
+++ 9.5
@@ -1,6 +1,6 @@
-ALTER FOREIGN TABLE [ IF EXISTS ] name
+ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
action [, ... ]
-ALTER FOREIGN TABLE [ IF EXISTS ] name
+ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
RENAME [ COLUMN ] column_name TO new_column_name
ALTER FOREIGN TABLE [ IF EXISTS ] name
RENAME TO new_name
@@ -10,17 +10,25 @@
ADD [ COLUMN ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
-ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type
+ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ]
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 STATISTICS integer
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 }
ALTER [ COLUMN ] column_name OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ])
+ADD table_constraint [ NOT VALID ]
+VALIDATE CONSTRAINT constraint_name
+DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ trigger_name | ALL | USER ]
ENABLE TRIGGER [ trigger_name | ALL | USER ]
ENABLE REPLICA TRIGGER trigger_name
ENABLE ALWAYS TRIGGER trigger_name
-OWNER TO new_owner
+SET WITH OIDS
+SET WITHOUT OIDS
+INHERIT parent_table
+NO INHERIT parent_table
+OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ])
changedALTER SERVER
--- 9.4
+++ 9.5
@@ -1,4 +1,4 @@
ALTER SERVER name [ VERSION 'new_version' ]
[ OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] ) ]
-ALTER SERVER name OWNER TO new_owner
+ALTER SERVER name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER SERVER name RENAME TO new_name
changedALTER USER MAPPING
--- 9.4
+++ 9.5
@@ -1,3 +1,3 @@
-ALTER USER MAPPING FOR { user_name | USER | CURRENT_USER | PUBLIC }
+ALTER USER MAPPING FOR { user_name | USER | CURRENT_USER | SESSION_USER | PUBLIC }
SERVER server_name
OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] )
changedDELETE
--- 9.4 +++ 9.5 @@ -1,5 +1,5 @@ [ WITH [ RECURSIVE ] with_query [, ...] ] DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ] -[ USING using_list ] +[ USING from_item [, ...] ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
changedINSERT
--- 9.4
+++ 9.5
@@ -1,4 +1,18 @@
[ WITH [ RECURSIVE ] with_query [, ...] ]
-INSERT INTO table_name [ ( column_name [, ...] ) ]
+INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
+[ ON CONFLICT [ conflict_target ] conflict_action ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
+
+where conflict_target can be one of:
+
+( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
+ON CONSTRAINT constraint_name
+and conflict_action is one of:
+
+DO NOTHING
+DO UPDATE SET { column_name = { expression | DEFAULT } |
+( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
+( column_name [, ...] ) = ( sub-SELECT )
+} [, ...]
+[ WHERE condition ]
changedSELECT
--- 9.4
+++ 9.5
@@ -3,19 +3,20 @@
[ * | expression [ [ AS ] output_name ] [, ...] ]
[ FROM from_item [, ...] ]
[ WHERE condition ]
-[ GROUP BY expression [, ...] ]
-[ HAVING condition [, ...] ]
+[ GROUP BY grouping_element [, ...] ]
+[ HAVING condition ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
-[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]
+[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
where from_item can be one of:
[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
+[ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
[ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
[ LATERAL ] function_name ( [ argument [, ...] ] )
@@ -26,6 +27,15 @@
[ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]
+and grouping_element can be one of:
+
+( )
+expression
+( expression [, ...] )
+ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
+CUBE ( { expression | ( expression [, ...] ) } [, ...] )
+GROUPING SETS ( grouping_element [, ...] )
+
and with_query is:
with_query_name [ ( column_name [, ...] ) ] AS ( select | values | insert | update | delete )
changedUPDATE
--- 9.4
+++ 9.5
@@ -1,7 +1,9 @@
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
SET { column_name = { expression | DEFAULT } |
-( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
-[ FROM from_list ]
+( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
+( column_name [, ...] ) = ( sub-SELECT )
+} [, ...]
+[ FROM from_item [, ...] ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
changedSELECT INTO
--- 9.4
+++ 9.5
@@ -5,7 +5,7 @@
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
-[ HAVING condition [, ...] ]
+[ HAVING condition ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
changedCOMMENT
--- 9.4 +++ 9.5 @@ -5,6 +5,7 @@ COLLATION object_name | COLUMN relation_name.column_name | CONSTRAINT constraint_name ON table_name | +CONSTRAINT constraint_name ON DOMAIN domain_name | CONVERSION object_name | DATABASE object_name | DOMAIN object_name | @@ -19,6 +20,7 @@ OPERATOR operator_name (left_type, right_type) | OPERATOR CLASS object_name USING index_method | OPERATOR FAMILY object_name USING index_method | +POLICY policy_name ON table_name | [ PROCEDURAL ] LANGUAGE object_name | ROLE object_name | RULE rule_name ON table_name | @@ -31,6 +33,7 @@ TEXT SEARCH DICTIONARY object_name | TEXT SEARCH PARSER object_name | TEXT SEARCH TEMPLATE object_name | +TRANSFORM FOR type_name LANGUAGE lang_name | TRIGGER trigger_name ON table_name | TYPE object_name | VIEW object_name
changedALTER LARGE OBJECT
--- 9.4
+++ 9.5
@@ -1 +1 @@
-ALTER LARGE OBJECT large_object_oid OWNER TO new_owner
+ALTER LARGE OBJECT large_object_oid OWNER TO { new_owner | CURRENT_USER | SESSION_USER }