SQL Commands / Version comparison
What changed?
SQL Commands · PostgreSQL 8.1 → 8.2
Historical documentation for a PostgreSQL version that is no longer supported.
3 added · 0 removed · 52 changed · 0 coverage changes. These are changes between the pinned reference snapshots.
changedTRUNCATE
--- 8.1 +++ 8.2 @@ -1 +1 @@ -TRUNCATE [ TABLE ] name [, ...] +TRUNCATE [ TABLE ] name [, ...] [ CASCADE | RESTRICT ]
changedCREATE SEQUENCE
--- 8.1
+++ 8.2
@@ -1,3 +1,4 @@
CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
+[ OWNED BY { table.column | NONE } ]
changedALTER SEQUENCE
--- 8.1
+++ 8.2
@@ -1,4 +1,5 @@
ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
+[ OWNED BY { table.column | NONE } ]
ALTER SEQUENCE name SET SCHEMA new_schema
changedDROP SEQUENCE
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DROP SEQUENCE name [, ...] [ CASCADE | RESTRICT ] +DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
changedCREATE TABLE
--- 8.1
+++ 8.2
@@ -1,11 +1,11 @@
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [
{ column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
| table_constraint
-| LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] }
+| LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS } ] ... }
[, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
-[ WITH OIDS | WITHOUT OIDS ]
+[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]
@@ -14,9 +14,9 @@
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
-UNIQUE [ USING INDEX TABLESPACE tablespace ] |
-PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |
-CHECK (expression) |
+UNIQUE index_parameters |
+PRIMARY KEY index_parameters |
+CHECK ( expression ) |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
@@ -24,9 +24,14 @@
and table_constraint is:
[ CONSTRAINT constraint_name ]
-{ UNIQUE ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
-PRIMARY KEY ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
+{ UNIQUE ( column_name [, ... ] ) index_parameters |
+PRIMARY KEY ( column_name [, ... ] ) index_parameters |
CHECK ( expression ) |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+
+index_parameters in UNIQUE and PRIMARY KEY constraints are:
+
+[ WITH ( storage_parameter [= value] [, ... ] ) ]
+[ USING INDEX TABLESPACE tablespace ]
changedALTER TABLE
--- 8.1 +++ 8.2 @@ -24,5 +24,9 @@ CLUSTER ON index_name SET WITHOUT CLUSTER SET WITHOUT OIDS +SET ( storage_parameter = value [, ... ] ) +RESET ( storage_parameter [, ... ] ) +INHERIT parent_table +NO INHERIT parent_table OWNER TO new_owner SET TABLESPACE new_tablespace
changedDROP TABLE
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DROP TABLE name [, ...] [ CASCADE | RESTRICT ] +DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
changedCREATE TABLE AS
--- 8.1
+++ 8.2
@@ -1,3 +1,6 @@
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
-[ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
+[ (column_name [, ...] ) ]
+[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
+[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
+[ TABLESPACE tablespace ]
AS query
changedDROP VIEW
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DROP VIEW name [, ...] [ CASCADE | RESTRICT ] +DROP VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
changedCREATE INDEX
--- 8.1
+++ 8.2
@@ -1,4 +1,5 @@
-CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
+CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
+[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace ]
[ WHERE predicate ]
changedALTER INDEX
--- 8.1 +++ 8.2 @@ -1,2 +1,4 @@ ALTER INDEX name RENAME TO new_name ALTER INDEX name SET TABLESPACE tablespace_name +ALTER INDEX name SET ( storage_parameter = value [, ... ] ) +ALTER INDEX name RESET ( storage_parameter [, ... ] )
changedDROP INDEX
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DROP INDEX name [, ...] [ CASCADE | RESTRICT ] +DROP INDEX [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
changedCREATE AGGREGATE
--- 8.1 +++ 8.2 @@ -1,8 +1,18 @@ -CREATE AGGREGATE name ( -BASETYPE = input_data_type, +CREATE AGGREGATE name ( input_data_type [ , ... ] ) ( SFUNC = sfunc, STYPE = state_data_type [ , FINALFUNC = ffunc ] [ , INITCOND = initial_condition ] [ , SORTOP = sort_operator ] ) + +or the old syntax + +CREATE AGGREGATE name ( +BASETYPE = base_type, +SFUNC = sfunc, +STYPE = state_data_type +[ , FINALFUNC = ffunc ] +[ , INITCOND = initial_condition ] +[ , SORTOP = sort_operator ] +)
changedALTER AGGREGATE
--- 8.1 +++ 8.2 @@ -1,3 +1,3 @@ -ALTER AGGREGATE name ( type ) RENAME TO new_name -ALTER AGGREGATE name ( type ) OWNER TO new_owner -ALTER AGGREGATE name ( type ) SET SCHEMA new_schema +ALTER AGGREGATE name ( type [ , ... ] ) RENAME TO new_name +ALTER AGGREGATE name ( type [ , ... ] ) OWNER TO new_owner +ALTER AGGREGATE name ( type [ , ... ] ) SET SCHEMA new_schema
changedDROP AGGREGATE
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DROP AGGREGATE name ( type ) [ CASCADE | RESTRICT ] +DROP AGGREGATE [ IF EXISTS ] name ( type [ , ... ] ) [ CASCADE | RESTRICT ]
changedDROP FUNCTION
--- 8.1 +++ 8.2 @@ -1,2 +1,2 @@ -DROP FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) +DROP FUNCTION [ IF EXISTS ] name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [ CASCADE | RESTRICT ]
changedDROP LANGUAGE
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DROP [ PROCEDURAL ] LANGUAGE name [ CASCADE | RESTRICT ] +DROP [ PROCEDURAL ] LANGUAGE [ IF EXISTS ] name [ CASCADE | RESTRICT ]
changedDROP CAST
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DROP CAST (sourcetype AS targettype) [ CASCADE | RESTRICT ] +DROP CAST [ IF EXISTS ] (sourcetype AS targettype) [ CASCADE | RESTRICT ]
changedCREATE CONVERSION
--- 8.1 +++ 8.2 @@ -1,2 +1,2 @@ -CREATE [DEFAULT] CONVERSION name +CREATE [ DEFAULT ] CONVERSION name FOR source_encoding TO dest_encoding FROM funcname
changedDROP CONVERSION
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DROP CONVERSION name [ CASCADE | RESTRICT ] +DROP CONVERSION [ IF EXISTS ] name [ CASCADE | RESTRICT ]
changedCREATE DOMAIN
--- 8.1 +++ 8.2 @@ -1,4 +1,4 @@ -CREATE DOMAIN name [AS] data_type +CREATE DOMAIN name [ AS ] data_type [ DEFAULT expression ] [ constraint [ ... ] ]
changedDROP DOMAIN
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DROP DOMAIN name [, ...] [ CASCADE | RESTRICT ] +DROP DOMAIN [IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
changedDROP OPERATOR
--- 8.1
+++ 8.2
@@ -1 +1 @@
-DROP OPERATOR name ( { lefttype | NONE } , { righttype | NONE } ) [ CASCADE | RESTRICT ]
+DROP OPERATOR [ IF EXISTS ] name ( { lefttype | NONE } , { righttype | NONE } ) [ CASCADE | RESTRICT ]
changedDROP OPERATOR CLASS
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DROP OPERATOR CLASS name USING index_method [ CASCADE | RESTRICT ] +DROP OPERATOR CLASS [ IF EXISTS ] name USING index_method [ CASCADE | RESTRICT ]
changedCREATE TYPE
--- 8.1 +++ 8.2 @@ -15,3 +15,5 @@ [ , ELEMENT = element ] [ , DELIMITER = delimiter ] ) + +CREATE TYPE name
changedDROP TYPE
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DROP TYPE name [, ...] [ CASCADE | RESTRICT ] +DROP TYPE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
changedDROP DATABASE
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DROP DATABASE name +DROP DATABASE [ IF EXISTS ] name
changedDROP SCHEMA
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DROP SCHEMA name [, ...] [ CASCADE | RESTRICT ] +DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
changedDROP TABLESPACE
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DROP TABLESPACE tablespacename +DROP TABLESPACE [ IF EXISTS ] tablespacename
changedGRANT
--- 8.1
+++ 8.2
@@ -1,9 +1,14 @@
-GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
+GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] tablename [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
-GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
+GRANT { { USAGE | SELECT | UPDATE }
+[,...] | ALL [ PRIVILEGES ] }
+ON SEQUENCE sequencename [, ...]
+TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+
+GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE dbname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
changedREVOKE
--- 8.1
+++ 8.2
@@ -1,12 +1,19 @@
REVOKE [ GRANT OPTION FOR ]
-{ { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
+{ { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] tablename [, ...]
FROM { username | GROUP groupname | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
-{ { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
+{ { USAGE | SELECT | UPDATE }
+[,...] | ALL [ PRIVILEGES ] }
+ON SEQUENCE sequencename [, ...]
+FROM { username | GROUP groupname | PUBLIC } [, ...]
+[ CASCADE | RESTRICT ]
+
+REVOKE [ GRANT OPTION FOR ]
+{ { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE dbname [, ...]
FROM { username | GROUP groupname | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
changedDROP GROUP
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DROP GROUP name [, ...] +DROP GROUP [ IF EXISTS ] name [, ...]
addedDROP OWNED
--- 8.1 +++ 8.2 @@ -0,0 +1 @@ +DROP OWNED BY name [, ...] [ CASCADE | RESTRICT ]
addedREASSIGN OWNED
--- 8.1 +++ 8.2 @@ -0,0 +1 @@ +REASSIGN OWNED BY old_role [, ...] TO new_role
changedALTER ROLE
--- 8.1
+++ 8.2
@@ -14,5 +14,5 @@
ALTER ROLE name RENAME TO newname
-ALTER ROLE name SET parameter { TO | = } { value | DEFAULT }
-ALTER ROLE name RESET parameter
+ALTER ROLE name SET configuration_parameter { TO | = } { value | DEFAULT }
+ALTER ROLE name RESET configuration_parameter
changedDROP ROLE
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DROP ROLE name [, ...] +DROP ROLE [ IF EXISTS ] name [, ...]
changedALTER USER
--- 8.1
+++ 8.2
@@ -14,5 +14,5 @@
ALTER USER name RENAME TO newname
-ALTER USER name SET parameter { TO | = } { value | DEFAULT }
-ALTER USER name RESET parameter
+ALTER USER name SET configuration_parameter { TO | = } { value | DEFAULT }
+ALTER USER name RESET configuration_parameter
changedDROP USER
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DROP USER name [, ...] +DROP USER [ IF EXISTS ] name [, ...]
changedCREATE CONSTRAINT TRIGGER
--- 8.1
+++ 8.2
@@ -1,4 +1,7 @@
CREATE CONSTRAINT TRIGGER name
-AFTER events ON
-tablename constraint attributes
-FOR EACH ROW EXECUTE PROCEDURE funcname ( args )
+AFTER event [ OR ... ]
+ON table_name
+[ FROM referenced_table_name ]
+{ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
+FOR EACH ROW
+EXECUTE PROCEDURE funcname ( arguments )
changedDROP RULE
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DROP RULE name ON relation [ CASCADE | RESTRICT ] +DROP RULE [ IF EXISTS ] name ON relation [ CASCADE | RESTRICT ]
changedDROP TRIGGER
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DROP TRIGGER name ON table [ CASCADE | RESTRICT ] +DROP TRIGGER [ IF EXISTS ] name ON table [ CASCADE | RESTRICT ]
changedCOPY
--- 8.1
+++ 8.2
@@ -10,7 +10,7 @@
[ ESCAPE [ AS ] 'escape' ]
[ FORCE NOT NULL column [, ...] ]
-COPY tablename [ ( column [, ...] ) ]
+COPY { tablename [ ( column [, ...] ) ] | ( query ) }
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
changedDELETE
--- 8.1 +++ 8.2 @@ -1,3 +1,4 @@ -DELETE FROM [ ONLY ] table +DELETE FROM [ ONLY ] table [ [ AS ] alias ] [ USING usinglist ] [ WHERE condition ] +[ RETURNING * | output_expression [ AS output_name ] [, ...] ]
changedINSERT
--- 8.1
+++ 8.2
@@ -1,2 +1,3 @@
INSERT INTO table [ ( column [, ...] ) ]
-{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }
+{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
+[ RETURNING * | output_expression [ AS output_name ] [, ...] ]
changedSELECT
--- 8.1
+++ 8.2
@@ -8,7 +8,7 @@
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
-[ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] ]
+[ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]
where from_item can be one of:
changedUPDATE
--- 8.1
+++ 8.2
@@ -1,3 +1,6 @@
-UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
+UPDATE [ ONLY ] table [ [ AS ] alias ]
+SET { column = { expression | DEFAULT } |
+( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
[ FROM fromlist ]
[ WHERE condition ]
+[ RETURNING * | output_expression [ AS output_name ] [, ...] ]
addedVALUES
--- 8.1
+++ 8.2
@@ -0,0 +1,4 @@
+VALUES ( expression [, ...] ) [, ...]
+[ ORDER BY sort_expression [ ASC | DESC | USING operator ] [, ...] ]
+[ LIMIT { count | ALL } ]
+[ OFFSET start ]
changedSELECT INTO
--- 8.1
+++ 8.2
@@ -9,4 +9,4 @@
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
-[ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] ]
+[ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]
changedDEALLOCATE
--- 8.1 +++ 8.2 @@ -1 +1 @@ -DEALLOCATE [ PREPARE ] plan_name +DEALLOCATE [ PREPARE ] name
changedEXECUTE
--- 8.1 +++ 8.2 @@ -1 +1 @@ -EXECUTE plan_name [ (parameter [, ...] ) ] +EXECUTE name [ (parameter [, ...] ) ]
changedPREPARE
--- 8.1 +++ 8.2 @@ -1 +1 @@ -PREPARE plan_name [ (datatype [, ...] ) ] AS statement +PREPARE name [ (datatype [, ...] ) ] AS statement
changedRESET
--- 8.1 +++ 8.2 @@ -1,2 +1,2 @@ -RESET name +RESET configuration_parameter RESET ALL
changedSET
--- 8.1
+++ 8.2
@@ -1,2 +1,2 @@
-SET [ SESSION | LOCAL ] name { TO | = } { value | 'value' | DEFAULT }
+SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }
changedVACUUM
--- 8.1 +++ 8.2 @@ -1,2 +1,2 @@ -VACUUM [ FULL | FREEZE ] [ VERBOSE ] [ table ] -VACUUM [ FULL | FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ] +VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ] +VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]
changedCOMMENT
--- 8.1
+++ 8.2
@@ -2,7 +2,7 @@
{
TABLE object_name |
COLUMN table_name.column_name |
-AGGREGATE agg_name (agg_type) |
+AGGREGATE agg_name (agg_type [, ...] ) |
CAST (sourcetype AS targettype) |
CONSTRAINT constraint_name ON table_name |
CONVERSION object_name |
@@ -14,9 +14,11 @@
OPERATOR op (leftoperand_type, rightoperand_type) |
OPERATOR CLASS object_name USING index_method |
[ PROCEDURAL ] LANGUAGE object_name |
+ROLE object_name |
RULE rule_name ON table_name |
SCHEMA object_name |
SEQUENCE object_name |
+TABLESPACE object_name |
TRIGGER trigger_name ON table_name |
TYPE object_name |
VIEW object_name