↑↓ 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.2 → 7.3

Browse PG 7.3

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

16 added · 0 removed · 32 changed · 0 coverage changes. These are changes between the pinned reference snapshots.

changedDROP SEQUENCE 
--- 7.2
+++ 7.3
@@ -1 +1,2 @@
-DROP SEQUENCE name [, ...]
+DROP SEQUENCE name [, ...] [ CASCADE | RESTRICT ]
+
changedALTER TABLE 
--- 7.2
+++ 7.3
@@ -1,16 +1,22 @@
 ALTER TABLE [ ONLY ] table [ * ]
 ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
 ALTER TABLE [ ONLY ] table [ * ]
+DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
+ALTER TABLE [ ONLY ] table [ * ]
 ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT }
+ALTER TABLE [ ONLY ] table [ * ]
+ALTER [ COLUMN ] column { SET | DROP } NOT NULL
 ALTER TABLE [ ONLY ] table [ * ]
 ALTER [ COLUMN ] column SET STATISTICS integer
 ALTER TABLE [ ONLY ] table [ * ]
-RENAME [ COLUMN ] column TO newcolumn
+ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
+ALTER TABLE [ ONLY ] table [ * ]
+RENAME [ COLUMN ] column TO new_column
 ALTER TABLE table
 RENAME TO new_table
-ALTER TABLE table
-ADD table_constraint_definition
-ALTER TABLE [ ONLY ] table
-DROP CONSTRAINT constraint { RESTRICT | CASCADE }
+ALTER TABLE [ ONLY ] table [ * ]
+ADD table_constraint
+ALTER TABLE [ ONLY ] table [ * ]
+DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
 ALTER TABLE table
 OWNER TO new_owner
changedDROP TABLE 
--- 7.2
+++ 7.3
@@ -1 +1,2 @@
-DROP TABLE name [, ...]
+DROP TABLE name [, ...] [ CASCADE | RESTRICT ]
+
changedCREATE VIEW 
--- 7.2
+++ 7.3
@@ -1 +1 @@
-CREATE VIEW view [ ( column name list ) ] AS SELECT query
+CREATE [ OR REPLACE ] VIEW view [ ( column name list ) ] AS SELECT query
changedDROP VIEW 
--- 7.2
+++ 7.3
@@ -1 +1 @@
-DROP VIEW name [, ...]
+DROP VIEW name [, ...] [ CASCADE | RESTRICT ]
changedDROP INDEX 
--- 7.2
+++ 7.3
@@ -1 +1 @@
-DROP INDEX index_name [, ...]
+DROP INDEX index_name [, ...] [ CASCADE | RESTRICT ]
changedDROP AGGREGATE 
--- 7.2
+++ 7.3
@@ -1 +1 @@
-DROP AGGREGATE name ( type )
+DROP AGGREGATE name ( type ) [ CASCADE | RESTRICT ]
changedCREATE FUNCTION 
--- 7.2
+++ 7.3
@@ -1,10 +1,10 @@
 CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] )
 RETURNS rettype
-AS 'definition'
-LANGUAGE langname
+{ LANGUAGE langname
+| IMMUTABLE | STABLE | VOLATILE
+| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
+| [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER
+| AS 'definition'
+| AS 'obj_file', 'link_symbol'
+} ...
 [ WITH ( attribute [, ...] ) ]
-CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] )
-RETURNS rettype
-AS 'obj_file', 'link_symbol'
-LANGUAGE langname
-[ WITH ( attribute [, ...] ) ]
changedDROP FUNCTION 
--- 7.2
+++ 7.3
@@ -1 +1 @@
-DROP FUNCTION name ( [ type [, ...] ] )
+DROP FUNCTION name ( [ type [, ...] ] ) [ CASCADE | RESTRICT ]
changedCREATE LANGUAGE 
--- 7.2
+++ 7.3
@@ -1,2 +1,2 @@
 CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE langname
-HANDLER call_handler
+HANDLER call_handler [ VALIDATOR valfunction ]
changedDROP LANGUAGE 
--- 7.2
+++ 7.3
@@ -1 +1 @@
-DROP [ PROCEDURAL ] LANGUAGE name
+DROP [ PROCEDURAL ] LANGUAGE name [ CASCADE | RESTRICT ]
addedCREATE CAST 
--- 7.2
+++ 7.3
@@ -0,0 +1,7 @@
+CREATE CAST (sourcetype AS targettype)
+WITH FUNCTION funcname (argtype)
+[ AS ASSIGNMENT | AS IMPLICIT ]
+
+CREATE CAST (sourcetype AS targettype)
+WITHOUT FUNCTION
+[ AS ASSIGNMENT | AS IMPLICIT ]
addedDROP CAST 
--- 7.2
+++ 7.3
@@ -0,0 +1,2 @@
+DROP CAST (sourcetype AS targettype)
+[ CASCADE | RESTRICT ]
addedCREATE CONVERSION 
--- 7.2
+++ 7.3
@@ -0,0 +1,2 @@
+CREATE [DEFAULT] CONVERSION conversion_name
+FOR source_encoding TO dest_encoding FROM funcname
addedDROP CONVERSION 
--- 7.2
+++ 7.3
@@ -0,0 +1,2 @@
+DROP CONVERSION conversion_name
+[ CASCADE | RESTRICT ]
addedCREATE DOMAIN 
--- 7.2
+++ 7.3
@@ -0,0 +1,8 @@
+CREATE DOMAIN domainname [AS] data_type
+[ DEFAULT default_expr ]
+[ constraint [ ... ] ]
+
+where constraint is:
+
+[ CONSTRAINT constraint_name ]
+{ NOT NULL | NULL }
addedDROP DOMAIN 
--- 7.2
+++ 7.3
@@ -0,0 +1 @@
+DROP DOMAIN domainname [, ...] [ CASCADE | RESTRICT ]
changedCREATE OPERATOR 
--- 7.2
+++ 7.3
@@ -3,4 +3,6 @@
 ] [, RIGHTARG = righttype ]
 [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
 [, RESTRICT = res_proc ] [, JOIN = join_proc ]
-[, HASHES ] [, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ] )
+[, HASHES ] [, MERGES ]
+[, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ]
+[, LTCMP = less_than_op ] [, GTCMP = greater_than_op ] )
changedDROP OPERATOR 
--- 7.2
+++ 7.3
@@ -1 +1 @@
-DROP OPERATOR id ( lefttype | NONE , righttype | NONE )
+DROP OPERATOR id ( lefttype | NONE , righttype | NONE ) [ CASCADE | RESTRICT ]
addedCREATE OPERATOR CLASS 
--- 7.2
+++ 7.3
@@ -0,0 +1,5 @@
+CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type USING access_method AS
+{ OPERATOR strategy_number operator_id [ ( type, type ) ] [ RECHECK ]
+| FUNCTION support_number func_name ( parameter_types )
+| STORAGE storage_type
+} [, ... ]
addedDROP OPERATOR CLASS 
--- 7.2
+++ 7.3
@@ -0,0 +1 @@
+DROP OPERATOR CLASS name USING access_method [ CASCADE | RESTRICT ]
changedCREATE TYPE 
--- 7.2
+++ 7.3
@@ -1,10 +1,11 @@
 CREATE TYPE typename ( INPUT = input_function, OUTPUT = output_function
 , INTERNALLENGTH = { internallength | VARIABLE }
-[ , EXTERNALLENGTH = { externallength | VARIABLE } ]
 [ , DEFAULT = default ]
 [ , ELEMENT = element ] [ , DELIMITER = delimiter ]
-[ , SEND = send_function ] [ , RECEIVE = receive_function ]
 [ , PASSEDBYVALUE ]
 [ , ALIGNMENT = alignment ]
 [ , STORAGE = storage ]
 )
+
+CREATE TYPE typename AS
+( column_name data_type [, ... ] )
changedDROP TYPE 
--- 7.2
+++ 7.3
@@ -1 +1,2 @@
-DROP TYPE typename [, ...]
+DROP TYPE typename [, ...] [ CASCADE | RESTRICT ]
+
changedCREATE DATABASE 
--- 7.2
+++ 7.3
@@ -1,4 +1,5 @@
 CREATE DATABASE name
-[ WITH [ LOCATION = 'dbpath' ]
-[ TEMPLATE = template ]
-[ ENCODING = encoding ] ]
+[ [ WITH ] [ OWNER [=] dbowner ]
+[ LOCATION [=] 'dbpath' ]
+[ TEMPLATE [=] template ]
+[ ENCODING [=] encoding ] ]
addedALTER DATABASE 
--- 7.2
+++ 7.3
@@ -0,0 +1,2 @@
+ALTER DATABASE name SET variable { TO | = } { value | DEFAULT }
+ALTER DATABASE name RESET variable
addedCREATE SCHEMA 
--- 7.2
+++ 7.3
@@ -0,0 +1,2 @@
+CREATE SCHEMA schemaname [ AUTHORIZATION username ] [ schema_element [ ... ] ]
+CREATE SCHEMA AUTHORIZATION username [ schema_element [ ... ] ]
addedDROP SCHEMA 
--- 7.2
+++ 7.3
@@ -0,0 +1,2 @@
+DROP SCHEMA name [, ...] [ CASCADE | RESTRICT ]
+
changedGRANT 
--- 7.2
+++ 7.3
@@ -1,3 +1,20 @@
-GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] }
-ON [ TABLE ] objectname [, ...]
+GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
+[,...] | ALL [ PRIVILEGES ] }
+ON [ TABLE ] tablename [, ...]
 TO { username | GROUP groupname | PUBLIC } [, ...]
+
+GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
+ON DATABASE dbname [, ...]
+TO { username | GROUP groupname | PUBLIC } [, ...]
+
+GRANT { EXECUTE | ALL [ PRIVILEGES ] }
+ON FUNCTION funcname ([type, ...]) [, ...]
+TO { username | GROUP groupname | PUBLIC } [, ...]
+
+GRANT { USAGE | ALL [ PRIVILEGES ] }
+ON LANGUAGE langname [, ...]
+TO { username | GROUP groupname | PUBLIC } [, ...]
+
+GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
+ON SCHEMA schemaname [, ...]
+TO { username | GROUP groupname | PUBLIC } [, ...]
changedREVOKE 
--- 7.2
+++ 7.3
@@ -1,3 +1,20 @@
-REVOKE { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] }
-ON [ TABLE ] object [, ...]
+REVOKE { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
+[,...] | ALL [ PRIVILEGES ] }
+ON [ TABLE ] tablename [, ...]
 FROM { username | GROUP groupname | PUBLIC } [, ...]
+
+REVOKE { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
+ON DATABASE dbname [, ...]
+FROM { username | GROUP groupname | PUBLIC } [, ...]
+
+REVOKE { EXECUTE | ALL [ PRIVILEGES ] }
+ON FUNCTION funcname ([type, ...]) [, ...]
+FROM { username | GROUP groupname | PUBLIC } [, ...]
+
+REVOKE { USAGE | ALL [ PRIVILEGES ] }
+ON LANGUAGE langname [, ...]
+FROM { username | GROUP groupname | PUBLIC } [, ...]
+
+REVOKE { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
+ON SCHEMA schemaname [, ...]
+FROM { username | GROUP groupname | PUBLIC } [, ...]
changedSET SESSION AUTHORIZATION 
--- 7.2
+++ 7.3
@@ -1 +1,3 @@
-SET SESSION AUTHORIZATION 'username'
+SET [ SESSION | LOCAL ] SESSION AUTHORIZATION username
+SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT
+RESET SESSION AUTHORIZATION
changedCREATE USER 

  

  
  

    changedALTER USER
    
--- 7.2
+++ 7.3
@@ -6,3 +6,6 @@
 | CREATEDB | NOCREATEDB
 | CREATEUSER | NOCREATEUSER
 | VALID UNTIL 'abstime'
+
+ALTER USER username SET variable { TO | = } { value | DEFAULT }
+ALTER USER username RESET variable

  

  
  

    changedCREATE RULE
    
--- 7.2
+++ 7.3
@@ -1,13 +1,9 @@
-CREATE RULE name AS ON event
-TO object [ WHERE condition ]
+CREATE [ OR REPLACE ] RULE name AS ON event
+TO table [ WHERE condition ]
 DO [ INSTEAD ] action
 
 where action can be:
 
 NOTHING
-|
-query
-|
-( query ; query ... )
-|
-[ query ; query ... ]
+| query
+| ( query ; query ... )

  

  
  

    changedDROP RULE
    
--- 7.2
+++ 7.3
@@ -1 +1 @@
-DROP RULE name [, ...]
+DROP RULE name ON relation [ CASCADE | RESTRICT ]

  

  
  

    addedALTER TRIGGER
    
--- 7.2
+++ 7.3
@@ -0,0 +1,2 @@
+ALTER TRIGGER trigger ON table
+RENAME TO newname

  

  
  

    changedDROP TRIGGER
    
--- 7.2
+++ 7.3
@@ -1 +1 @@
-DROP TRIGGER name ON table
+DROP TRIGGER name ON table [ CASCADE | RESTRICT ]

  

  
  

    changedCOPY
    
--- 7.2
+++ 7.3
@@ -1,8 +1,14 @@
-COPY [ BINARY ] table [ WITH OIDS ]
+COPY table [ ( column [, ...] ) ]
 FROM { 'filename' | stdin }
-[ [USING] DELIMITERS 'delimiter' ]
-[ WITH NULL AS 'null string' ]
-COPY [ BINARY ] table [ WITH OIDS ]
+[ [ WITH ]
+[ BINARY ]
+[ OIDS ]
+[ DELIMITER [ AS ] 'delimiter' ]
+[ NULL [ AS ] 'null string' ] ]
+COPY table [ ( column [, ...] ) ]
 TO { 'filename' | stdout }
-[ [USING] DELIMITERS 'delimiter' ]
-[ WITH NULL AS 'null string' ]
+[ [ WITH ]
+[ BINARY ]
+[ OIDS ]
+[ DELIMITER [ AS ] 'delimiter' ]
+[ NULL [ AS ] 'null string' ] ]

  

  
  

    changedINSERT
    
--- 7.2
+++ 7.3
@@ -1,2 +1,2 @@
 INSERT INTO table [ ( column [, ...] ) ]
-{ DEFAULT VALUES | VALUES ( expression [, ...] ) | SELECT query }
+{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | SELECT query }

  

  
  

    changedSELECT
    
--- 7.2
+++ 7.3
@@ -6,9 +6,9 @@
 [ HAVING condition [, ...] ]
 [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
 [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
-[ FOR UPDATE [ OF tablename [, ...] ] ]
 [ LIMIT { count | ALL } ]
 [ OFFSET start ]
+[ FOR UPDATE [ OF tablename [, ...] ] ]
 
 where from_item can be:
 
@@ -18,5 +18,11 @@
 ( select )
 [ AS ] alias [ ( column_alias_list ) ]
 |
+table_function_name ( [ argument [, ...] ] )
+[ AS ] alias [ ( column_alias_list | column_definition_list ) ]
+|
+table_function_name ( [ argument [, ...] ] )
+AS ( column_definition_list )
+|
 from_item [ NATURAL ] join_type from_item
 [ ON join_condition | USING ( join_column_list ) ]

  

  
  

    changedSELECT INTO
    
--- 7.2
+++ 7.3
@@ -7,17 +7,6 @@
 [ HAVING condition [, ...] ]
 [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
 [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
+[ LIMIT { count | ALL } ]
+[ OFFSET start ]
 [ FOR UPDATE [ OF tablename [, ...] ] ]
-[ LIMIT [ start , ] { count | ALL } ]
-[ OFFSET start ]
-
-where from_item can be:
-
-[ ONLY ] table_name [ * ]
-[ [ AS ] alias [ ( column_alias_list ) ] ]
-|
-( select )
-[ AS ] alias [ ( column_alias_list ) ]
-|
-from_item [ NATURAL ] join_type from_item
-[ ON join_condition | USING ( join_column_list ) ]

  

  
  

    addedDEALLOCATE
    
--- 7.2
+++ 7.3
@@ -0,0 +1 @@
+DEALLOCATE [ PREPARE ] plan_name

  

  
  

    addedEXECUTE
    
--- 7.2
+++ 7.3
@@ -0,0 +1 @@
+EXECUTE plan_name [ (parameter [, ...] ) ]

  

  
  

    changedFETCH
    
--- 7.2
+++ 7.3
@@ -1,5 +1,6 @@
 FETCH [ direction ] [ count ] { IN | FROM } cursor
-FETCH [ FORWARD | BACKWARD | RELATIVE ] [ # | ALL | NEXT | PRIOR ] { IN | FROM } cursor
+FETCH [ FORWARD | BACKWARD | RELATIVE ] [ # | ALL | NEXT | PRIOR ]
+{ IN | FROM } cursor
 
 
 FETCH RELATIVE 0 FROM cursor.

  

  
  

    addedPREPARE
    
--- 7.2
+++ 7.3
@@ -0,0 +1 @@
+PREPARE plan_name [ (datatype [, ...] ) ] AS query

  

  
  

    changedBEGIN
    
--- 7.2
+++ 7.3
@@ -1,2 +1 @@
-
 BEGIN [ WORK | TRANSACTION ]

  

  
  

    addedSTART TRANSACTION
    
--- 7.2
+++ 7.3
@@ -0,0 +1 @@
+START TRANSACTION [ ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE } ]

  

  
  

    changedSET
    
--- 7.2
+++ 7.3
@@ -1,2 +1,2 @@
-SET variable { TO | = } { value | 'value' | DEFAULT }
-SET TIME ZONE { 'timezone' | LOCAL | DEFAULT }
+SET [ SESSION | LOCAL ] variable { TO | = } { value | 'value' | DEFAULT }
+SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }

  

  
  

    changedCOMMENT
    
--- 7.2
+++ 7.3
@@ -1,9 +1,18 @@
 COMMENT ON
 [
-[ DATABASE | INDEX | RULE | SEQUENCE | TABLE | TYPE | VIEW ] object_name |
+TABLE 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
+CONSTRAINT constraint_name ON table_name |
+DATABASE object_name |
+DOMAIN object_name |
+FUNCTION func_name (arg1_type, arg2_type, ...) |
+INDEX object_name |
+OPERATOR op (leftoperand_type, rightoperand_type) |
+RULE rule_name ON table_name |
+SCHEMA object_name |
+SEQUENCE object_name |
+TRIGGER trigger_name ON table_name |
+TYPE object_name |
+VIEW object_name
 ] IS 'text'