↑↓ 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 8.0 → 8.1

Browse PG 8.1

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

7 added · 0 removed · 30 changed · 0 coverage changes. These are changes between the pinned reference snapshots.

changedTRUNCATE 
--- 8.0
+++ 8.1
@@ -1 +1 @@
-TRUNCATE [ TABLE ] name
+TRUNCATE [ TABLE ] name [, ...]
changedALTER SEQUENCE 
--- 8.0
+++ 8.1
@@ -1,3 +1,4 @@
 ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
 [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
 [ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
+ALTER SEQUENCE name SET SCHEMA new_schema
changedCREATE TABLE 
--- 8.0
+++ 8.1
@@ -1,8 +1,9 @@
-CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
+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 ] }
+[, ... ]
+] )
 [ INHERITS ( parent_table [, ... ] ) ]
 [ WITH OIDS | WITHOUT OIDS ]
 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
changedALTER TABLE 
--- 8.0
+++ 8.1
@@ -4,6 +4,8 @@
 RENAME [ COLUMN ] column TO new_column
 ALTER TABLE name
 RENAME TO new_name
+ALTER TABLE name
+SET SCHEMA new_schema
 
 where action is one of:
 
@@ -17,8 +19,10 @@
 ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
 ADD table_constraint
 DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
+DISABLE TRIGGER [ trigger_name | ALL | USER ]
+ENABLE TRIGGER [ trigger_name | ALL | USER ]
 CLUSTER ON index_name
 SET WITHOUT CLUSTER
 SET WITHOUT OIDS
 OWNER TO new_owner
-SET TABLESPACE tablespace_name
+SET TABLESPACE new_tablespace
changedCREATE TABLE AS 
--- 8.0
+++ 8.1
@@ -1,2 +1,3 @@
-CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name [ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
+CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
+[ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
 AS query
changedCREATE VIEW 
--- 8.0
+++ 8.1
@@ -1 +1,2 @@
-CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query
+CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] ) ]
+AS query
changedREINDEX 
--- 8.0
+++ 8.1
@@ -1 +1 @@
-REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]
+REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]
changedALTER INDEX 
--- 8.0
+++ 8.1
@@ -1,9 +1,2 @@
-ALTER INDEX name
-action [, ... ]
-ALTER INDEX name
-RENAME TO new_name
-
-where action is one of:
-
-OWNER TO new_owner
-SET TABLESPACE indexspace_name
+ALTER INDEX name RENAME TO new_name
+ALTER INDEX name SET TABLESPACE tablespace_name
changedCREATE AGGREGATE 
--- 8.0
+++ 8.1
@@ -4,4 +4,5 @@
 STYPE = state_data_type
 [ , FINALFUNC = ffunc ]
 [ , INITCOND = initial_condition ]
+[ , SORTOP = sort_operator ]
 )
changedALTER AGGREGATE 
--- 8.0
+++ 8.1
@@ -1,2 +1,3 @@
-ALTER AGGREGATE name ( type ) RENAME TO newname
-ALTER AGGREGATE name ( type ) OWNER TO newowner
+ALTER AGGREGATE name ( type ) RENAME TO new_name
+ALTER AGGREGATE name ( type ) OWNER TO new_owner
+ALTER AGGREGATE name ( type ) SET SCHEMA new_schema
changedCREATE FUNCTION 
--- 8.0
+++ 8.1
@@ -1,5 +1,6 @@
-CREATE [ OR REPLACE ] FUNCTION name ( [ [ argname ] argtype [, ...] ] )
-RETURNS rettype
+CREATE [ OR REPLACE ] FUNCTION
+name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
+[ RETURNS rettype ]
 { LANGUAGE langname
 | IMMUTABLE | STABLE | VOLATILE
 | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
changedALTER FUNCTION 
--- 8.0
+++ 8.1
@@ -1,2 +1,14 @@
-ALTER FUNCTION name ( [ type [, ...] ] ) RENAME TO newname
-ALTER FUNCTION name ( [ type [, ...] ] ) OWNER TO newowner
+ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
+action [, ... ] [ RESTRICT ]
+ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
+RENAME TO new_name
+ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
+OWNER TO new_owner
+ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
+SET SCHEMA new_schema
+
+where action is one of:
+
+CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
+IMMUTABLE | STABLE | VOLATILE
+[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
changedDROP FUNCTION 
--- 8.0
+++ 8.1
@@ -1 +1,2 @@
-DROP FUNCTION name ( [ type [, ...] ] ) [ CASCADE | RESTRICT ]
+DROP FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
+[ CASCADE | RESTRICT ]
changedCREATE LANGUAGE 
--- 8.0
+++ 8.1
@@ -1,2 +1,3 @@
+CREATE [ PROCEDURAL ] LANGUAGE name
 CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
 HANDLER call_handler [ VALIDATOR valfunction ]
changedALTER DOMAIN 
--- 8.0
+++ 8.1
@@ -8,3 +8,5 @@
 DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
 ALTER DOMAIN name
 OWNER TO new_owner
+ALTER DOMAIN name
+SET SCHEMA new_schema
changedALTER TYPE 
--- 8.0
+++ 8.1
@@ -1 +1,2 @@
 ALTER TYPE name OWNER TO new_owner
+ALTER TYPE name SET SCHEMA new_schema
changedCREATE DATABASE 
--- 8.0
+++ 8.1
@@ -2,4 +2,5 @@
 [ [ WITH ] [ OWNER [=] dbowner ]
 [ TEMPLATE [=] template ]
 [ ENCODING [=] encoding ]
-[ TABLESPACE [=] tablespace ] ]
+[ TABLESPACE [=] tablespace ]
+[ CONNECTION LIMIT [=] connlimit ] ]
changedALTER DATABASE 
--- 8.0
+++ 8.1
@@ -1,3 +1,9 @@
+ALTER DATABASE name [ [ WITH ] option [ ... ] ]
+
+where option can be:
+
+CONNECTION LIMIT connlimit
+
 ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT }
 ALTER DATABASE name RESET parameter
changedGRANT 
--- 8.0
+++ 8.1
@@ -8,7 +8,7 @@
 TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
 GRANT { EXECUTE | ALL [ PRIVILEGES ] }
-ON FUNCTION funcname ([type, ...]) [, ...]
+ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
 TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
 GRANT { USAGE | ALL [ PRIVILEGES ] }
@@ -22,3 +22,5 @@
 GRANT { CREATE | ALL [ PRIVILEGES ] }
 ON TABLESPACE tablespacename [, ...]
 TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+
+GRANT role [, ...] TO username [, ...] [ WITH ADMIN OPTION ]
changedREVOKE 
--- 8.0
+++ 8.1
@@ -13,7 +13,7 @@
 
 REVOKE [ GRANT OPTION FOR ]
 { EXECUTE | ALL [ PRIVILEGES ] }
-ON FUNCTION funcname ([type, ...]) [, ...]
+ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
 FROM { username | GROUP groupname | PUBLIC } [, ...]
 [ CASCADE | RESTRICT ]
 
@@ -34,3 +34,7 @@
 ON TABLESPACE tablespacename [, ...]
 FROM { username | GROUP groupname | PUBLIC } [, ...]
 [ CASCADE | RESTRICT ]
+
+REVOKE [ ADMIN OPTION FOR ]
+role [, ...] FROM username [, ...]
+[ CASCADE | RESTRICT ]
changedCREATE GROUP 
--- 8.0
+++ 8.1
@@ -2,5 +2,17 @@
 
 where option can be:
 
-SYSID gid
-| USER username [, ...]
+SUPERUSER | NOSUPERUSER
+| CREATEDB | NOCREATEDB
+| CREATEROLE | NOCREATEROLE
+| CREATEUSER | NOCREATEUSER
+| INHERIT | NOINHERIT
+| LOGIN | NOLOGIN
+| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
+| VALID UNTIL 'timestamp'
+| IN ROLE rolename [, ...]
+| IN GROUP rolename [, ...]
+| ROLE rolename [, ...]
+| ADMIN rolename [, ...]
+| USER rolename [, ...]
+| SYSID uid
changedDROP GROUP 
--- 8.0
+++ 8.1
@@ -1 +1 @@
-DROP GROUP name
+DROP GROUP name [, ...]
addedCREATE ROLE 
--- 8.0
+++ 8.1
@@ -0,0 +1,19 @@
+CREATE ROLE name [ [ WITH ] option [ ... ] ]
+
+where option can be:
+
+SUPERUSER | NOSUPERUSER
+| CREATEDB | NOCREATEDB
+| CREATEROLE | NOCREATEROLE
+| CREATEUSER | NOCREATEUSER
+| INHERIT | NOINHERIT
+| LOGIN | NOLOGIN
+| CONNECTION LIMIT connlimit
+| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
+| VALID UNTIL 'timestamp'
+| IN ROLE rolename [, ...]
+| IN GROUP rolename [, ...]
+| ROLE rolename [, ...]
+| ADMIN rolename [, ...]
+| USER rolename [, ...]
+| SYSID uid
addedALTER ROLE 
--- 8.0
+++ 8.1
@@ -0,0 +1,18 @@
+ALTER ROLE name [ [ WITH ] option [ ... ] ]
+
+where option can be:
+
+SUPERUSER | NOSUPERUSER
+| CREATEDB | NOCREATEDB
+| CREATEROLE | NOCREATEROLE
+| CREATEUSER | NOCREATEUSER
+| INHERIT | NOINHERIT
+| LOGIN | NOLOGIN
+| CONNECTION LIMIT connlimit
+| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
+| VALID UNTIL 'timestamp'
+
+ALTER ROLE name RENAME TO newname
+
+ALTER ROLE name SET parameter { TO | = } { value | DEFAULT }
+ALTER ROLE name RESET parameter
addedDROP ROLE 
--- 8.0
+++ 8.1
@@ -0,0 +1 @@
+DROP ROLE name [, ...]
addedSET ROLE 
--- 8.0
+++ 8.1
@@ -0,0 +1,3 @@
+SET [ SESSION | LOCAL ] ROLE rolename
+SET [ SESSION | LOCAL ] ROLE NONE
+RESET ROLE
changedCREATE USER 
--- 8.0
+++ 8.1
@@ -2,9 +2,18 @@
 
 where option can be:
 
-SYSID uid
+SUPERUSER | NOSUPERUSER
 | CREATEDB | NOCREATEDB
+| CREATEROLE | NOCREATEROLE
 | CREATEUSER | NOCREATEUSER
-| IN GROUP groupname [, ...]
+| INHERIT | NOINHERIT
+| LOGIN | NOLOGIN
+| CONNECTION LIMIT connlimit
 | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
-| VALID UNTIL 'abstime'
+| VALID UNTIL 'timestamp'
+| IN ROLE rolename [, ...]
+| IN GROUP rolename [, ...]
+| ROLE rolename [, ...]
+| ADMIN rolename [, ...]
+| USER rolename [, ...]
+| SYSID uid
changedALTER USER 
--- 8.0
+++ 8.1
@@ -2,10 +2,15 @@
 
 where option can be:
 
-CREATEDB | NOCREATEDB
+SUPERUSER | NOSUPERUSER
+| CREATEDB | NOCREATEDB
+| CREATEROLE | NOCREATEROLE
 | CREATEUSER | NOCREATEUSER
+| INHERIT | NOINHERIT
+| LOGIN | NOLOGIN
+| CONNECTION LIMIT connlimit
 | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
-| VALID UNTIL 'abstime'
+| VALID UNTIL 'timestamp'
 
 ALTER USER name RENAME TO newname
changedDROP USER 
--- 8.0
+++ 8.1
@@ -1 +1 @@
-DROP USER name
+DROP USER name [, ...]
changedCOPY 
--- 8.0
+++ 8.1
@@ -5,7 +5,8 @@
 [ OIDS ]
 [ DELIMITER [ AS ] 'delimiter' ]
 [ NULL [ AS ] 'null string' ]
-[ CSV [ QUOTE [ AS ] 'quote' ]
+[ CSV [ HEADER ]
+[ QUOTE [ AS ] 'quote' ]
 [ ESCAPE [ AS ] 'escape' ]
 [ FORCE NOT NULL column [, ...] ]
 
@@ -16,6 +17,7 @@
 [ OIDS ]
 [ DELIMITER [ AS ] 'delimiter' ]
 [ NULL [ AS ] 'null string' ]
-[ CSV [ QUOTE [ AS ] 'quote' ]
+[ CSV [ HEADER ]
+[ QUOTE [ AS ] 'quote' ]
 [ ESCAPE [ AS ] 'escape' ]
 [ FORCE QUOTE column [, ...] ]
changedDELETE 
--- 8.0
+++ 8.1
@@ -1 +1,3 @@
-DELETE FROM [ ONLY ] table [ WHERE condition ]
+DELETE FROM [ ONLY ] table
+[ USING usinglist ]
+[ WHERE condition ]
changedSELECT 
--- 8.0
+++ 8.1
@@ -8,7 +8,7 @@
 [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
 [ LIMIT { count | ALL } ]
 [ OFFSET start ]
-[ FOR UPDATE [ OF table_name [, ...] ] ]
+[ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] ]
 
 where from_item can be one of:
changedSELECT INTO 
--- 8.0
+++ 8.1
@@ -9,4 +9,4 @@
 [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
 [ LIMIT { count | ALL } ]
 [ OFFSET start ]
-[ FOR UPDATE [ OF tablename [, ...] ] ]
+[ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] ]
addedCOMMIT PREPARED 
--- 8.0
+++ 8.1
@@ -0,0 +1 @@
+COMMIT PREPARED transaction_id
addedROLLBACK PREPARED 
--- 8.0
+++ 8.1
@@ -0,0 +1 @@
+ROLLBACK PREPARED transaction_id
addedPREPARE TRANSACTION 
--- 8.0
+++ 8.1
@@ -0,0 +1 @@
+PREPARE TRANSACTION transaction_id
changedCOMMENT 
--- 8.0
+++ 8.1
@@ -8,7 +8,7 @@
 CONVERSION object_name |
 DATABASE object_name |
 DOMAIN object_name |
-FUNCTION func_name (arg1_type, arg2_type, ...) |
+FUNCTION func_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) |
 INDEX object_name |
 LARGE OBJECT large_object_oid |
 OPERATOR op (leftoperand_type, rightoperand_type) |