Table Definition

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 and REMOTE 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'] ]

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

Tables created using a query by default use WITH DATA.

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

Associated system table: sys.tables

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

Remote tables

Define an alias for a remote table. The remote table must be an existing table on another running MonetDB server The definition of the remote table structure must match exactly the definition of its counterpart in the remote database, so the same schema name, same table name, same column names and same column data types. For distributed query processing we support the MonetDB specific CREATE REMOTE table syntax.

Example

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;

CREATE TEMP TABLE names (id int NOT NULL PRIMARY KEY, name VARCHAR(99) NOT NULL UNIQUE) ON COMMIT PRESERVE ROWS;
-- Note that temporary tables are implicitly assigned to schema: tmp
INSERT INTO tmp.names VALUES (1, 'one');
INSERT INTO tmp.names VALUES (2, 'two');
INSERT INTO tmp.names VALUES (2, 'dos');
-- Error: INSERT INTO: PRIMARY KEY constraint 'names.names_id_pkey' violated
INSERT INTO tmp.names VALUES (3, 'two');
-- Error: INSERT INTO: UNIQUE constraint 'names.names_name_unique' violated
INSERT INTO tmp.names VALUES (3, 'free');
SELECT * FROM tmp.names;
-- shows 3 rows
DROP TABLE tmp.names;

CREATE GLOBAL TEMP TABLE tmp.name_lengths (name, length)
 AS SELECT DISTINCT name, LENGTH(name) FROM sys.ids ORDER BY 1
 WITH DATA
 ON COMMIT PRESERVE ROWS;
SELECT * FROM tmp.name_lengths WHERE name ILIKE '%\\_id%' ESCAPE '\\';
SELECT COUNT(*) AS count_names, AVG(length) AS avg_name_length FROM tmp.name_lengths;
DROP TABLE tmp.name_lengths;