↑↓ select ↵ open ⌫ change scope Open full search

PG.CENTER connects PostgreSQL documentation, reference, and ecosystem knowledge. Maintained by Pigsty.

SQL Commands / Version comparison

What changed?

SQL Commands · PostgreSQL 7.4 → 8.0

Browse PG 8.0

Historical documentation for a PostgreSQL version that is no longer supported.

9 added · 0 removed · 27 changed · 0 coverage changes. These are changes between the pinned reference snapshots.

changedCREATE TABLE 
--- 7.4
+++ 8.0
@@ -6,11 +6,15 @@
 [ INHERITS ( parent_table [, ... ] ) ]
 [ WITH OIDS | WITHOUT OIDS ]
 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
+[ TABLESPACE tablespace ]
 
 where column_constraint is:
 
 [ CONSTRAINT constraint_name ]
-{ NOT NULL | NULL | UNIQUE | PRIMARY KEY |
+{ NOT NULL |
+NULL |
+UNIQUE [ USING INDEX TABLESPACE tablespace ] |
+PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |
 CHECK (expression) |
 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
 [ ON DELETE action ] [ ON UPDATE action ] }
@@ -19,8 +23,8 @@
 and table_constraint is:
 
 [ CONSTRAINT constraint_name ]
-{ UNIQUE ( column_name [, ... ] ) |
-PRIMARY KEY ( column_name [, ... ] ) |
+{ UNIQUE ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
+PRIMARY KEY ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
 CHECK ( expression ) |
 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
 [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
changedALTER TABLE 
--- 7.4
+++ 8.0
@@ -1,26 +1,24 @@
 ALTER TABLE [ ONLY ] name [ * ]
-ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
-ALTER TABLE [ ONLY ] name [ * ]
-DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
-ALTER TABLE [ ONLY ] name [ * ]
-ALTER [ COLUMN ] column { SET DEFAULT expression | DROP DEFAULT }
-ALTER TABLE [ ONLY ] name [ * ]
-ALTER [ COLUMN ] column { SET | DROP } NOT NULL
-ALTER TABLE [ ONLY ] name [ * ]
-ALTER [ COLUMN ] column SET STATISTICS integer
-ALTER TABLE [ ONLY ] name [ * ]
-ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
-ALTER TABLE [ ONLY ] name [ * ]
-SET WITHOUT OIDS
+action [, ... ]
 ALTER TABLE [ ONLY ] name [ * ]
 RENAME [ COLUMN ] column TO new_column
 ALTER TABLE name
 RENAME TO new_name
-ALTER TABLE [ ONLY ] name [ * ]
+
+where action is one of:
+
+ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
+DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
+ALTER [ COLUMN ] column TYPE type [ USING expression ]
+ALTER [ COLUMN ] column SET DEFAULT expression
+ALTER [ COLUMN ] column DROP DEFAULT
+ALTER [ COLUMN ] column { SET | DROP } NOT NULL
+ALTER [ COLUMN ] column SET STATISTICS integer
+ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
 ADD table_constraint
-ALTER TABLE [ ONLY ] name [ * ]
 DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
-ALTER TABLE name
+CLUSTER ON index_name
+SET WITHOUT CLUSTER
+SET WITHOUT OIDS
 OWNER TO new_owner
-ALTER TABLE name
-CLUSTER ON index_name
+SET TABLESPACE tablespace_name
changedCREATE TABLE AS 
--- 7.4
+++ 8.0
@@ -1,2 +1,2 @@
-CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name [ (column_name [, ...] ) ]
+CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name [ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
 AS query
changedCREATE INDEX 
--- 7.4
+++ 8.0
@@ -1,3 +1,4 @@
 CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
 ( { column | ( expression ) } [ opclass ] [, ...] )
+[ TABLESPACE tablespace ]
 [ WHERE predicate ]
addedALTER INDEX 
--- 7.4
+++ 8.0
@@ -0,0 +1,9 @@
+ALTER INDEX name
+action [, ... ]
+ALTER INDEX name
+RENAME TO new_name
+
+where action is one of:
+
+OWNER TO new_owner
+SET TABLESPACE indexspace_name
changedALTER AGGREGATE 
--- 7.4
+++ 8.0
@@ -1 +1,2 @@
 ALTER AGGREGATE name ( type ) RENAME TO newname
+ALTER AGGREGATE name ( type ) OWNER TO newowner
changedCREATE FUNCTION 
--- 7.4
+++ 8.0
@@ -1,9 +1,9 @@
-CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] )
+CREATE [ OR REPLACE ] FUNCTION name ( [ [ argname ] argtype [, ...] ] )
 RETURNS rettype
 { LANGUAGE langname
 | IMMUTABLE | STABLE | VOLATILE
 | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
-| [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER
+| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
 | AS 'definition'
 | AS 'obj_file', 'link_symbol'
 } ...
changedALTER FUNCTION 
--- 7.4
+++ 8.0
@@ -1 +1,2 @@
 ALTER FUNCTION name ( [ type [, ...] ] ) RENAME TO newname
+ALTER FUNCTION name ( [ type [, ...] ] ) OWNER TO newowner
changedCREATE CAST 
--- 7.4
+++ 8.0
@@ -1,5 +1,5 @@
 CREATE CAST (sourcetype AS targettype)
-WITH FUNCTION funcname (argtype)
+WITH FUNCTION funcname (argtypes)
 [ AS ASSIGNMENT | AS IMPLICIT ]
 
 CREATE CAST (sourcetype AS targettype)
changedALTER CONVERSION 
--- 7.4
+++ 8.0
@@ -1 +1,2 @@
 ALTER CONVERSION name RENAME TO newname
+ALTER CONVERSION name OWNER TO newowner
addedALTER OPERATOR 
--- 7.4
+++ 8.0
@@ -0,0 +1 @@
+ALTER OPERATOR name ( { lefttype | NONE } , { righttype | NONE } ) OWNER TO newowner
changedDROP OPERATOR 
--- 7.4
+++ 8.0
@@ -1 +1 @@
-DROP OPERATOR name ( lefttype | NONE , righttype | NONE ) [ CASCADE | RESTRICT ]
+DROP OPERATOR name ( { lefttype | NONE } , { righttype | NONE } ) [ CASCADE | RESTRICT ]
changedALTER OPERATOR CLASS 
--- 7.4
+++ 8.0
@@ -1 +1,2 @@
 ALTER OPERATOR CLASS name USING index_method RENAME TO newname
+ALTER OPERATOR CLASS name USING index_method OWNER TO newowner
changedCREATE TYPE 
--- 7.4
+++ 8.0
@@ -6,6 +6,7 @@
 OUTPUT = output_function
 [ , RECEIVE = receive_function ]
 [ , SEND = send_function ]
+[ , ANALYZE = analyze_function ]
 [ , INTERNALLENGTH = { internallength | VARIABLE } ]
 [ , PASSEDBYVALUE ]
 [ , ALIGNMENT = alignment ]
addedALTER TYPE 
--- 7.4
+++ 8.0
@@ -0,0 +1 @@
+ALTER TYPE name OWNER TO new_owner
changedCREATE DATABASE 
--- 7.4
+++ 8.0
@@ -1,5 +1,5 @@
 CREATE DATABASE name
 [ [ WITH ] [ OWNER [=] dbowner ]
-[ LOCATION [=] 'dbpath' ]
 [ TEMPLATE [=] template ]
-[ ENCODING [=] encoding ] ]
+[ ENCODING [=] encoding ]
+[ TABLESPACE [=] tablespace ] ]
changedALTER DATABASE 
--- 7.4
+++ 8.0
@@ -2,3 +2,5 @@
 ALTER DATABASE name RESET parameter
 
 ALTER DATABASE name RENAME TO newname
+
+ALTER DATABASE name OWNER TO new_owner
changedALTER SCHEMA 
--- 7.4
+++ 8.0
@@ -1 +1,2 @@
 ALTER SCHEMA name RENAME TO newname
+ALTER SCHEMA name OWNER TO newowner
addedCREATE TABLESPACE 
--- 7.4
+++ 8.0
@@ -0,0 +1 @@
+CREATE TABLESPACE tablespacename [ OWNER username ] LOCATION 'directory'
addedALTER TABLESPACE 
--- 7.4
+++ 8.0
@@ -0,0 +1,2 @@
+ALTER TABLESPACE name RENAME TO newname
+ALTER TABLESPACE name OWNER TO newowner
addedDROP TABLESPACE 
--- 7.4
+++ 8.0
@@ -0,0 +1 @@
+DROP TABLESPACE tablespacename
changedGRANT 
--- 7.4
+++ 8.0
@@ -18,3 +18,7 @@
 GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
 ON SCHEMA schemaname [, ...]
 TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+
+GRANT { CREATE | ALL [ PRIVILEGES ] }
+ON TABLESPACE tablespacename [, ...]
+TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
changedREVOKE 
--- 7.4
+++ 8.0
@@ -28,3 +28,9 @@
 ON SCHEMA schemaname [, ...]
 FROM { username | GROUP groupname | PUBLIC } [, ...]
 [ CASCADE | RESTRICT ]
+
+REVOKE [ GRANT OPTION FOR ]
+{ CREATE | ALL [ PRIVILEGES ] }
+ON TABLESPACE tablespacename [, ...]
+FROM { username | GROUP groupname | PUBLIC } [, ...]
+[ CASCADE | RESTRICT ]
changedCREATE USER 
--- 7.4
+++ 8.0
@@ -3,8 +3,8 @@
 where option can be:
 
 SYSID uid
-| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
 | CREATEDB | NOCREATEDB
 | CREATEUSER | NOCREATEUSER
 | IN GROUP groupname [, ...]
+| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
 | VALID UNTIL 'abstime'
changedALTER USER 
--- 7.4
+++ 8.0
@@ -2,9 +2,9 @@
 
 where option can be:
 
-[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
-| CREATEDB | NOCREATEDB
+CREATEDB | NOCREATEDB
 | CREATEUSER | NOCREATEUSER
+| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
 | VALID UNTIL 'abstime'
 
 ALTER USER name RENAME TO newname
changedCREATE RULE 
--- 7.4
+++ 8.0
@@ -1,3 +1,3 @@
 CREATE [ OR REPLACE ] RULE name AS ON event
 TO table [ WHERE condition ]
-DO [ INSTEAD ] { NOTHING | command | ( command ; command ... ) }
+DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }
changedCOPY 
--- 7.4
+++ 8.0
@@ -4,7 +4,10 @@
 [ BINARY ]
 [ OIDS ]
 [ DELIMITER [ AS ] 'delimiter' ]
-[ NULL [ AS ] 'null string' ] ]
+[ NULL [ AS ] 'null string' ]
+[ CSV [ QUOTE [ AS ] 'quote' ]
+[ ESCAPE [ AS ] 'escape' ]
+[ FORCE NOT NULL column [, ...] ]
 
 COPY tablename [ ( column [, ...] ) ]
 TO { 'filename' | STDOUT }
@@ -12,4 +15,7 @@
 [ BINARY ]
 [ OIDS ]
 [ DELIMITER [ AS ] 'delimiter' ]
-[ NULL [ AS ] 'null string' ] ]
+[ NULL [ AS ] 'null string' ]
+[ CSV [ QUOTE [ AS ] 'quote' ]
+[ ESCAPE [ AS ] 'escape' ]
+[ FORCE QUOTE column [, ...] ]
changedLOCK 
--- 7.4
+++ 8.0
@@ -1,4 +1,4 @@
-LOCK [ TABLE ] name [, ...] [ IN lockmode MODE ]
+LOCK [ TABLE ] name [, ...] [ IN lockmode MODE ] [ NOWAIT ]
 
 where lockmode is one of:
changedBEGIN 
--- 7.4
+++ 8.0
@@ -1 +1,6 @@
-BEGIN [ WORK | TRANSACTION ]
+BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]
+
+where transaction_mode is one of:
+
+ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
+READ WRITE | READ ONLY
addedSAVEPOINT 
--- 7.4
+++ 8.0
@@ -0,0 +1 @@
+SAVEPOINT savepoint_name
addedRELEASE SAVEPOINT 
--- 7.4
+++ 8.0
@@ -0,0 +1 @@
+RELEASE [ SAVEPOINT ] savepoint_name
addedROLLBACK TO SAVEPOINT 
--- 7.4
+++ 8.0
@@ -0,0 +1 @@
+ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name
changedSET TRANSACTION 
--- 7.4
+++ 8.0
@@ -1,4 +1,7 @@
-SET TRANSACTION
-[ ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE } ] [ READ WRITE | READ ONLY ]
-SET SESSION CHARACTERISTICS AS TRANSACTION
-[ ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE } ] [ READ WRITE | READ ONLY ]
+SET TRANSACTION transaction_mode [, ...]
+SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]
+
+where transaction_mode is one of:
+
+ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
+READ WRITE | READ ONLY
changedSTART TRANSACTION 
--- 7.4
+++ 8.0
@@ -1 +1,6 @@
-START TRANSACTION [ ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE } ] [ READ WRITE | READ ONLY ]
+START TRANSACTION [ transaction_mode [, ...] ]
+
+where transaction_mode is one of:
+
+ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
+READ WRITE | READ ONLY
changedVACUUM 
--- 7.4
+++ 8.0
@@ -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 
--- 7.4
+++ 8.0
@@ -3,12 +3,17 @@
 TABLE object_name |
 COLUMN table_name.column_name |
 AGGREGATE agg_name (agg_type) |
+CAST (sourcetype AS targettype) |
 CONSTRAINT constraint_name ON table_name |
+CONVERSION object_name |
 DATABASE object_name |
 DOMAIN object_name |
 FUNCTION func_name (arg1_type, arg2_type, ...) |
 INDEX object_name |
+LARGE OBJECT large_object_oid |
 OPERATOR op (leftoperand_type, rightoperand_type) |
+OPERATOR CLASS object_name USING index_method |
+[ PROCEDURAL ] LANGUAGE object_name |
 RULE rule_name ON table_name |
 SCHEMA object_name |
 SEQUENCE object_name |