SQL Commands / Version comparison
What changed?
SQL Commands · PostgreSQL 7.1 → 7.2
Historical documentation for a PostgreSQL version that is no longer supported.
2 added · 0 removed · 27 changed · 0 coverage changes. These are changes between the pinned reference snapshots.
changedCREATE SEQUENCE
--- 7.1 +++ 7.2 @@ -1,3 +1,3 @@ -CREATE SEQUENCE seqname [ INCREMENT increment ] +CREATE [ TEMPORARY | TEMP ] SEQUENCE seqname [ INCREMENT increment ] [ MINVALUE minvalue ] [ MAXVALUE maxvalue ] [ START start ] [ CACHE cache ] [ CYCLE ]
changedCREATE TABLE
--- 7.1
+++ 7.2
@@ -1,22 +1,25 @@
-CREATE [ TEMPORARY | TEMP ] TABLE table_name (
-{ column_name type [ column_constraint [ ... ] ]
+CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
+{ column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
| table_constraint } [, ... ]
-) [ INHERITS ( parent_table [, ... ] ) ]
+)
+[ INHERITS ( parent_table [, ... ] ) ]
+[ WITH OIDS | WITHOUT OIDS ]
-where column_constraint can be:
+where column_constraint is:
+
[ CONSTRAINT constraint_name ]
-{ NOT NULL | NULL | UNIQUE | PRIMARY KEY | DEFAULT value | CHECK (condition) |
-REFERENCES table [ ( column ) ] [ MATCH FULL | MATCH PARTIAL ]
-[ ON DELETE action ] [ ON UPDATE action ]
+{ NOT NULL | NULL | UNIQUE | PRIMARY KEY |
+CHECK (expression) |
+REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
+[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
-}
-and table_constraint can be:
+and table_constraint is:
+
[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) |
PRIMARY KEY ( column_name [, ... ] ) |
-CHECK ( condition ) |
-FOREIGN KEY ( column_name [, ... ] ) REFERENCES table [ ( column [, ... ] ) ]
-[ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ]
+CHECK ( expression ) |
+FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
+[ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
-}
changedALTER TABLE
--- 7.1
+++ 7.2
@@ -1,12 +1,16 @@
ALTER TABLE [ ONLY ] table [ * ]
-ADD [ COLUMN ] column type
+ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
ALTER TABLE [ ONLY ] table [ * ]
ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT }
-ALTER TABLE table [ * ]
+ALTER TABLE [ ONLY ] table [ * ]
+ALTER [ COLUMN ] column SET STATISTICS integer
+ALTER TABLE [ ONLY ] table [ * ]
RENAME [ COLUMN ] column TO newcolumn
ALTER TABLE table
-RENAME TO newtable
+RENAME TO new_table
ALTER TABLE table
-ADD table constraint definition
+ADD table_constraint_definition
+ALTER TABLE [ ONLY ] table
+DROP CONSTRAINT constraint { RESTRICT | CASCADE }
ALTER TABLE table
-OWNER TO new owner
+OWNER TO new_owner
changedCREATE TABLE AS
--- 7.1
+++ 7.2
@@ -1,2 +1,2 @@
-CREATE [ TEMPORARY | TEMP ] TABLE table [ (column [, ...] ) ]
-AS select_clause
+CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name [ (column_name [, ...] ) ]
+AS query
changedCREATE VIEW
--- 7.1 +++ 7.2 @@ -1 +1 @@ -CREATE VIEW view AS SELECT query +CREATE VIEW view [ ( column name list ) ] AS SELECT query
changedCREATE INDEX
--- 7.1 +++ 7.2 @@ -1,4 +1,6 @@ CREATE [ UNIQUE ] INDEX index_name ON table -[ USING acc_name ] ( column [ ops_name ] [, ...] ) +[ USING acc_method ] ( column [ ops_name ] [, ...] ) +[ WHERE predicate ] CREATE [ UNIQUE ] INDEX index_name ON table -[ USING acc_name ] ( func_name( column [, ... ]) [ ops_name ] ) +[ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] ) +[ WHERE predicate ]
changedDROP AGGREGATE
--- 7.1 +++ 7.2 @@ -1 +1 @@ -DROP AGGREGATE name type +DROP AGGREGATE name ( type )
changedCREATE FUNCTION
--- 7.1 +++ 7.2 @@ -1,10 +1,10 @@ -CREATE FUNCTION name ( [ ftype [, ...] ] ) -RETURNS rtype -AS definition -LANGUAGE 'langname' +CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] ) +RETURNS rettype +AS 'definition' +LANGUAGE langname [ WITH ( attribute [, ...] ) ] -CREATE FUNCTION name ( [ ftype [, ...] ] ) -RETURNS rtype -AS obj_file , link_symbol -LANGUAGE 'langname' +CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] ) +RETURNS rettype +AS 'obj_file', 'link_symbol' +LANGUAGE langname [ WITH ( attribute [, ...] ) ]
changedCREATE LANGUAGE
--- 7.1 +++ 7.2 @@ -1,3 +1,2 @@ -CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE 'langname' +CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE langname HANDLER call_handler -LANCOMPILER 'comment'
changedDROP LANGUAGE
--- 7.1 +++ 7.2 @@ -1 +1 @@ -DROP [ PROCEDURAL ] LANGUAGE 'name' +DROP [ PROCEDURAL ] LANGUAGE name
changedCREATE OPERATOR
--- 7.1 +++ 7.2 @@ -1,5 +1,6 @@ CREATE OPERATOR name ( PROCEDURE = func_name -[, LEFTARG = type1 ] [, RIGHTARG = type2 ] +[, LEFTARG = lefttype +] [, RIGHTARG = righttype ] [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ] [, RESTRICT = res_proc ] [, JOIN = join_proc ] [, HASHES ] [, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ] )
changedCREATE TYPE
--- 7.1
+++ 7.2
@@ -1,7 +1,7 @@
CREATE TYPE typename ( INPUT = input_function, OUTPUT = output_function
, INTERNALLENGTH = { internallength | VARIABLE }
[ , EXTERNALLENGTH = { externallength | VARIABLE } ]
-[ , DEFAULT = "default" ]
+[ , DEFAULT = default ]
[ , ELEMENT = element ] [ , DELIMITER = delimiter ]
[ , SEND = send_function ] [ , RECEIVE = receive_function ]
[ , PASSEDBYVALUE ]
changedGRANT
--- 7.1
+++ 7.2
@@ -1,2 +1,3 @@
-GRANT privilege [, ...] ON object [, ...]
-TO { PUBLIC | GROUP group | username }
+GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] }
+ON [ TABLE ] objectname [, ...]
+TO { username | GROUP groupname | PUBLIC } [, ...]
changedREVOKE
--- 7.1
+++ 7.2
@@ -1,3 +1,3 @@
-REVOKE privilege [, ...]
-ON object [, ...]
-FROM { PUBLIC | GROUP groupname | username }
+REVOKE { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] }
+ON [ TABLE ] object [, ...]
+FROM { username | GROUP groupname | PUBLIC } [, ...]
changedCREATE GROUP
--- 7.1 +++ 7.2 @@ -1,4 +1,6 @@ -CREATE GROUP name -[ WITH -[ SYSID gid ] -[ USER username [, ...] ] ] +CREATE GROUP name [ [ WITH ] option [ ... ] ] + +where option can be: + +SYSID gid +| USER username [, ...]
addedSET SESSION AUTHORIZATION
--- 7.1 +++ 7.2 @@ -0,0 +1 @@ +SET SESSION AUTHORIZATION 'username'
changedCREATE USER
--- 7.1 +++ 7.2 @@ -1,7 +1,10 @@ -CREATE USER username -[ WITH -[ SYSID uid ] -[ PASSWORD 'password' ] ] -[ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ] -[ IN GROUP groupname [, ...] ] -[ VALID UNTIL 'abstime' ] +CREATE USER username [ [ WITH ] option [ ... ] ] + +where option can be: + +SYSID uid +| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' +| CREATEDB | NOCREATEDB +| CREATEUSER | NOCREATEUSER +| IN GROUP groupname [, ...] +| VALID UNTIL 'abstime'
changedALTER USER
--- 7.1 +++ 7.2 @@ -1,4 +1,8 @@ -ALTER USER username -[ WITH PASSWORD 'password' ] -[ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ] -[ VALID UNTIL 'abstime' ] +ALTER USER username [ [ WITH ] option [ ... ] ] + +where option can be: + +[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' +| CREATEDB | NOCREATEDB +| CREATEUSER | NOCREATEUSER +| VALID UNTIL 'abstime'
changedLOAD
changedEXPLAIN
--- 7.1 +++ 7.2 @@ -1 +1 @@ -EXPLAIN [ VERBOSE ] query +EXPLAIN [ ANALYZE ] [ VERBOSE ] query
changedLOCK
--- 7.1
+++ 7.2
@@ -1,3 +1,7 @@
-LOCK [ TABLE ] name
-LOCK [ TABLE ] name IN [ ROW | ACCESS ] { SHARE | EXCLUSIVE } MODE
-LOCK [ TABLE ] name IN SHARE ROW EXCLUSIVE MODE
+LOCK [ TABLE ] name [, ...]
+LOCK [ TABLE ] name [, ...] IN lockmode MODE
+
+where lockmode is one of:
+
+ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE |
+SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE
changedSELECT
--- 7.1
+++ 7.2
@@ -4,10 +4,11 @@
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
-[ { UNION | INTERSECT | EXCEPT [ ALL ] } select ]
+[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ FOR UPDATE [ OF tablename [, ...] ] ]
-[ LIMIT { count | ALL } [ { OFFSET | , } start ]]
+[ LIMIT { count | ALL } ]
+[ OFFSET start ]
where from_item can be:
changedSELECT INTO
--- 7.1
+++ 7.2
@@ -5,10 +5,11 @@
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
-[ { UNION | INTERSECT | EXCEPT [ ALL ] } select ]
+[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ FOR UPDATE [ OF tablename [, ...] ] ]
-[ LIMIT { count | ALL } [ { OFFSET | , } start ]]
+[ LIMIT [ start , ] { count | ALL } ]
+[ OFFSET start ]
where from_item can be:
changedSET TRANSACTION
--- 7.1
+++ 7.2
@@ -1,2 +1,3 @@
SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE }
-SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE }
+SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL
+{ READ COMMITTED | SERIALIZABLE }
changedRESET
--- 7.1 +++ 7.2 @@ -1 +1,4 @@ RESET variable + + +RESET ALL
changedSHOW
--- 7.1 +++ 7.2 @@ -1 +1,4 @@ SHOW name + + +SHOW ALL
addedANALYZE
--- 7.1 +++ 7.2 @@ -0,0 +1 @@ +ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]
changedVACUUM
--- 7.1 +++ 7.2 @@ -1,2 +1,2 @@ -VACUUM [ VERBOSE ] [ ANALYZE ] [ table ] -VACUUM [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ] +VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ] +VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]
changedCOMMENT
--- 7.1 +++ 7.2 @@ -1,10 +1,9 @@ COMMENT ON [ -[ DATABASE | INDEX | RULE | SEQUENCE | TABLE | TYPE | VIEW ] -object_name | -COLUMN table_name.column_name| -AGGREGATE agg_name agg_type| -FUNCTION func_name (arg1, arg2, ...)| +[ DATABASE | INDEX | RULE | SEQUENCE | TABLE | TYPE | VIEW ] object_name | +COLUMN table_name.column_name | +AGGREGATE agg_name (agg_type) | +FUNCTION func_name (arg1, arg2, ...) | OPERATOR op (leftoperand_type rightoperand_type) | TRIGGER trigger_name ON table_name ] IS 'text'