SQL Commands / Version comparison
What changed?
SQL Commands · PostgreSQL 6.5 → 7.0
Historical documentation for a PostgreSQL version that is no longer supported.
9 added · 0 removed · 24 changed · 0 coverage changes. These are changes between the pinned reference snapshots.
addedTRUNCATE
--- 6.5 +++ 7.0 @@ -0,0 +1 @@ +TRUNCATE [ TABLE ] name
changedCREATE SEQUENCE
--- 6.5 +++ 7.0 @@ -1,7 +1,3 @@ -CREATE SEQUENCE seqname -[ INCREMENT increment ] -[ MINVALUE minvalue ] -[ MAXVALUE maxvalue ] -[ START start ] -[ CACHE cache ] -[ CYCLE ] +CREATE SEQUENCE seqname [ INCREMENT increment ] +[ MINVALUE minvalue ] [ MAXVALUE maxvalue ] +[ START start ] [ CACHE cache ] [ CYCLE ]
changedALTER TABLE
--- 6.5
+++ 7.0
@@ -1,6 +1,10 @@
-ALTER TABLE table
-[ * ] ADD [ COLUMN ] ER">coBLE> type
-ALTER TABLE table
-[ * ] RENAME [ COLUMN ] ER">coBLE> TO newcolumn
+ALTER TABLE table [ * ]
+ADD [ COLUMN ] column type
+ALTER TABLE table [ * ]
+ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT }
+ALTER TABLE table [ * ]
+RENAME [ COLUMN ] column TO newcolumn
ALTER TABLE table
RENAME TO newtable
+ALTER TABLE table
+ADD table constraint definition
addedCREATE TABLE AS
--- 6.5 +++ 7.0 @@ -0,0 +1,2 @@ +CREATE TABLE table [ (column [, ...] ) ] +AS select_clause
changedCREATE VIEW
--- 6.5 +++ 7.0 @@ -1,2 +1 @@ -CREATE VIEW view -AS SELECT query +CREATE VIEW view AS SELECT query
addedREINDEX
--- 6.5
+++ 7.0
@@ -0,0 +1 @@
+REINDEX { TABLE | DATABASE | INDEX } name [ FORCE ]
changedCREATE INDEX
--- 6.5 +++ 7.0 @@ -1,6 +1,4 @@ -CREATE [ UNIQUE ] INDEX index_name -ON table [ USING acc_name ] -( column [ ops_name] [, ...] ) -CREATE [ UNIQUE ] INDEX index_name -ON table [ USING acc_name ] -( func_name( r">colle> [, ... ]) ops_name ) +CREATE [ UNIQUE ] INDEX index_name ON table +[ USING acc_name ] ( column [ ops_name ] [, ...] ) +CREATE [ UNIQUE ] INDEX index_name ON table +[ USING acc_name ] ( func_name( column [, ... ]) [ ops_name ] )
changedCREATE AGGREGATE
--- 6.5 +++ 7.0 @@ -1,10 +1,6 @@ -CREATE AGGREGATE name [ AS ] -( BASETYPE = data_type -[ , SFUNC1 = sfunc1 -, STYPE1 = sfunc1_return_type ] -[ , SFUNC2 = sfunc2 -, STYPE2 = sfunc2_return_type ] +CREATE AGGREGATE name ( BASETYPE = input_data_type +[ , SFUNC1 = sfunc1, STYPE1 = state1_type ] +[ , SFUNC2 = sfunc2, STYPE2 = state2_type ] [ , FINALFUNC = ffunc ] [ , INITCOND1 = initial_condition1 ] -[ , INITCOND2 = initial_condition2 ] -) +[ , INITCOND2 = initial_condition2 ] )
changedCREATE FUNCTION
--- 6.5 +++ 7.0 @@ -2,3 +2,9 @@ RETURNS rtype AS definition LANGUAGE 'langname' +[ WITH ( attribute [, ...] ) ] +CREATE FUNCTION name ( [ ftype [, ...] ] ) +RETURNS rtype +AS obj_file , link_symbol +LANGUAGE 'C' +[ WITH ( attribute [, ...] ) ]
changedCREATE OPERATOR
--- 6.5 +++ 7.0 @@ -1,12 +1,5 @@ -CREATE OPERATOR name ( -PROCEDURE = func_name -[, LEFTARG = type1 ] -[, RIGHTARG = type2 ] -[, COMMUTATOR = com_op ] -[, NEGATOR = neg_op ] -[, RESTRICT = res_proc ] -[, JOIN = join_proc ] -[, HASHES ] -[, SORT1 = left_sort_op ] -[, SORT2 = right_sort_op ] -) +CREATE OPERATOR name ( PROCEDURE = func_name +[, LEFTARG = type1 ] [, RIGHTARG = type2 ] +[, COMMUTATOR = com_op ] [, NEGATOR = neg_op ] +[, RESTRICT = res_proc ] [, JOIN = join_proc ] +[, HASHES ] [, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ] )
changedCREATE TYPE
--- 6.5
+++ 7.0
@@ -1,12 +1,6 @@
-CREATE TYPE typename (
-INPUT = input_function
-, OUTPUT = output_function
-, INTERNALLENGTH = (internallength | VARIABLE)
-[ , EXTERNALLENGTH = (externallength | VARIABLE) ]
-[ , ELEMENT = element ]
-[ , DELIMITER = delimiter ]
+CREATE TYPE typename ( INPUT = input_function, OUTPUT = output_function
+, INTERNALLENGTH = { internallength | VARIABLE } [ , EXTERNALLENGTH = { externallength | VARIABLE } ]
[ , DEFAULT = "default" ]
-[ , SEND = send_function ]
-[ , RECEIVE = receive_function ]
-[ , PASSEDBYVALUE ]
-)
+[ , ELEMENT = element ] [ , DELIMITER = delimiter ]
+[ , SEND = send_function ] [ , RECEIVE = receive_function ]
+[ , PASSEDBYVALUE ] )
changedREVOKE
--- 6.5
+++ 7.0
@@ -1,3 +1,3 @@
REVOKE privilege [, ...]
ON object [, ...]
-FROM { PUBLIC | GROUP ER">gBLE> | username }
+FROM { PUBLIC | GROUP groupname | username }
addedCREATE GROUP
--- 6.5 +++ 7.0 @@ -0,0 +1,4 @@ +CREATE GROUP name +[ WITH +[ SYSID gid ] +[ USER username [, ...] ] ]
addedALTER GROUP
--- 6.5 +++ 7.0 @@ -0,0 +1,2 @@ +ALTER GROUP name ADD USER username [, ... ] +ALTER GROUP name DROP USER username [, ... ]
addedDROP GROUP
--- 6.5 +++ 7.0 @@ -0,0 +1 @@ +DROP GROUP name
changedCREATE USER
--- 6.5 +++ 7.0 @@ -1,6 +1,7 @@ CREATE USER username -[ WITH PASSWORD password ] -[ CREATEDB | NOCREATEDB ] -[ CREATEUSER | NOCREATEUSER ] +[ WITH +[ SYSID uid ] +[ PASSWORD 'password' ] ] +[ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ] [ IN GROUP groupname [, ...] ] [ VALID UNTIL 'abstime' ]
changedALTER USER
--- 6.5 +++ 7.0 @@ -1,4 +1,4 @@ -ALTER USER username [ WITH PASSWORD password ] +ALTER USER username +[ WITH PASSWORD 'password' ] [ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ] -[ IN GROUP groupname [, ...] ] [ VALID UNTIL 'abstime' ]
addedCREATE CONSTRAINT TRIGGER
--- 6.5
+++ 7.0
@@ -0,0 +1,4 @@
+CREATE CONSTRAINT TRIGGER name
+AFTER events ON
+relation constraint attributes
+FOR EACH ROW EXECUTE PROCEDURE func '(' args ')'
changedCREATE TRIGGER
--- 6.5
+++ 7.0
@@ -1,3 +1,3 @@
CREATE TRIGGER name { BEFORE | AFTER } { event [OR ...] }
ON table FOR EACH { ROW | STATEMENT }
-EXECUTE PROCEDURE ER">funcBLE> ( arguments )
+EXECUTE PROCEDURE func ( arguments )
changedCOPY
--- 6.5
+++ 7.0
@@ -1,8 +1,8 @@
COPY [ BINARY ] table [ WITH OIDS ]
-FROM { 'filename' |
-stdin }
-[ USING DELIMITERS 'delimiter' ]
+FROM { 'filename' | stdin }
+[ [USING] DELIMITERS 'delimiter' ]
+[ WITH NULL AS 'null string' ]
COPY [ BINARY ] table [ WITH OIDS ]
-TO { 'filename' |
-stdout }
-[ USING DELIMITERS 'delimiter' ]
+TO { 'filename' | stdout }
+[ [USING] DELIMITERS 'delimiter' ]
+[ WITH NULL AS 'null string' ]
changedLOCK
--- 6.5
+++ 7.0
@@ -1,3 +1,3 @@
-LOCK [ TABLE ] table
-LOCK [ TABLE ] table IN [ ROW | ACCESS ] { SHARE | EXCLUSIVE } MODE
-LOCK [ TABLE ] table IN SHARE ROW EXCLUSIVE MODE
+LOCK [ TABLE ] name
+LOCK [ TABLE ] name IN [ ROW | ACCESS ] { SHARE | EXCLUSIVE } MODE
+LOCK [ TABLE ] name IN SHARE ROW EXCLUSIVE MODE
changedSELECT
--- 6.5
+++ 7.0
@@ -1,13 +1,11 @@
-SELECT [ALL|DISTINCT [ON column] ]
-expression [ AS
-name ] [, ...]
-[ INTO [TEMP] [TABLE] new_table ]
-[ FROM table
-[alias ] [, ...] ]
+SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
+expression [ AS name ] [, ...]
+[ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
+[ FROM table [ alias ] [, ...] ]
[ WHERE condition ]
[ GROUP BY column [, ...] ]
[ HAVING condition [, ...] ]
-[ { UNION [ALL] | INTERSECT | EXCEPT } select ]
-[ ORDER BY column [ ASC | DESC ] [, ...] ]
-[ FOR UPDATE [OF class_name...]]
-[ LIMIT count [OFFSET|, count]]
+[ { UNION [ ALL ] | INTERSECT | EXCEPT } select ]
+[ ORDER BY column [ ASC | DESC | USING operator ] [, ...] ]
+[ FOR UPDATE [ OF class_name [, ...] ] ]
+LIMIT { count | ALL } [ { OFFSET | , } start ]
changedUPDATE
--- 6.5 +++ 7.0 @@ -1,3 +1,3 @@ -UPDATE table SET column = expression [, ...] +UPDATE table SET col = expression [, ...] [ FROM fromlist ] [ WHERE condition ]
changedSELECT INTO
--- 6.5
+++ 7.0
@@ -1,10 +1,11 @@
-SELECT [ ALL | DISTINCT ] expression [ AS name ] [, ...]
-INTO [TEMP] [ TABLE ] new_table ]
-[ FROM table [alias] [, ...] ]
+SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
+expression [ AS name ] [, ...]
+[ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
+[ FROM table [ alias ] [, ...] ]
[ WHERE condition ]
[ GROUP BY column [, ...] ]
[ HAVING condition [, ...] ]
-[ { UNION [ALL] | INTERSECT | EXCEPT } select]
-[ ORDER BY column [ ASC | DESC ] [, ...] ]
-[ FOR UPDATE [OF class_name...]]
-[ LIMIT count [OFFSET|, count]]
+[ { UNION [ ALL ] | INTERSECT | EXCEPT } select ]
+[ ORDER BY column [ ASC | DESC | USING operator ] [, ...] ]
+[ FOR UPDATE [ OF class_name [, ...] ] ]
+LIMIT { count | ALL } [ { OFFSET | , } start ]
changedDECLARE
--- 6.5
+++ 7.0
@@ -1,3 +1,3 @@
-DECLARE cursor [ BINARY ] [ INSENSITIVE ] [ SCROLL ]
+DECLARE cursorname [ BINARY ] [ INSENSITIVE ] [ SCROLL ]
CURSOR FOR query
[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] ]
changedMOVE
--- 6.5
+++ 7.0
@@ -1,3 +1,2 @@
MOVE [ selector ] [ count ]
{ IN | FROM } cursor
-FETCH [ RELATIVE ] [ { [ # | ALL | NEXT | PRIOR ] } ] FROM ] cursor
changedABORT
--- 6.5 +++ 7.0 @@ -1 +1 @@ -ABORT [ TRANSACTION | WORK ] +ABORT [ WORK | TRANSACTION ]
addedEND
--- 6.5 +++ 7.0 @@ -0,0 +1 @@ +END [ WORK | TRANSACTION ]
changedSET
--- 6.5
+++ 7.0
@@ -1,3 +1,4 @@
-SET variable { TO | = } { 'value' | DEFAULT }
+SET variable { TO | = } { value | 'value' | DEFAULT }
+SET CONSTRAINTS {ALL | constraintlist} mode
SET TIME ZONE { 'timezone' | LOCAL | DEFAULT }
-SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | SERIALIZED }
+SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE }
changedSHOW
--- 6.5 +++ 7.0 @@ -1 +1 @@ -SHOW variable +SHOW keyword
changedUNLISTEN
changedVACUUM
addedCOMMENT
--- 6.5 +++ 7.0 @@ -0,0 +1,10 @@ +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, ...)| +OPERATOR op (leftoperand_type rightoperand_type) | +TRIGGER trigger_name ON table_name +] IS 'text'