SQL Commands / Version comparison
What changed?
SQL Commands · PostgreSQL 9.0 → 9.1
Historical documentation for a PostgreSQL version that is no longer supported.
10 added · 1 removed · 37 changed · 0 coverage changes. These are changes between the pinned reference snapshots.
changedCREATE TABLE
--- 9.0
+++ 9.1
@@ -1,5 +1,5 @@
-CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [
-{ column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
+CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
+{ column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE parent_table [ like_option ... ] }
[, ... ]
@@ -9,9 +9,9 @@
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]
-CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
+CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
OF type_name [ (
-{ column_name WITH OPTIONS [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
+{ column_name WITH OPTIONS [ column_constraint [ ... ] ]
| table_constraint }
[, ... ]
) ]
@@ -25,6 +25,7 @@
{ NOT NULL |
NULL |
CHECK ( expression ) |
+DEFAULT default_expr |
UNIQUE index_parameters |
PRIMARY KEY index_parameters |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
changedALTER TABLE
--- 9.0
+++ 9.1
@@ -8,9 +8,9 @@
SET SCHEMA new_schema
where action is one of:
-ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
+ADD [ COLUMN ] column data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
DROP [ COLUMN ] [ IF EXISTS ] column [ RESTRICT | CASCADE ]
-ALTER [ COLUMN ] column [ SET DATA ] TYPE type [ USING expression ]
+ALTER [ COLUMN ] column [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
ALTER [ COLUMN ] column SET DEFAULT expression
ALTER [ COLUMN ] column DROP DEFAULT
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
@@ -18,7 +18,9 @@
ALTER [ COLUMN ] column SET ( attribute_option = value [, ... ] )
ALTER [ COLUMN ] column RESET ( attribute_option [, ... ] )
ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
-ADD table_constraint
+ADD table_constraint [ NOT VALID ]
+ADD table_constraint_using_index
+VALIDATE CONSTRAINT constraint_name
DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ trigger_name | ALL | USER ]
ENABLE TRIGGER [ trigger_name | ALL | USER ]
@@ -36,5 +38,12 @@
RESET ( storage_parameter [, ... ] )
INHERIT parent_table
NO INHERIT parent_table
+OF type_name
+NOT OF
OWNER TO new_owner
SET TABLESPACE new_tablespace
+and table_constraint_using_index is:
+
+[ CONSTRAINT constraint_name ]
+{ UNIQUE | PRIMARY KEY } USING INDEX index_name
+[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
changedCREATE TABLE AS
--- 9.0
+++ 9.1
@@ -1,4 +1,4 @@
-CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
+CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE table_name
[ (column_name [, ...] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
changedCREATE INDEX
--- 9.0
+++ 9.1
@@ -1,5 +1,5 @@
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table [ USING method ]
-( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
+( { column | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace ]
[ WHERE predicate ]
addedCREATE COLLATION
--- 9.0 +++ 9.1 @@ -0,0 +1,6 @@ +CREATE COLLATION name ( +[ LOCALE = locale, ] +[ LC_COLLATE = lc_collate, ] +[ LC_CTYPE = lc_ctype ] +) +CREATE COLLATION name FROM existing_collation
addedALTER COLLATION
--- 9.0 +++ 9.1 @@ -0,0 +1,3 @@ +ALTER COLLATION name RENAME TO new_name +ALTER COLLATION name OWNER TO new_owner +ALTER COLLATION name SET SCHEMA new_schema
addedDROP COLLATION
--- 9.0 +++ 9.1 @@ -0,0 +1 @@ +DROP COLLATION [ IF EXISTS ] name [ CASCADE | RESTRICT ]
changedALTER CONVERSION
--- 9.0 +++ 9.1 @@ -1,2 +1,3 @@ ALTER CONVERSION name RENAME TO new_name ALTER CONVERSION name OWNER TO new_owner +ALTER CONVERSION name SET SCHEMA new_schema
changedCREATE DOMAIN
--- 9.0 +++ 9.1 @@ -1,4 +1,5 @@ CREATE DOMAIN name [ AS ] data_type +[ COLLATE collation ] [ DEFAULT expression ] [ constraint [ ... ] ]
changedALTER OPERATOR
--- 9.0
+++ 9.1
@@ -1 +1,2 @@
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
changedCREATE OPERATOR CLASS
--- 9.0
+++ 9.1
@@ -1,6 +1,6 @@
CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type
USING index_method [ FAMILY family_name ] AS
-{ OPERATOR strategy_number operator_name [ ( op_type, op_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 [, ...] )
| STORAGE storage_type
} [, ... ]
changedALTER OPERATOR CLASS
--- 9.0 +++ 9.1 @@ -1,2 +1,3 @@ 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
changedALTER OPERATOR FAMILY
--- 9.0
+++ 9.1
@@ -1,5 +1,5 @@
ALTER OPERATOR FAMILY name USING index_method ADD
-{ OPERATOR strategy_number operator_name ( op_type, op_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
@@ -8,3 +8,4 @@
} [, ... ]
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
changedCREATE TYPE
--- 9.0 +++ 9.1 @@ -1,5 +1,5 @@ CREATE TYPE name AS -( attribute_name data_type [, ... ] ) +( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] ) CREATE TYPE name AS ENUM ( [ 'label' [, ... ] ] ) @@ -22,6 +22,7 @@ [ , DEFAULT = default ] [ , ELEMENT = element ] [ , DELIMITER = delimiter ] +[ , COLLATABLE = collatable ] ) CREATE TYPE name
changedALTER TYPE
--- 9.0
+++ 9.1
@@ -1,3 +1,12 @@
+ALTER TYPE name action [, ... ]
+ALTER TYPE name OWNER TO new_owner
+ALTER TYPE name RENAME ATTRIBUTE attribute_name TO new_attribute_name [ CASCADE | RESTRICT ]
ALTER TYPE name RENAME TO new_name
-ALTER TYPE name OWNER TO new_owner
ALTER TYPE name SET SCHEMA new_schema
+ALTER TYPE name ADD VALUE new_enum_value [ { BEFORE | AFTER } existing_enum_value ]
+
+where action is one of:
+
+ADD ATTRIBUTE attribute_name data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]
+DROP ATTRIBUTE [ IF EXISTS ] attribute_name [ CASCADE | RESTRICT ]
+ALTER ATTRIBUTE attribute_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]
changedGRANT
--- 9.0
+++ 9.1
@@ -1,21 +1,21 @@
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
-[,...] | ALL [ PRIVILEGES ] }
+[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
-[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
+[, ...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
-[,...] | ALL [ PRIVILEGES ] }
+[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
-GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
+GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
@@ -36,11 +36,11 @@
ON LANGUAGE lang_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
-GRANT { { SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] }
+GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
-GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
+GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
changedREVOKE
--- 9.0
+++ 9.1
@@ -1,6 +1,6 @@
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
-[,...] | ALL [ PRIVILEGES ] }
+[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
@@ -8,21 +8,21 @@
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
-[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
+[, ...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
ON [ TABLE ] table_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
-[,...] | ALL [ PRIVILEGES ] }
+[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
-{ { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
+{ { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
@@ -53,13 +53,13 @@
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
-{ { SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] }
+{ { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
-{ { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
+{ { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
changedALTER DEFAULT PRIVILEGES
--- 9.0
+++ 9.1
@@ -5,12 +5,12 @@
where abbreviated_grant_or_revoke is one of:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
-[,...] | ALL [ PRIVILEGES ] }
+[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
-[,...] | ALL [ PRIVILEGES ] }
+[, ...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
@@ -20,14 +20,14 @@
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
-[,...] | ALL [ PRIVILEGES ] }
+[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
-[,...] | ALL [ PRIVILEGES ] }
+[, ...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
addedSECURITY LABEL
--- 9.0
+++ 9.1
@@ -0,0 +1,15 @@
+SECURITY LABEL [ FOR provider ] ON
+{
+TABLE object_name |
+COLUMN table_name.column_name |
+AGGREGATE agg_name (agg_type [, ...] ) |
+DOMAIN object_name |
+FOREIGN TABLE object_name
+FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) |
+LARGE OBJECT large_object_oid |
+[ PROCEDURAL ] LANGUAGE object_name |
+SCHEMA object_name |
+SEQUENCE object_name |
+TYPE object_name |
+VIEW object_name
+} IS 'label'
changedCREATE ROLE
--- 9.0 +++ 9.1 @@ -8,6 +8,7 @@ | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN +| REPLICATION | NOREPLICATION | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp'
changedALTER ROLE
--- 9.0 +++ 9.1 @@ -8,6 +8,7 @@ | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN +| REPLICATION | NOREPLICATION | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp'
changedCREATE USER
--- 9.0 +++ 9.1 @@ -8,6 +8,7 @@ | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN +| REPLICATION | NOREPLICATION | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp'
changedALTER USER
--- 9.0 +++ 9.1 @@ -8,6 +8,7 @@ | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN +| REPLICATION | NOREPLICATION | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp'
removedCREATE CONSTRAINT TRIGGER
--- 9.0
+++ 9.1
@@ -1,8 +0,0 @@
-CREATE CONSTRAINT TRIGGER name
-AFTER event [ OR ... ]
-ON table_name
-[ FROM referenced_table_name ]
-{ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
-FOR EACH ROW
-[ WHEN ( condition ) ]
-EXECUTE PROCEDURE function_name ( arguments )
changedCREATE TRIGGER
--- 9.0
+++ 9.1
@@ -1,4 +1,14 @@
-CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
-ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
+CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
+ON table
+[ FROM referenced_table_name ]
+[ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } ]
+[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE PROCEDURE function_name ( arguments )
+
+where event can be one of:
+
+INSERT
+UPDATE [ OF column_name [, ... ] ]
+DELETE
+TRUNCATE
addedCREATE EXTENSION
--- 9.0 +++ 9.1 @@ -0,0 +1,4 @@ +CREATE EXTENSION [ IF NOT EXISTS ] extension_name +[ WITH ] [ SCHEMA schema_name ] +[ VERSION version ] +[ FROM old_version ]
addedALTER EXTENSION
--- 9.0 +++ 9.1 @@ -0,0 +1,28 @@ +ALTER EXTENSION extension_name UPDATE [ TO new_version ] +ALTER EXTENSION extension_name SET SCHEMA new_schema +ALTER EXTENSION extension_name ADD member_object +ALTER EXTENSION extension_name DROP member_object +where member_object is: + +AGGREGATE agg_name (agg_type [, ...] ) | +CAST (source_type AS target_type) | +COLLATION object_name | +CONVERSION object_name | +DOMAIN object_name | +FOREIGN DATA WRAPPER object_name | +FOREIGN TABLE object_name | +FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) | +OPERATOR operator_name (left_type, right_type) | +OPERATOR CLASS object_name USING index_method | +OPERATOR FAMILY object_name USING index_method | +[ PROCEDURAL ] LANGUAGE object_name | +SCHEMA object_name | +SEQUENCE object_name | +SERVER object_name | +TABLE object_name | +TEXT SEARCH CONFIGURATION object_name | +TEXT SEARCH DICTIONARY object_name | +TEXT SEARCH PARSER object_name | +TEXT SEARCH TEMPLATE object_name | +TYPE object_name | +VIEW object_name
addedDROP EXTENSION
--- 9.0 +++ 9.1 @@ -0,0 +1 @@ +DROP EXTENSION [ IF EXISTS ] extension_name [, ...] [ CASCADE | RESTRICT ]
changedALTER TEXT SEARCH CONFIGURATION
--- 9.0 +++ 9.1 @@ -10,3 +10,4 @@ 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 SET SCHEMA new_schema
changedALTER TEXT SEARCH DICTIONARY
--- 9.0 +++ 9.1 @@ -3,3 +3,4 @@ ) ALTER TEXT SEARCH DICTIONARY name RENAME TO new_name ALTER TEXT SEARCH DICTIONARY name OWNER TO new_owner +ALTER TEXT SEARCH DICTIONARY name SET SCHEMA new_schema
changedALTER TEXT SEARCH PARSER
--- 9.0 +++ 9.1 @@ -1 +1,2 @@ ALTER TEXT SEARCH PARSER name RENAME TO new_name +ALTER TEXT SEARCH PARSER name SET SCHEMA new_schema
changedALTER TEXT SEARCH TEMPLATE
--- 9.0 +++ 9.1 @@ -1 +1,2 @@ ALTER TEXT SEARCH TEMPLATE name RENAME TO new_name +ALTER TEXT SEARCH TEMPLATE name SET SCHEMA new_schema
changedCREATE FOREIGN DATA WRAPPER
--- 9.0 +++ 9.1 @@ -1,3 +1,4 @@ CREATE FOREIGN DATA WRAPPER name -[ VALIDATOR valfunction | NO VALIDATOR ] +[ HANDLER handler_function | NO HANDLER ] +[ VALIDATOR validator_function | NO VALIDATOR ] [ OPTIONS ( option 'value' [, ... ] ) ]
changedALTER FOREIGN DATA WRAPPER
--- 9.0 +++ 9.1 @@ -1,4 +1,5 @@ ALTER FOREIGN DATA WRAPPER name -[ VALIDATOR valfunction | NO VALIDATOR ] +[ 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
addedCREATE FOREIGN TABLE
--- 9.0
+++ 9.1
@@ -0,0 +1,6 @@
+CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
+{ column_name data_type [ NULL | NOT NULL ] }
+[, ... ]
+] )
+SERVER server_name
+[ OPTIONS ( option 'value' [, ... ] ) ]
addedALTER FOREIGN TABLE
--- 9.0
+++ 9.1
@@ -0,0 +1,16 @@
+ALTER FOREIGN TABLE name
+action [, ... ]
+ALTER FOREIGN TABLE name
+RENAME [ COLUMN ] column TO new_column
+ALTER FOREIGN TABLE name
+RENAME TO new_name
+ALTER FOREIGN TABLE name
+SET SCHEMA new_schema
+where action is one of:
+
+ADD [ COLUMN ] column type [ NULL | NOT NULL ]
+DROP [ COLUMN ] [ IF EXISTS ] column [ RESTRICT | CASCADE ]
+ALTER [ COLUMN ] column [ SET DATA ] TYPE type
+ALTER [ COLUMN ] column { SET | DROP } NOT NULL
+OWNER TO new_owner
+OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ])
addedDROP FOREIGN TABLE
--- 9.0 +++ 9.1 @@ -0,0 +1 @@ +DROP FOREIGN TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
changedCOPY
--- 9.0
+++ 9.1
@@ -17,3 +17,4 @@
ESCAPE 'escape_character'
FORCE_QUOTE { ( column [, ...] ) | * }
FORCE_NOT_NULL ( column [, ...] )
+ENCODING 'encoding_name'
changedDELETE
--- 9.0 +++ 9.1 @@ -1,3 +1,4 @@ +[ WITH [ RECURSIVE ] with_query [, ...] ] DELETE FROM [ ONLY ] table [ * ] [ [ AS ] alias ] [ USING using_list ] [ WHERE condition | WHERE CURRENT OF cursor_name ]
changedINSERT
--- 9.0
+++ 9.1
@@ -1,3 +1,4 @@
+[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
changedSELECT
--- 9.0
+++ 9.1
@@ -6,7 +6,7 @@
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]
-[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
+[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
@@ -23,6 +23,6 @@
from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]
and with_query is:
-with_query_name [ ( column_name [, ...] ) ] AS ( select )
+with_query_name [ ( column_name [, ...] ) ] AS ( select | insert | update | delete )
-TABLE { [ ONLY ] table_name [ * ] | with_query_name }
+TABLE [ ONLY ] table_name [ * ]
changedUPDATE
--- 9.0
+++ 9.1
@@ -1,3 +1,4 @@
+[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table [ * ] [ [ AS ] alias ]
SET { column = { expression | DEFAULT } |
( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
changedSELECT INTO
--- 9.0
+++ 9.1
@@ -1,13 +1,13 @@
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ [ AS ] output_name ] [, ...]
-INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table
+INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]
-[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
+[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
changedMOVE
--- 9.0 +++ 9.1 @@ -1 +1,17 @@ MOVE [ direction [ FROM | IN ] ] cursor_name +where direction can be empty or one of: + +NEXT +PRIOR +FIRST +LAST +ABSOLUTE count +RELATIVE count +count +ALL +FORWARD +FORWARD count +FORWARD ALL +BACKWARD +BACKWARD count +BACKWARD ALL
changedBEGIN
--- 9.0
+++ 9.1
@@ -4,3 +4,4 @@
ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
READ WRITE | READ ONLY
+[ NOT ] DEFERRABLE
changedSET TRANSACTION
--- 9.0
+++ 9.1
@@ -5,3 +5,4 @@
ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
READ WRITE | READ ONLY
+[ NOT ] DEFERRABLE
changedSTART TRANSACTION
--- 9.0
+++ 9.1
@@ -4,3 +4,4 @@
ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
READ WRITE | READ ONLY
+[ NOT ] DEFERRABLE
changedCOMMENT
--- 9.0
+++ 9.1
@@ -1,13 +1,16 @@
COMMENT ON
{
-TABLE object_name |
AGGREGATE agg_name (agg_type [, ...] ) |
CAST (source_type AS target_type) |
+COLLATION object_name |
COLUMN relation_name.column_name |
CONSTRAINT constraint_name ON table_name |
CONVERSION object_name |
DATABASE object_name |
DOMAIN object_name |
+EXTENSION object_name |
+FOREIGN DATA WRAPPER object_name |
+FOREIGN TABLE object_name |
FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) |
INDEX object_name |
LARGE OBJECT large_object_oid |
@@ -19,6 +22,8 @@
RULE rule_name ON table_name |
SCHEMA object_name |
SEQUENCE object_name |
+SERVER object_name |
+TABLE object_name |
TABLESPACE object_name |
TEXT SEARCH CONFIGURATION object_name |
TEXT SEARCH DICTIONARY object_name |