Skip to main content

Table definitions

The CREATE TABLE statement conforms to the full SQL standard. Tables are assigned to the current schema unless the schema name is explicitly given as part of the table name. Table names should be unique amongst those mentioned within the same schema and distinct from view names.

table_def:
     CREATE TABLE [ IF NOT EXISTS ] table_name  table_content_source [ STORAGE ident string ]
   | CREATE [ LOCAL | GLOBAL ] TEMPORARY TABLE [ IF NOT EXISTS ] table_name  table_content_source
                     [ ON COMMIT [ DELETE ROWS |  PRESERVE ROWS |  DROP ] ]
   | CREATE TABLE [ IF NOT EXISTS ] table_name FROM LOADER function_ref
   | CREATE MERGE TABLE [ IF NOT EXISTS ] table_name  table_content_source
   | CREATE REPLICA TABLE [ IF NOT EXISTS ] table_name  table_content_source
   | CREATE REMOTE TABLE [ IF NOT EXISTS ] table_name  table_content_source  ON
                     'mapi:monetdb://host:port/dbname' [ WITH [ USER 'username' ] [ [ ENCRYPTED ] PASSWORD 'password'] ]
   | CREATE STREAM TABLE [ IF NOT EXISTS ] table_name  table_content_source

table_content_source:
   '(' table_element_list ')'
  |  [ '(' column_name_list ')' ] AS select_query   [ WITH DATA | WITH NO DATA ]

table_element_list:
   table_element
  |  table_element_list , table_element

The derived (temporary) tables are either filled upon creation or automatically upon use within queries.

Temporary tables are stored automatically under the schema 'tmp'. Temporary local tables are limited to the client session. The qualifiers denote the actions taken during transaction commit over a temporary table. If the ON COMMIT clause is omitted then all tuples are dropped while retaining the structure. In most cases you would use: ON COMMIT PRESERVE ROWS

For using Loader functions we support the MonetDB specific CREATE table FROM LOADER syntax.

For merging partitioned table data we support the MonetDB specific CREATE MERGE table syntax.

For replicating table data we support the MonetDB specific CREATE REPLICA table syntax.

For distributed query processing we support the MonetDB specific CREATE REMOTE table syntax.

For data stream processing we support the MonetDB specific CREATE STREAM table syntax.