Schema

Database objects are identified by schema and name. Name check is case-insensitive, so for example table myTable vs MyTable is considered as the same table.

All objects are built by reading object properties from different system metadata tables and views. Functions, stored procedures and table constraints also use pg_get_functiondef/pg_get_constraintdef functions to retrieve object definitions. Application supports the most commonly used object attributes. However in many cases it is only the subset of PostgreSQL specifications, and the application does not support some PostgreSQL language features. Such features are not recognized and if an object has changes in any non-supported attribute then such change is ignored. In case of object merge these attributes can be lost in the target database. Below you will find the information about supported/unsupported features.

Schemas

Here is the presentation of supported/unsupported schema features, based on PostgreSQL CREATE SCHEMA specification. Unsupported ones are shown as ~~crossed out~~:

CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ]

Table definitions

Here is the high-level presentation of supported/unsupported table definition attributes, based on PostgreSQL CREATE TABLE specification. Unsupported ones are shown as ~~crossed out~~:

CREATE [ UNLOGGED ] TABLE table_name ( [
  { column_name data_type [ STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT } ] [ COMPRESSION compression_method ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

CREATE .. TABLE table_name
  OF type_name ..
  
CREATE .. TABLE table_name
  PARTITION OF parent_table ..
    
column_constraint:
  [ CONSTRAINT constraint_name ]
  { NOT NULL |
    NULL |
    DEFAULT default_expr |
    GENERATED ALWAYS AS ( generation_expr ) STORED |
    GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] }
  [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Note that column_constraint excludes CHECK, UNIQUE, PRIMARY KEY and REFERENCES constraints because they are handled as table_constraint. Table-level constraints are fully supported, including constraint names and deferrability. Constraint defninition are retrieved using pg_get_constraintdef system function.

Views

Here is the presentation of supported/unsupported view features, based on PostgreSQL CREATE VIEW specification. Unsupported ones are shown as ~~crossed out~~:

CREATE [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

Sequences

Here is the presentation of supported/unsupported sequence features, based on PostgreSQL CREATE SEQUENCE specification. Unsupported ones are shown as ~~crossed out~~:

CREATE [ UNLOGGED ] SEQUENCE name
    [ AS data_type ]
    [ 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 } ]

Last updated: 2023-10-06