Prev   Next

Appendix C. Supported PostgreSQL DDL Syntax

The following table documents what a model in Theon can natively represent in the way of derivable SQL DDL commands, those that would appear in a pg_dump -s. So basically just CREATE statements (even though the generated DDL may also use ALTER and DROP statements as part of an installation or upgrade process as pg_dump does to make changes and avoid some dependency ordering issues).

If a physical database only uses the supported statements below in the output of a pg_dump -s then it can be easily represented in Theon. If not then it cannot be completely represented and one or more different approaches need to be taken to represent the unsupported aspects. How viable this will be depends on how much is unsupported and how static the unsupported aspects are likely to be over time. For example, you may have a database that used fully supported statements except that for performance reasons some indexes were defined as particular types which were not supported. There are two main approaches to representing these indexes. One would still be to define the index in the model in Theon but override the automatically generated DDL SQL statement for the index by entering the actual statement to use in the entity container. Alternatively the indexes could be held in a separately maintained file of DDL statements outside of Theon and applied as a separate performance step in any installation or upgrade of the database.

In the Supported Syntax column below the supported aspects of the statement are highlighted in bold. Any syntax not highlighted or not included is not supported.

Statement

Supported

Supported Syntax

CREATE ACCESS METHOD

No

N/A

CREATE AGGREGATE

No

N/A

CREATE CAST

No

N/A

CREATE COLLATION

No

N/A

CREATE CONVERSION

No

N/A

CREATE DATABASE

Yes

CREATE DATABASE name [ [ WITH ] [ OWNER [=] user_name ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ LC_COLLATE [=] lc_collate ] [ LC_CTYPE [=] lc_ctype ] [ TABLESPACE [=] tablespace_name ] [ CONNECTION LIMIT [=] connlimit ] ]

CREATE DOMAIN

Yes

CREATE DOMAIN name [ AS ] data_type [ COLLATE collation ] [ DEFAULT expression ] [ constraint [ … ] ] where constraint is: [ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK (expression) }

CREATE EVENT TRIGGER

No

N/A

CREATE EXTENSION

Yes

CREATE EXTENSION [ IF NOT EXISTS ] extension_name [ WITH ] [ SCHEMA schema_name ] [ VERSION version ] [ FROM old_version ]

CREATE FOREIGN DATA WRAPPER

No

N/A

CREATE FOREIGN TABLE

No

N/A

CREATE FUNCTION

Yes

CREATE [ OR REPLACE ] FUNCTION name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, …] ] ) [ RETURNS rettype | RETURNS TABLE ( column_name column_type [, …] ) ] { LANGUAGE lang_name | WINDOW | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF | 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 } … [ WITH ( attribute [, …] ) ]

Only SQL and PG/PGSQL functions are supported.

CREATE GROUP

No

N/A

CREATE INDEX

Yes

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table_name [ USING method ] ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, …] ) [ WITH ( storage_parameter = value [, … ] ) ] [ TABLESPACE tablespace_name ] [ WHERE predicate ]

CREATE LANGUAGE

No

N/A

CREATE MATERIALIZED VIEW

No

N/A

CREATE OPERATOR

No

N/A

CREATE OPERATOR CLASS

No

N/A

CREATE OPERATOR FAMILY

No

N/A

CREATE POLICY

No

N/A

CREATE ROLE

Yes

CREATE ROLE name [ [ WITH ] option [ … ] ]

CREATE RULE

No

N/A

CREATE SCHEMA

No

N/A

CREATE SEQUENCE

Yes

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] [ OWNED BY { table_name.column_name | NONE } ]

CREATE SERVER

No

N/A

CREATE TABLE

Yes

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [ { column_name data_type [ COLLATE collation ] [ column_constraint [ … ] ] | table_constraint | LIKE source_table [ like_option … ] } [, … ] ] ) [ INHERITS ( parent_table [, … ] ) ] [ WITH ( storage_parameter [= value] [, … ] ) | WITH OIDS | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace_name ]

where column_constraint is:

[ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK ( expression ) [ NO INHERIT ] | DEFAULT default_expr | UNIQUE index_parameters | PRIMARY KEY index_parameters | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ]* [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint is:

[ CONSTRAINT constraint_name ] { CHECK ( expression ) [ NO INHERIT ] | UNIQUE ( column_name [, … ] ) index_parameters | PRIMARY KEY ( column_name [, … ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, … ] ) index_parameters [ WHERE ( predicate ) ] | FOREIGN KEY ( column_name [, … ] ) REFERENCES reftable [ ( refcolumn [, … ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Supported data_type’s are:

boolean; character varying [ (n) ]; date; double precision; integer; interval [ fields ] [ (p) ]; numeric [ (p, s) ]; serial; text; time [ (p) ]; timestamp [ (p) ]

Any other data_type (including custom types) can be indirectly supported if necessary, see docs.

CREATE TABLE AS

No

N/A

CREATE TABLESPACE

No

N/A

CREATE TEXT SEARCH CONFIGURATION

No

N/A

CREATE TEXT SEARCH DICTIONARY

No

N/A

CREATE TEXT SEARCH PARSER

No

N/A

CREATE TEXT SEARCH TEMPLATE

No

N/A

CREATE TRANSFORM

No

N/A

CREATE TRIGGER

Yes

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR … ] } ON table_name [ FROM referenced_table_name ] { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } } [ FOR [ EACH ] { ROW | STATEMENT } ] [ WHEN ( condition ) ] EXECUTE PROCEDURE function_name ( arguments )

CREATE TYPE

Yes

CREATE TYPE name AS ( [ attribute_name data_type [ COLLATE collation ] [, … ] ] )

CREATE TYPE name AS ENUM ( [ label [, … ] ] )

CREATE USER

No

N/A

CREATE USER MAPPING

No

N/A

CREATE VIEW

Yes

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, …] ) ] [ WITH ( view_option_name [= view_option_value] [, … ] ) ] AS query

GRANT

Yes

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, …] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, …] | ALL TABLES IN SCHEMA schema_name [, …] } TO { [ GROUP ] role_name | PUBLIC } [, …] [ WITH GRANT OPTION ]

For upgrades only "additive" operations in SQL DDL commands are supported. So this includes all of the supported CREATE and GRANT commands above plus the ALTER … ADD commands in the table below. No "deletive" operations, using the commands DROP, ALTER … DROP, REVOKE or any modifications are currently supported for upgrades.

In the Supported Syntax column below the supported aspects of the statement are highlighted in bold. Any syntax not highlighted or not included is not supported.

Statement

Supported

Supported Syntax

ALTER AGGREGATE

No

N/A

ALTER COLLATION

No

N/A

ALTER CONVERSION

No

N/A

ALTER DATABASE

No

N/A

ALTER DEFAULT PRIVILEGES

No

N/A

ALTER DOMAIN

No

N/A

ALTER EVENT TRIGGER

No

N/A

ALTER EXTENSION

No

N/A

ALTER FOREIGN DATA WRAPPER

No

N/A

ALTER FOREIGN TABLE

No

N/A

ALTER FUNCTION

No

N/A

ALTER GROUP

No

N/A

ALTER INDEX

No

N/A

ALTER LANGUAGE

No

N/A

ALTER LARGE OBJECT

No

N/A

ALTER MATERIALIZED VIEW

No

N/A

ALTER OPERATOR

No

N/A

ALTER OPERATOR CLASS

No

N/A

ALTER OPERATOR FAMILY

No

N/A

ALTER POLICY

No

N/A

ALTER ROLE

No

N/A

ALTER RULE

No

N/A

ALTER SCHEMA

No

N/A

ALTER SEQUENCE

No

N/A

ALTER SERVER

No

N/A

ALTER SYSTEM

No

N/A

ALTER TABLE

Yes

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ] action [, … ] ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ] RENAME [ COLUMN ] column_name TO new_column_name ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ] RENAME CONSTRAINT constraint_name TO new_constraint_name ALTER TABLE [ IF EXISTS ] name RENAME TO new_name ALTER TABLE [ IF EXISTS ] name SET SCHEMA new_schema ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, … ] ] SET TABLESPACE new_tablespace [ NOWAIT ]

where action is one of:

ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ … ] ] DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ] ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ] ALTER [ COLUMN ] column_name SET DEFAULT expression ALTER [ COLUMN ] column_name DROP DEFAULT ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL ALTER [ COLUMN ] column_name SET STATISTICS integer ALTER [ COLUMN ] column_name SET ( attribute_option = value [, … ] ) ALTER [ COLUMN ] column_name RESET ( attribute_option [, … ] ) ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } ADD table_constraint [ NOT VALID ] ADD table_constraint_using_index ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] VALIDATE CONSTRAINT constraint_name DROP CONSTRAINT [ IF EXISTS ] 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 DISABLE ROW LEVEL SECURITY ENABLE ROW LEVEL SECURITY FORCE ROW LEVEL SECURITY NO FORCE ROW LEVEL SECURITY CLUSTER ON index_name SET WITHOUT CLUSTER SET WITH OIDS SET WITHOUT OIDS SET TABLESPACE new_tablespace SET { LOGGED | UNLOGGED } SET ( storage_parameter = value [, … ] ) RESET ( storage_parameter [, … ] ) INHERIT parent_table NO INHERIT parent_table OF type_name NOT OF OWNER TO { new_owner | CURRENT_USER | SESSION_USER } REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }

and column_constraint is: supported syntax as defined under CREATE TABLE above.

and table_constraint is: supported syntax as defined under CREATE TABLE above.

and table_constraint_using_index is:

[ CONSTRAINT constraint_name ] { UNIQUE | PRIMARY KEY } USING INDEX index_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

ALTER TABLESPACE

No

N/A

ALTER TEXT SEARCH CONFIGURATION

No

N/A

ALTER TEXT SEARCH DICTIONARY

No

N/A

ALTER TEXT SEARCH PARSER

No

N/A

ALTER TEXT SEARCH TEMPLATE

No

N/A

ALTER TRIGGER

No

N/A

ALTER TYPE

Yes

ALTER TYPE name action [, … ]

where action is one of:

ADD ATTRIBUTE attribute_name data_type [ COLLATE collation ] [ CASCADE

RESTRICT ] DROP ATTRIBUTE [ IF EXISTS ] attribute_name [ CASCADE

RESTRICT ] ALTER ATTRIBUTE attribute_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ CASCADE

RESTRICT ]

ALTER TYPE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER TYPE name RENAME ATTRIBUTE attribute_name TO new_attribute_name [ CASCADE | RESTRICT ] ALTER TYPE name RENAME TO new_name ALTER TYPE name SET SCHEMA new_schema ALTER TYPE name ADD VALUE [ IF NOT EXISTS ] new_enum_value [ { BEFORE | AFTER } existing_enum_value ]

ALTER USER

No

N/A

ALTER USER MAPPING

No

N/A

ALTER VIEW

No

N/A

Prev   Next
 Home