SQL Commands / Version comparison
What changed?
SQL Commands · PostgreSQL 9.2 → 9.3
Historical documentation for a PostgreSQL version that is no longer supported.
8 added · 0 removed · 12 changed · 0 coverage changes. These are changes between the pinned reference snapshots.
addedCREATE MATERIALIZED VIEW
--- 9.2 +++ 9.3 @@ -0,0 +1,6 @@ +CREATE MATERIALIZED VIEW table_name +[ (column_name [, ...] ) ] +[ WITH ( storage_parameter [= value] [, ... ] ) ] +[ TABLESPACE tablespace_name ] +AS query +[ WITH [ NO ] DATA ]
addedALTER MATERIALIZED VIEW
--- 9.2
+++ 9.3
@@ -0,0 +1,20 @@
+ALTER MATERIALIZED VIEW [ IF EXISTS ] name
+action [, ... ]
+ALTER MATERIALIZED VIEW [ IF EXISTS ] name
+RENAME [ COLUMN ] column_name TO new_column_name
+ALTER MATERIALIZED VIEW [ IF EXISTS ] name
+RENAME TO new_name
+ALTER MATERIALIZED VIEW [ IF EXISTS ] name
+SET SCHEMA new_schema
+where action is one of:
+
+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 }
+CLUSTER ON index_name
+SET WITHOUT CLUSTER
+SET ( storage_parameter = value [, ... ] )
+RESET ( storage_parameter [, ... ] )
+OWNER TO new_owner
+SET TABLESPACE new_tablespace
addedDROP MATERIALIZED VIEW
--- 9.2 +++ 9.3 @@ -0,0 +1 @@ +DROP MATERIALIZED VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
addedREFRESH MATERIALIZED VIEW
--- 9.2 +++ 9.3 @@ -0,0 +1,2 @@ +REFRESH MATERIALIZED VIEW name +[ WITH [ NO ] DATA ]
changedCREATE VIEW
--- 9.2 +++ 9.3 @@ -1,3 +1,3 @@ -CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] ) ] +CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ] [ WITH ( view_option_name [= view_option_value] [, ... ] ) ] AS query
changedALTER TYPE
--- 9.2
+++ 9.3
@@ -3,7 +3,7 @@
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 ADD VALUE new_enum_value [ { BEFORE | AFTER } existing_enum_value ]
+ALTER TYPE name ADD VALUE [ IF NOT EXISTS ] new_enum_value [ { BEFORE | AFTER } existing_enum_value ]
where action is one of:
changedCREATE SCHEMA
--- 9.2 +++ 9.3 @@ -1,2 +1,4 @@ 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
changedSECURITY LABEL
--- 9.2 +++ 9.3 @@ -5,9 +5,11 @@ AGGREGATE agg_name (agg_type [, ...] ) | DATABASE object_name | DOMAIN object_name | +EVENT TRIGGER object_name | FOREIGN TABLE object_name FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) | LARGE OBJECT large_object_oid | +MATERIALIZED VIEW object_name | [ PROCEDURAL ] LANGUAGE object_name | ROLE object_name | SCHEMA object_name |
changedALTER ROLE
--- 9.2
+++ 9.3
@@ -15,7 +15,7 @@
ALTER ROLE name RENAME TO new_name
-ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
-ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
-ALTER ROLE name [ IN DATABASE database_name ] RESET configuration_parameter
-ALTER ROLE name [ IN DATABASE database_name ] RESET ALL
+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
changedALTER USER
--- 9.2
+++ 9.3
@@ -15,7 +15,7 @@
ALTER USER name RENAME TO new_name
-ALTER USER name [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
-ALTER USER name [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
-ALTER USER name [ IN DATABASE database_name ] RESET configuration_parameter
-ALTER USER name [ IN DATABASE database_name ] RESET ALL
+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
addedCREATE EVENT TRIGGER
--- 9.2 +++ 9.3 @@ -0,0 +1,4 @@ +CREATE EVENT TRIGGER name +ON event +[ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ] +EXECUTE PROCEDURE function_name()
addedALTER EVENT TRIGGER
--- 9.2 +++ 9.3 @@ -0,0 +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 RENAME TO new_name
addedDROP EVENT TRIGGER
--- 9.2 +++ 9.3 @@ -0,0 +1 @@ +DROP EVENT TRIGGER [ IF EXISTS ] name [ CASCADE | RESTRICT ]
addedALTER RULE
--- 9.2 +++ 9.3 @@ -0,0 +1 @@ +ALTER RULE name ON table_name RENAME TO new_name
changedALTER EXTENSION
--- 9.2 +++ 9.3 @@ -9,9 +9,11 @@ COLLATION object_name | CONVERSION object_name | DOMAIN object_name | +EVENT TRIGGER object_name | FOREIGN DATA WRAPPER object_name | FOREIGN TABLE object_name | FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) | +MATERIALIZED VIEW object_name | OPERATOR operator_name (left_type, right_type) | OPERATOR CLASS object_name USING index_method | OPERATOR FAMILY object_name USING index_method |
changedCREATE FOREIGN TABLE
--- 9.2
+++ 9.3
@@ -1,6 +1,13 @@
CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
-{ column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ NULL | NOT NULL ] }
+column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
[, ... ]
] )
SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]
+
+where column_constraint is:
+
+[ CONSTRAINT constraint_name ]
+{ NOT NULL |
+NULL |
+DEFAULT default_expr }
changedALTER FOREIGN TABLE
--- 9.2
+++ 9.3
@@ -8,9 +8,11 @@
SET SCHEMA new_schema
where action is one of:
-ADD [ COLUMN ] column_name data_type [ NULL | NOT NULL ]
+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 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 [, ... ] )
changedCOPY
--- 9.2
+++ 9.3
@@ -1,15 +1,16 @@
COPY table_name [ ( column_name [, ...] ) ]
-FROM { 'filename' | STDIN }
+FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
-TO { 'filename' | STDOUT }
+TO { 'filename' | PROGRAM 'command' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]
where option can be one of:
FORMAT format_name
OIDS [ boolean ]
+FREEZE [ boolean ]
DELIMITER 'delimiter_character'
NULL 'null_string'
HEADER [ boolean ]
changedSELECT
--- 9.2
+++ 9.3
@@ -11,15 +11,15 @@
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
-[ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]
+[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]
where from_item can be one of:
[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
-( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
+[ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
-function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
-function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
+[ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
+[ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]
and with_query is:
changedCOMMENT
--- 9.2 +++ 9.3 @@ -9,11 +9,13 @@ DATABASE object_name | DOMAIN object_name | EXTENSION object_name | +EVENT TRIGGER 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 | +MATERIALIZED VIEW object_name | OPERATOR operator_name (left_type, right_type) | OPERATOR CLASS object_name USING index_method | OPERATOR FAMILY object_name USING index_method |