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, REMOTE TABLE, REPLICA TABLE and UNLOGGED TABLE for specific usages.

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

qname:
     [ schema_name '.' ] table_name

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

Table elements are described in table-elements.

Tables created using a AS select_query by default use WITH DATA. If you only want to copy the query result structure without the data specify WITH NO DATA.

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

Temporary tables

MonetDB supports both LOCAL and GLOBAL temporary tables as defined in the SQL standard. Default is LOCAL.
A LOCAL temporary table is only visible in the current session. It is not visible to other sessions or users. It will be removed automatically after the user session is terminated.
A GLOBAL temporary table will be visible by other users and sessions and remain in the system catalog even after a server shutdown but each session will have its own data and state which is not visible or accessable by other sessions or users.
All temporary tables are stored under the system schema 'tmp'. You are not allowed to specify a different schema name.
When the ON COMMIT clause is not specified then the default behavior is ON COMMIT DELETE ROWS, complying to the SQL standard. In most cases however you would use ON COMMIT PRESERVE ROWS, see examples below.
When using AS SELECT ... the default is WITH DATA.

Examples:

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 TEMPORARY 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 CASCADE;

Merge tables

A merge table is a novel way to virtually create a large horizontally partitioned table from many individual tables. It defines a virtual table. The partition tables are added via ALTER TABLE merge_table ADD TABLE partition_table). However all partition tables must have the exact same table definition.
Compared to a view which combines multiple SELECT queries (via UNION ALL), a merge table is easier and faster to extend/change with new partitions and can processes queries faster, e.g. because it can skip complete partition tables based on statistics. Also with PARTITION BY specified, the virtual merge table becomes updatable, so allow inserts, updates and deletes on the merge table directly instead of on the individual partition tables. For more details read Merge tables.

Remote tables

For distributed query processing we support the MonetDB specific CREATE REMOTE TABLE syntax. It defines 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.

Unlogged tables

An unlogged table is a globally accessible table for which it is not required that its data is stored persistently. Therefore unlogged tables do not have their changes written to the write-ahead log (WAL), sacrificing durability. When committing changes to an unlogged table, it benefits from an absence of WAL I/O since nothing is written to the WAL and thus these commit actions can be relatively very fast.

Unlogged tables differ from global temporary tables in the fact that the unlogged table content is the same for each user, like normal tables.

If an unlogged table has the append only property activated via ALTER TABLE qname SET INSERT ONLY statement, there is the possibility to attempt to persist data that it contains. The function is called sys.persist_unlogged(schema, table). It is a table returning function which returns the table name, the table id and the count of rows in that table that are persisted to disk. If the function call was not able to persist the data, the returned rowcount will be 0.

CREATE SCHEMA put;
SET SCHEMA put;

CREATE UNLOGGED TABLE foo(x int);

ALTER TABLE foo SET INSERT ONLY;
INSERT INTO foo SELECT * FROM generate_series(0,10);

SELECT * FROM persist_unlogged('put', 'foo');
+----------+----------+----------+
| table    | table_id | rowcount |
+================================+
| foo      | 8912     | 0        |
+--------------------------------+
-- No rows were persisted

CREATE SCHEMA put;
SET SCHEMA put;

CREATE UNLOGGED TABLE foo(x int);

ALTER TABLE foo SET INSERT ONLY;
INSERT INTO foo SELECT * FROM generate_series(0,10);

CREATE TABLE bar(x int);
INSERT INTO bar SELECT * FROM generate_series(0,1000000);

SELECT * FROM persist_unlogged('put', 'foo');
+----------+----------+----------+
| table    | table_id | rowcount |
+================================+
| foo      | 8912     | 10       |
+--------------------------------+
-- 10 rows of data persisted

Replica tables

Alter table defintions

To alter a table definition see alter-table.

Drop table statement

drop_table:
     DROP TABLE [ IF EXISTS ] [ schema_name '.' ] table_name [ RESTRICT | CASCADE ]

See also system tables/views: sys.tables, information_schema.tables, sys.columns, information_schema.columns, sys.statistics.

To find out which user created tables are defined in your database run query:

SELECT * FROM sys.tables
 WHERE NOT system
   AND type IN (SELECT table_type_id FROM sys.table_types WHERE table_type_name LIKE '%TABLE')
 ORDER BY schema_id, name;

or alternatively use query

SELECT * FROM information_schema.tables
 WHERE NOT is_system
   AND table_type LIKE '%TABLE'
 ORDER BY table_schema, table_name;

To find out the columns of a specific table and schema in your database run query:

SELECT * FROM sys.columns
 WHERE table_id IN (SELECT id FROM sys.tables WHERE name = 'mytable'
                       AND schema_id IN (SELECT id FROM sys.schemas WHERE name = 'mysschema'))
 ORDER BY table_id, "number";

or alternatively use query

SELECT * FROM information_schema.columns
 WHERE table_schema = 'mysschema'
   AND table_name = 'mytable'
 ORDER BY table_schema, table_name, ordinal_position;

To find out the column statistics of a specific table and schema in your database run query:

SELECT * FROM sys.statistics
 WHERE "schema" = 'mysschema'
   AND "table" = 'mytable'
 ORDER BY column_id;