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.
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;