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 must 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 an 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.
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 accessible by other sessions or users.
All temporary tables are placed in 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;
It is not allowed or possible to add comments to temporary tables or columns of temporary 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 partitioning info or on column data 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.
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 or view 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 REPLICA TABLE syntax. It defines a copy of a remote table.
One can create a REPLICA TABLE and add both local tables (i.e. normal SQL tables that were created
on the same MonetDB server as this REPLICA TABLE) and remote tables (i.e. the aforementioned REMOTE TABLEs) to it.
The keyword REPLICA indicates that all members are an exact replica of each other
and the server can use whichever member is most convenient.
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.
When 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
Note This persist_unlogged() function does not interact with the write-ahead log in any manner.
To alter a table definition see alter-table.
Syntax:
DROP TABLE [ IF EXISTS ] [ schema_name '.' ] table_name [ RESTRICT | CASCADE ]
When RESTRICT or CASCADE is not specified, RESTRICT is used by default.
The definitions of the created tables are stored in system tables.
See tables/views: sys.tables,
information_schema.tables,
sys.columns,
information_schema.columns,
information_schema.table_constraints,
sys.statistics,
sys.storage.
To find out which user created tables are defined in your database run query:
SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE NOT is_system
AND table_type LIKE '%TABLE'
ORDER BY table_schema, table_name;
or alternatively use 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;
To find out the columns of a specific table and schema in your database run query:
SELECT table_schema, table_name, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_schema = 'mysschema'
AND table_name = 'mytable'
ORDER BY table_schema, table_name, ordinal_position;
or alternatively use 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";
To find out the column statistics of a specific table and schema in your database run query:
SELECT *
FROM sys.statistics('mysschema', 'mytable')
ORDER BY "schema", "table", "column_id";
To find out the column storage information of a specific table and schema in your database run query:
SELECT *
FROM sys.storage('mysschema', 'mytable')
ORDER BY "schema", "table", "column";