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.

Besides the standard SQL CREATE TABLE, CREATE LOCAL TEMPORARY TABLE and CREATE GLOBAL TEMPORARY TABLE statements, MonetDB introduces new table types: MERGE TABLE, REPLICA TABLE, REMOTE TABLE and STREAM TABLE for specific usages.

table_def:
     CREATE TABLE [ IF NOT EXISTS ] table_name  table_content_source [ STORAGE ident string ]
   | CREATE TABLE [ IF NOT EXISTS ] table_name FROM LOADER function_ref
   | CREATE [ LOCAL | GLOBAL ] TEMPORARY TABLE [ IF NOT EXISTS ] table_name  table_content_source
                     [ ON COMMIT { DELETE ROWS  |  PRESERVE ROWS  |  DROP } ]
   | 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.

Tip: to find out which user created tables are defined in your database run query:
  SELECT * FROM sys.tables WHERE type IN (SELECT table_type_id FROM sys.table_types
           WHERE table_type_name LIKE '%TABLE' AND table_type_name <> 'SYSTEM TABLE')
   ORDER BY schema_id, name;