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

Browse PG 8.3

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

17 added · 0 removed · 28 changed · 0 coverage changes. These are changes between the pinned reference snapshots.

changedALTER SEQUENCE 
--- 8.2
+++ 8.3
@@ -2,4 +2,5 @@
 [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
 [ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
 [ OWNED BY { table.column | NONE } ]
+ALTER SEQUENCE name RENAME TO new_name
 ALTER SEQUENCE name SET SCHEMA new_schema
changedCREATE TABLE 
--- 8.2
+++ 8.3
@@ -1,7 +1,7 @@
 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 | CONSTRAINTS } ] ... }
+| LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES } ] ... }
 [, ... ]
 ] )
 [ INHERITS ( parent_table [, ... ] ) ]
changedALTER TABLE 
--- 8.2
+++ 8.3
@@ -21,6 +21,12 @@
 DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
 DISABLE TRIGGER [ trigger_name | ALL | USER ]
 ENABLE TRIGGER [ trigger_name | ALL | USER ]
+ENABLE REPLICA TRIGGER trigger_name
+ENABLE ALWAYS TRIGGER trigger_name
+DISABLE RULE rewrite_rule_name
+ENABLE RULE rewrite_rule_name
+ENABLE REPLICA RULE rewrite_rule_name
+ENABLE ALWAYS RULE rewrite_rule_name
 CLUSTER ON index_name
 SET WITHOUT CLUSTER
 SET WITHOUT OIDS
addedALTER VIEW 
--- 8.2
+++ 8.3
@@ -0,0 +1 @@
+ALTER VIEW name RENAME TO newname
changedCREATE INDEX 
--- 8.2
+++ 8.3
@@ -1,5 +1,5 @@
 CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
-( { column | ( expression ) } [ opclass ] [, ...] )
+( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
 [ WITH ( storage_parameter = value [, ... ] ) ]
 [ TABLESPACE tablespace ]
 [ WHERE predicate ]
changedCREATE FUNCTION 
--- 8.2
+++ 8.3
@@ -5,6 +5,9 @@
 | IMMUTABLE | STABLE | VOLATILE
 | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
 | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
+| COST execution_cost
+| ROWS result_rows
+| SET configuration_parameter { TO value | = value | FROM CURRENT }
 | AS 'definition'
 | AS 'obj_file', 'link_symbol'
 } ...
changedALTER FUNCTION 
--- 8.2
+++ 8.3
@@ -1,5 +1,5 @@
 ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
-action [, ... ] [ RESTRICT ]
+action [ ... ] [ RESTRICT ]
 ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
 RENAME TO new_name
 ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
@@ -12,3 +12,9 @@
 CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
 IMMUTABLE | STABLE | VOLATILE
 [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
+COST execution_cost
+ROWS result_rows
+SET configuration_parameter { TO | = } { value | DEFAULT }
+SET configuration_parameter FROM CURRENT
+RESET configuration_parameter
+RESET ALL
changedALTER LANGUAGE 
--- 8.2
+++ 8.3
@@ -1 +1,2 @@
-ALTER LANGUAGE name RENAME TO newname
+ALTER [ PROCEDURAL ] LANGUAGE name RENAME TO newname
+ALTER [ PROCEDURAL ] LANGUAGE name OWNER TO new_owner
changedDROP DOMAIN 
--- 8.2
+++ 8.3
@@ -1 +1 @@
-DROP DOMAIN [IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
+DROP DOMAIN [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
changedCREATE OPERATOR 
--- 8.2
+++ 8.3
@@ -4,6 +4,4 @@
 [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
 [, RESTRICT = res_proc ] [, JOIN = join_proc ]
 [, HASHES ] [, MERGES ]
-[, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ]
-[, LTCMP = less_than_op ] [, GTCMP = greater_than_op ]
 )
changedCREATE OPERATOR CLASS 
--- 8.2
+++ 8.3
@@ -1,5 +1,6 @@
-CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type USING index_method AS
+CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type
+USING index_method [ FAMILY family_name ] AS
 { OPERATOR strategy_number operator_name [ ( op_type, op_type ) ] [ RECHECK ]
-| FUNCTION support_number funcname ( argument_type [, ...] )
+| FUNCTION support_number [ ( op_type [ , op_type ] ) ] funcname ( argument_type [, ...] )
 | STORAGE storage_type
 } [, ... ]
addedCREATE OPERATOR FAMILY 
--- 8.2
+++ 8.3
@@ -0,0 +1 @@
+CREATE OPERATOR FAMILY name USING index_method
addedALTER OPERATOR FAMILY 
--- 8.2
+++ 8.3
@@ -0,0 +1,10 @@
+ALTER OPERATOR FAMILY name USING index_method ADD
+{ OPERATOR strategy_number operator_name ( op_type, op_type ) [ RECHECK ]
+| FUNCTION support_number [ ( op_type [ , op_type ] ) ] funcname ( argument_type [, ...] )
+} [, ... ]
+ALTER OPERATOR FAMILY name USING index_method DROP
+{ OPERATOR strategy_number ( op_type [ , op_type ] )
+| FUNCTION support_number ( op_type [ , op_type ] )
+} [, ... ]
+ALTER OPERATOR FAMILY name USING index_method RENAME TO newname
+ALTER OPERATOR FAMILY name USING index_method OWNER TO newowner
addedDROP OPERATOR FAMILY 
--- 8.2
+++ 8.3
@@ -0,0 +1 @@
+DROP OPERATOR FAMILY [ IF EXISTS ] name USING index_method [ CASCADE | RESTRICT ]
changedCREATE TYPE 
--- 8.2
+++ 8.3
@@ -1,11 +1,16 @@
 CREATE TYPE name AS
 ( attribute_name data_type [, ... ] )
+
+CREATE TYPE name AS ENUM
+( 'label' [, ... ] )
 
 CREATE TYPE name (
 INPUT = input_function,
 OUTPUT = output_function
 [ , RECEIVE = receive_function ]
 [ , SEND = send_function ]
+[ , TYPMOD_IN = type_modifier_input_function ]
+[ , TYPMOD_OUT = type_modifier_output_function ]
 [ , ANALYZE = analyze_function ]
 [ , INTERNALLENGTH = { internallength | VARIABLE } ]
 [ , PASSEDBYVALUE ]
changedALTER DATABASE 
--- 8.2
+++ 8.3
@@ -4,9 +4,11 @@
 
 CONNECTION LIMIT connlimit
 
-ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT }
-ALTER DATABASE name RESET parameter
-
 ALTER DATABASE name RENAME TO newname
 
 ALTER DATABASE name OWNER TO new_owner
+
+ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
+ALTER DATABASE name SET configuration_parameter FROM CURRENT
+ALTER DATABASE name RESET configuration_parameter
+ALTER DATABASE name RESET ALL
changedGRANT 
--- 8.2
+++ 8.3
@@ -1,31 +1,31 @@
 GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER }
 [,...] | ALL [ PRIVILEGES ] }
 ON [ TABLE ] tablename [, ...]
-TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
 GRANT { { USAGE | SELECT | UPDATE }
 [,...] | ALL [ PRIVILEGES ] }
 ON SEQUENCE sequencename [, ...]
-TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
 GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
 ON DATABASE dbname [, ...]
-TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
 GRANT { EXECUTE | ALL [ PRIVILEGES ] }
 ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
-TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
 GRANT { USAGE | ALL [ PRIVILEGES ] }
 ON LANGUAGE langname [, ...]
-TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
 GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
 ON SCHEMA schemaname [, ...]
-TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
 GRANT { CREATE | ALL [ PRIVILEGES ] }
 ON TABLESPACE tablespacename [, ...]
-TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
-GRANT role [, ...] TO username [, ...] [ WITH ADMIN OPTION ]
+GRANT role [, ...] TO rolename [, ...] [ WITH ADMIN OPTION ]
changedREVOKE 
--- 8.2
+++ 8.3
@@ -2,46 +2,46 @@
 { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER }
 [,...] | ALL [ PRIVILEGES ] }
 ON [ TABLE ] tablename [, ...]
-FROM { username | GROUP groupname | PUBLIC } [, ...]
+FROM { [ GROUP ] rolename | PUBLIC } [, ...]
 [ CASCADE | RESTRICT ]
 
 REVOKE [ GRANT OPTION FOR ]
 { { USAGE | SELECT | UPDATE }
 [,...] | ALL [ PRIVILEGES ] }
 ON SEQUENCE sequencename [, ...]
-FROM { username | GROUP groupname | PUBLIC } [, ...]
+FROM { [ GROUP ] rolename | PUBLIC } [, ...]
 [ CASCADE | RESTRICT ]
 
 REVOKE [ GRANT OPTION FOR ]
 { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
 ON DATABASE dbname [, ...]
-FROM { username | GROUP groupname | PUBLIC } [, ...]
+FROM { [ GROUP ] rolename | PUBLIC } [, ...]
 [ CASCADE | RESTRICT ]
 
 REVOKE [ GRANT OPTION FOR ]
 { EXECUTE | ALL [ PRIVILEGES ] }
 ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
-FROM { username | GROUP groupname | PUBLIC } [, ...]
+FROM { [ GROUP ] rolename | PUBLIC } [, ...]
 [ CASCADE | RESTRICT ]
 
 REVOKE [ GRANT OPTION FOR ]
 { USAGE | ALL [ PRIVILEGES ] }
 ON LANGUAGE langname [, ...]
-FROM { username | GROUP groupname | PUBLIC } [, ...]
+FROM { [ GROUP ] rolename | PUBLIC } [, ...]
 [ CASCADE | RESTRICT ]
 
 REVOKE [ GRANT OPTION FOR ]
 { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
 ON SCHEMA schemaname [, ...]
-FROM { username | GROUP groupname | PUBLIC } [, ...]
+FROM { [ GROUP ] rolename | PUBLIC } [, ...]
 [ CASCADE | RESTRICT ]
 
 REVOKE [ GRANT OPTION FOR ]
 { CREATE | ALL [ PRIVILEGES ] }
 ON TABLESPACE tablespacename [, ...]
-FROM { username | GROUP groupname | PUBLIC } [, ...]
+FROM { [ GROUP ] rolename | PUBLIC } [, ...]
 [ CASCADE | RESTRICT ]
 
 REVOKE [ ADMIN OPTION FOR ]
-role [, ...] FROM username [, ...]
+role [, ...] FROM rolename [, ...]
 [ CASCADE | RESTRICT ]
changedALTER ROLE 
--- 8.2
+++ 8.3
@@ -15,4 +15,6 @@
 ALTER ROLE name RENAME TO newname
 
 ALTER ROLE name SET configuration_parameter { TO | = } { value | DEFAULT }
+ALTER ROLE name SET configuration_parameter FROM CURRENT
 ALTER ROLE name RESET configuration_parameter
+ALTER ROLE name RESET ALL
changedALTER USER 
--- 8.2
+++ 8.3
@@ -15,4 +15,6 @@
 ALTER USER name RENAME TO newname
 
 ALTER USER name SET configuration_parameter { TO | = } { value | DEFAULT }
+ALTER USER name SET configuration_parameter FROM CURRENT
 ALTER USER name RESET configuration_parameter
+ALTER USER name RESET ALL
addedCREATE TEXT SEARCH CONFIGURATION 
--- 8.2
+++ 8.3
@@ -0,0 +1,4 @@
+CREATE TEXT SEARCH CONFIGURATION name (
+PARSER = parser_name |
+COPY = source_config
+)
addedALTER TEXT SEARCH CONFIGURATION 
--- 8.2
+++ 8.3
@@ -0,0 +1,12 @@
+ALTER TEXT SEARCH CONFIGURATION name
+ADD MAPPING FOR token_type [, ... ] WITH dictionary_name [, ... ]
+ALTER TEXT SEARCH CONFIGURATION name
+ALTER MAPPING FOR token_type [, ... ] WITH dictionary_name [, ... ]
+ALTER TEXT SEARCH CONFIGURATION name
+ALTER MAPPING REPLACE old_dictionary WITH new_dictionary
+ALTER TEXT SEARCH CONFIGURATION name
+ALTER MAPPING FOR token_type [, ... ] REPLACE old_dictionary WITH new_dictionary
+ALTER TEXT SEARCH CONFIGURATION name
+DROP MAPPING [ IF EXISTS ] FOR token_type [, ... ]
+ALTER TEXT SEARCH CONFIGURATION name RENAME TO newname
+ALTER TEXT SEARCH CONFIGURATION name OWNER TO newowner
addedDROP TEXT SEARCH CONFIGURATION 
--- 8.2
+++ 8.3
@@ -0,0 +1 @@
+DROP TEXT SEARCH CONFIGURATION [ IF EXISTS ] name [ CASCADE | RESTRICT ]
addedCREATE TEXT SEARCH DICTIONARY 
--- 8.2
+++ 8.3
@@ -0,0 +1,4 @@
+CREATE TEXT SEARCH DICTIONARY name (
+TEMPLATE = template
+[, option = value [, ... ]]
+)
addedALTER TEXT SEARCH DICTIONARY 
--- 8.2
+++ 8.3
@@ -0,0 +1,5 @@
+ALTER TEXT SEARCH DICTIONARY name (
+option [ = value ] [, ... ]
+)
+ALTER TEXT SEARCH DICTIONARY name RENAME TO newname
+ALTER TEXT SEARCH DICTIONARY name OWNER TO newowner
addedDROP TEXT SEARCH DICTIONARY 
--- 8.2
+++ 8.3
@@ -0,0 +1 @@
+DROP TEXT SEARCH DICTIONARY [ IF EXISTS ] name [ CASCADE | RESTRICT ]
addedCREATE TEXT SEARCH PARSER 
--- 8.2
+++ 8.3
@@ -0,0 +1,7 @@
+CREATE TEXT SEARCH PARSER name (
+START = start_function ,
+GETTOKEN = gettoken_function ,
+END = end_function ,
+LEXTYPES = lextypes_function
+[, HEADLINE = headline_function ]
+)
addedALTER TEXT SEARCH PARSER 
--- 8.2
+++ 8.3
@@ -0,0 +1 @@
+ALTER TEXT SEARCH PARSER name RENAME TO newname
addedDROP TEXT SEARCH PARSER 
--- 8.2
+++ 8.3
@@ -0,0 +1 @@
+DROP TEXT SEARCH PARSER [ IF EXISTS ] name [ CASCADE | RESTRICT ]
addedCREATE TEXT SEARCH TEMPLATE 
--- 8.2
+++ 8.3
@@ -0,0 +1,4 @@
+CREATE TEXT SEARCH TEMPLATE name (
+[ INIT = init_function , ]
+LEXIZE = lexize_function
+)
addedALTER TEXT SEARCH TEMPLATE 
--- 8.2
+++ 8.3
@@ -0,0 +1 @@
+ALTER TEXT SEARCH TEMPLATE name RENAME TO newname
addedDROP TEXT SEARCH TEMPLATE 
--- 8.2
+++ 8.3
@@ -0,0 +1 @@
+DROP TEXT SEARCH TEMPLATE [ IF EXISTS ] name [ CASCADE | RESTRICT ]
changedDELETE 
--- 8.2
+++ 8.3
@@ -1,4 +1,4 @@
-DELETE FROM [ ONLY ] table [ [ AS ] alias ]
+DELETE FROM [ ONLY ] table [ * ] [ [ AS ] alias ]
 [ USING usinglist ]
-[ WHERE condition ]
+[ WHERE condition | WHERE CURRENT OF cursor_name ]
 [ RETURNING * | output_expression [ AS output_name ] [, ...] ]
changedSELECT 
--- 8.2
+++ 8.3
@@ -5,7 +5,7 @@
 [ GROUP BY expression [, ...] ]
 [ HAVING condition [, ...] ]
 [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
-[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
+[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
 [ LIMIT { count | ALL } ]
 [ OFFSET start ]
 [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]
changedUPDATE 
--- 8.2
+++ 8.3
@@ -1,6 +1,6 @@
-UPDATE [ ONLY ] table [ [ AS ] alias ]
+UPDATE [ ONLY ] table [ * ] [ [ AS ] alias ]
 SET { column = { expression | DEFAULT } |
 ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
 [ FROM fromlist ]
-[ WHERE condition ]
+[ WHERE condition | WHERE CURRENT OF cursor_name ]
 [ RETURNING * | output_expression [ AS output_name ] [, ...] ]
changedSELECT INTO 
--- 8.2
+++ 8.3
@@ -6,7 +6,7 @@
 [ GROUP BY expression [, ...] ]
 [ HAVING condition [, ...] ]
 [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
-[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
+[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
 [ LIMIT { count | ALL } ]
 [ OFFSET start ]
 [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]
changedCLOSE 
--- 8.2
+++ 8.3
@@ -1 +1 @@
-CLOSE name
+CLOSE { name | ALL }
changedDEALLOCATE 
--- 8.2
+++ 8.3
@@ -1 +1 @@
-DEALLOCATE [ PREPARE ] name
+DEALLOCATE [ PREPARE ] { name | ALL }
changedDECLARE 
--- 8.2
+++ 8.3
@@ -1,3 +1,2 @@
 DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
 CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
-[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]
changedEXECUTE 
--- 8.2
+++ 8.3
@@ -1 +1 @@
-EXECUTE name [ (parameter [, ...] ) ]
+EXECUTE name [ ( parameter [, ...] ) ]
changedPREPARE 
--- 8.2
+++ 8.3
@@ -1 +1 @@
-PREPARE name [ (datatype [, ...] ) ] AS statement
+PREPARE name [ ( datatype [, ...] ) ] AS statement
addedDISCARD 
--- 8.2
+++ 8.3
@@ -0,0 +1 @@
+DISCARD { ALL | PLANS | TEMPORARY | TEMP }
changedANALYZE 
--- 8.2
+++ 8.3
@@ -1 +1 @@
-ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]
+ANALYZE [ VERBOSE ] [ table [ ( column [, ...] ) ] ]
changedCLUSTER 
--- 8.2
+++ 8.3
@@ -1,3 +1,2 @@
-CLUSTER indexname ON tablename
-CLUSTER tablename
+CLUSTER tablename [ USING indexname ]
 CLUSTER
changedCOMMENT 
--- 8.2
+++ 8.3
@@ -13,12 +13,17 @@
 LARGE OBJECT large_object_oid |
 OPERATOR op (leftoperand_type, rightoperand_type) |
 OPERATOR CLASS object_name USING index_method |
+OPERATOR FAMILY object_name USING index_method |
 [ PROCEDURAL ] LANGUAGE object_name |
 ROLE object_name |
 RULE rule_name ON table_name |
 SCHEMA object_name |
 SEQUENCE object_name |
 TABLESPACE object_name |
+TEXT SEARCH CONFIGURATION object_name |
+TEXT SEARCH DICTIONARY object_name |
+TEXT SEARCH PARSER object_name |
+TEXT SEARCH TEMPLATE object_name |
 TRIGGER trigger_name ON table_name |
 TYPE object_name |
 VIEW object_name