SQL Commands / Version comparison
What changed?
SQL Commands · PostgreSQL 8.3 → 8.4
Historical documentation for a PostgreSQL version that is no longer supported.
9 added · 0 removed · 23 changed · 0 coverage changes. These are changes between the pinned reference snapshots.
changedTRUNCATE
--- 8.3 +++ 8.4 @@ -1 +1,2 @@ -TRUNCATE [ TABLE ] name [, ...] [ CASCADE | RESTRICT ] +TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ] +[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
changedALTER SEQUENCE
--- 8.3
+++ 8.4
@@ -1,6 +1,9 @@
ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
-[ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
+[ START [ WITH ] start ]
+[ RESTART [ [ WITH ] restart ] ]
+[ CACHE cache ] [ [ NO ] CYCLE ]
[ OWNED BY { table.column | NONE } ]
+ALTER SEQUENCE name OWNER TO new_owner
ALTER SEQUENCE name RENAME TO new_name
ALTER SEQUENCE name SET SCHEMA new_schema
changedALTER TABLE
--- 8.3
+++ 8.4
@@ -11,7 +11,7 @@
ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
-ALTER [ COLUMN ] column TYPE type [ USING expression ]
+ALTER [ COLUMN ] column [ SET DATA ] TYPE type [ USING expression ]
ALTER [ COLUMN ] column SET DEFAULT expression
ALTER [ COLUMN ] column DROP DEFAULT
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
@@ -29,6 +29,7 @@
ENABLE ALWAYS RULE rewrite_rule_name
CLUSTER ON index_name
SET WITHOUT CLUSTER
+SET WITH OIDS
SET WITHOUT OIDS
SET ( storage_parameter = value [, ... ] )
RESET ( storage_parameter [, ... ] )
changedCREATE TABLE AS
--- 8.3
+++ 8.4
@@ -4,3 +4,4 @@
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]
AS query
+[ WITH [ NO ] DATA ]
changedALTER VIEW
--- 8.3 +++ 8.4 @@ -1 +1,5 @@ -ALTER VIEW name RENAME TO newname +ALTER VIEW name ALTER [ COLUMN ] column SET DEFAULT expression +ALTER VIEW name ALTER [ COLUMN ] column DROP DEFAULT +ALTER VIEW name OWNER TO new_owner +ALTER VIEW name RENAME TO new_name +ALTER VIEW name SET SCHEMA new_schema
changedCREATE FUNCTION
--- 8.3
+++ 8.4
@@ -1,7 +1,9 @@
CREATE [ OR REPLACE ] FUNCTION
-name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
-[ RETURNS rettype ]
+name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } defexpr ] [, ...] ] )
+[ RETURNS rettype
+| RETURNS TABLE ( colname coltype [, ...] ) ]
{ LANGUAGE langname
+| WINDOW
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
changedCREATE CAST
--- 8.3 +++ 8.4 @@ -5,3 +5,7 @@ CREATE CAST (sourcetype AS targettype) WITHOUT FUNCTION [ AS ASSIGNMENT | AS IMPLICIT ] + +CREATE CAST (sourcetype AS targettype) +WITH INOUT +[ AS ASSIGNMENT | AS IMPLICIT ]
changedCREATE OPERATOR CLASS
--- 8.3
+++ 8.4
@@ -1,6 +1,6 @@
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 ]
+{ OPERATOR strategy_number operator_name [ ( op_type, op_type ) ]
| FUNCTION support_number [ ( op_type [ , op_type ] ) ] funcname ( argument_type [, ...] )
| STORAGE storage_type
} [, ... ]
changedALTER OPERATOR FAMILY
--- 8.3
+++ 8.4
@@ -1,5 +1,5 @@
ALTER OPERATOR FAMILY name USING index_method ADD
-{ OPERATOR strategy_number operator_name ( op_type, op_type ) [ RECHECK ]
+{ OPERATOR strategy_number operator_name ( op_type, op_type )
| FUNCTION support_number [ ( op_type [ , op_type ] ) ] funcname ( argument_type [, ...] )
} [, ... ]
ALTER OPERATOR FAMILY name USING index_method DROP
changedCREATE TYPE
--- 8.3 +++ 8.4 @@ -16,6 +16,9 @@ [ , PASSEDBYVALUE ] [ , ALIGNMENT = alignment ] [ , STORAGE = storage ] +[ , LIKE = like_type ] +[ , CATEGORY = category ] +[ , PREFERRED = preferred ] [ , DEFAULT = default ] [ , ELEMENT = element ] [ , DELIMITER = delimiter ]
changedALTER TYPE
--- 8.3 +++ 8.4 @@ -1,2 +1,3 @@ +ALTER TYPE name RENAME TO new_name ALTER TYPE name OWNER TO new_owner ALTER TYPE name SET SCHEMA new_schema
changedCREATE DATABASE
--- 8.3 +++ 8.4 @@ -2,5 +2,7 @@ [ [ WITH ] [ OWNER [=] dbowner ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] +[ LC_COLLATE [=] lc_collate ] +[ LC_CTYPE [=] lc_ctype ] [ TABLESPACE [=] tablespace ] [ CONNECTION LIMIT [=] connlimit ] ]
changedALTER DATABASE
--- 8.3
+++ 8.4
@@ -8,6 +8,8 @@
ALTER DATABASE name OWNER TO new_owner
+ALTER DATABASE name SET TABLESPACE new_tablespace
+
ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
changedGRANT
--- 8.3
+++ 8.4
@@ -1,5 +1,10 @@
-GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER }
+GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
+ON [ TABLE ] tablename [, ...]
+TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+
+GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
+[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
ON [ TABLE ] tablename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
@@ -10,6 +15,14 @@
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE dbname [, ...]
+TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+
+GRANT { USAGE | ALL [ PRIVILEGES ] }
+ON FOREIGN DATA WRAPPER fdwname [, ...]
+TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+
+GRANT { USAGE | ALL [ PRIVILEGES ] }
+ON FOREIGN SERVER servername [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
changedREVOKE
--- 8.3
+++ 8.4
@@ -1,6 +1,13 @@
REVOKE [ GRANT OPTION FOR ]
-{ { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER }
+{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
+ON [ TABLE ] tablename [, ...]
+FROM { [ GROUP ] rolename | PUBLIC } [, ...]
+[ CASCADE | RESTRICT ]
+
+REVOKE [ GRANT OPTION FOR ]
+{ { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
+[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
ON [ TABLE ] tablename [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
@@ -15,6 +22,18 @@
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE dbname [, ...]
+FROM { [ GROUP ] rolename | PUBLIC } [, ...]
+[ CASCADE | RESTRICT ]
+
+REVOKE [ GRANT OPTION FOR ]
+{ USAGE | ALL [ PRIVILEGES ] }
+ON FOREIGN DATA WRAPPER fdwname [, ...]
+FROM { [ GROUP ] rolename | PUBLIC } [, ...]
+[ CASCADE | RESTRICT ]
+
+REVOKE [ GRANT OPTION FOR ]
+{ USAGE | ALL [ PRIVILEGES ] }
+ON FOREIGN SERVER servername [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
addedCREATE FOREIGN DATA WRAPPER
--- 8.3 +++ 8.4 @@ -0,0 +1,3 @@ +CREATE FOREIGN DATA WRAPPER name +[ VALIDATOR valfunction | NO VALIDATOR ] +[ OPTIONS ( option 'value' [, ... ] ) ]
addedALTER FOREIGN DATA WRAPPER
--- 8.3 +++ 8.4 @@ -0,0 +1,4 @@ +ALTER FOREIGN DATA WRAPPER name +[ VALIDATOR valfunction | NO VALIDATOR ] +[ OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ]) ] +ALTER FOREIGN DATA WRAPPER name OWNER TO new_owner
addedDROP FOREIGN DATA WRAPPER
--- 8.3 +++ 8.4 @@ -0,0 +1 @@ +DROP FOREIGN DATA WRAPPER [ IF EXISTS ] name [ CASCADE | RESTRICT ]
addedCREATE SERVER
--- 8.3 +++ 8.4 @@ -0,0 +1,3 @@ +CREATE SERVER servername [ TYPE 'servertype' ] [ VERSION 'serverversion' ] +FOREIGN DATA WRAPPER fdwname +[ OPTIONS ( option 'value' [, ... ] ) ]
addedALTER SERVER
--- 8.3 +++ 8.4 @@ -0,0 +1,3 @@ +ALTER SERVER servername [ VERSION 'newversion' ] +[ OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] ) ] +ALTER SERVER servername OWNER TO new_owner
addedDROP SERVER
--- 8.3 +++ 8.4 @@ -0,0 +1 @@ +DROP SERVER [ IF EXISTS ] servername [ CASCADE | RESTRICT ]
addedCREATE USER MAPPING
--- 8.3
+++ 8.4
@@ -0,0 +1,3 @@
+CREATE USER MAPPING FOR { username | USER | CURRENT_USER | PUBLIC }
+SERVER servername
+[ OPTIONS ( option 'value' [ , ... ] ) ]
addedALTER USER MAPPING
--- 8.3
+++ 8.4
@@ -0,0 +1,3 @@
+ALTER USER MAPPING FOR { username | USER | CURRENT_USER | PUBLIC }
+SERVER servername
+OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] )
addedDROP USER MAPPING
--- 8.3
+++ 8.4
@@ -0,0 +1 @@
+DROP USER MAPPING [ IF EXISTS ] FOR { username | USER | CURRENT_USER | PUBLIC } SERVER servername
changedDELETE
--- 8.3 +++ 8.4 @@ -1,4 +1,4 @@ DELETE FROM [ ONLY ] table [ * ] [ [ AS ] alias ] [ USING usinglist ] [ WHERE condition | WHERE CURRENT OF cursor_name ] -[ RETURNING * | output_expression [ AS output_name ] [, ...] ] +[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
changedINSERT
--- 8.3
+++ 8.4
@@ -1,3 +1,3 @@
INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
-[ RETURNING * | output_expression [ AS output_name ] [, ...] ]
+[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
changedLOCK
--- 8.3 +++ 8.4 @@ -1,4 +1,4 @@ -LOCK [ TABLE ] name [, ...] [ IN lockmode MODE ] [ NOWAIT ] +LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ] where lockmode is one of:
changedSELECT
--- 8.3
+++ 8.4
@@ -1,19 +1,29 @@
+[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
-* | expression [ AS output_name ] [, ...]
+* | expression [ [ AS ] output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
+[ WINDOW window_name AS ( window_definition ) [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
-[ OFFSET start ]
+[ OFFSET start [ ROW | ROWS ] ]
+[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]
where from_item can be one of:
[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
+with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]
+
+and with_query is:
+
+with_query_name [ ( column_name [, ...] ) ] AS ( select )
+
+TABLE { [ ONLY ] table_name [ * ] | with_query_name }
changedUPDATE
--- 8.3
+++ 8.4
@@ -3,4 +3,4 @@
( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
[ FROM fromlist ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
-[ RETURNING * | output_expression [ AS output_name ] [, ...] ]
+[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
changedVALUES
--- 8.3
+++ 8.4
@@ -1,4 +1,5 @@
VALUES ( expression [, ...] ) [, ...]
[ ORDER BY sort_expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
-[ OFFSET start ]
+[ OFFSET start [ ROW | ROWS ] ]
+[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
changedSELECT INTO
--- 8.3
+++ 8.4
@@ -1,12 +1,15 @@
+[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
-* | expression [ AS output_name ] [, ...]
+* | expression [ [ AS ] output_name ] [, ...]
INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
+[ WINDOW window_name AS ( window_definition ) [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
-[ OFFSET start ]
+[ OFFSET start [ ROW | ROWS ] ]
+[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]
changedCLUSTER
--- 8.3 +++ 8.4 @@ -1,2 +1,2 @@ -CLUSTER tablename [ USING indexname ] -CLUSTER +CLUSTER [VERBOSE] tablename [ USING indexname ] +CLUSTER [VERBOSE]