DDL

• CREATE TABLE
  • [GLOBAL | LOCAL] {TEMPORARY | TEMP}
  • UNLOGGED
  • IF NOT EXISTS
  • OF type_name
  • PARTITION OF parent_table
  • column definitions:
    • STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }
    • COMPRESSION { pglz | lz4 | default}
    • COLLATE collation
    • column_name [WITH OPTIONS] (syntax sugar which has no effect)
  • column constraints:
    • CONSTRAINT name
    • NULL | NOT NULL
    • CHECK ( expression )
    • DEFAULT expr (unlike SQLite and BigQuery which only support literals in here)
    • GENERATED ALWAYS AS ( expr ) STORED
    • GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
    • GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY ( sequence_options ) see CREATE SEQUENCE
    • UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters
    • PRIMARY KEY index_parameters
    • REFERENCES ...
    • NO INHERIT
    • DEFERRABLE | NOT DEFERRABLE
    • INITIALLY DEFERRED | INITIALLY IMMEDIATE
  • table constraints:
    • CONSTRAINT name
    • CHECK ( expression )
    • UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters
    • PRIMARY KEY ( column_name [, ... ] ) index_parameters
    • EXCLUDE
      • [ USING index_method ]
      • ( expr WITH operator [, ... ] )
      • ( expr [opclass] [ASC|DESC] [NULLS {FIRST|LAST}] WITH operator, ... )
      • index_parameters
      • [ WHERE ( predicate ) ]
    • FOREIGN KEY ...
    • NO INHERIT
    • DEFERRABLE | NOT DEFERRABLE
    • INITIALLY DEFERRED | INITIALLY IMMEDIATE
  • references definition:
    • REFERENCES tbl_name [(key_part,...)]
    • ... [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
    • ... [ON DELETE referencial_action]
    • ... [ON UPDATE referencial_action]
    • referential_action:
      • RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
      • SET NULL ( col1, col2, ...)
      • SET DEFAULT ( col1, col2, ...)
  • index_parameters:
    • INCLUDE ( column_name [, ... ] )
    • WITH ( storage_parameter [= value] [, ... ] )
    • USING INDEX TABLESPACE tablespace_name
  • CREATE TABLE .. ( LIKE source_table [like_option] ) unlike with other dialects, the LIKE clause is inside columns list.
    • like options: { INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
  • INHERITS (table , ...)
  • FOR VALUES partition_bound_spec | DEFAULT
    • IN ( expr [, ...] )
    • FROM ( { expr | MINVALUE | MAXVALUE } [, ...] ) TO ( { expr | MINVALUE | MAXVALUE } [, ...] )
    • WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )
  • PARTITION BY { RANGE | LIST | HASH } ( part_param, ... ) with part_param consisting of:
    • column_name | ( expr )
    • [COLLATE collation]
    • [opclass]
  • USING method
  • WITH ( storage_parameter [= value] [, ... ] )
  • WITHOUT OIDS
  • ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP }
  • TABLESPACE tablespace_name
  • CREATE TABLE AS
    • (column_name, ...)
    • AS query
    • WITH [NO] DATA
  • CREATE FOREIGN TABLE
    • OPTIONS ( option 'value', ... )
      • as column constraint
      • as create table clause
    • SERVER server_name