Table definitions

Table definitions mk Sat, 03/27/2010 - 22:34

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

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.

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;

Table elements

Table elements mk Sun, 03/28/2010 - 13:46
table_element:
     column_name   data_type   [ column_option ... ]
   | column_name { SERIAL | BIGSERIAL }
   | column_name   WITH OPTIONS '(' column_option [ ',' ... ] ')'
   | LIKE table_name
   | [ CONSTRAINT constraint_name ] table_constraint_type
column_option:
     DEFAULT default_value_expr
   | [ CONSTRAINT constraint_name ] column_constraint_type
   | GENERATED ALWAYS AS IDENTITY [ '(' serial_parameters ')' ]
   | AUTO_INCREMENT

Identity columns take their values from a sequence generator. The MySQL (auto_increment) and PostgreSQL (serial data type) syntax are also supported. The SERIAL type is an identity column which is mapped to an INTEGER NOT NULL PRIMARY KEY. Similarly the BIGSERIAL type is an identity column which is mapped to a BIGINT NOT NULL PRIMARY KEY.

Column and Table constraints are both supported. Besides the not null constraint also unique, primary key and foreign key constraints are supported. A table can have at most one primary key definition. We currently check constraints directly on insert, update and delete, so immediate. This limitation stems from the missing triggers. The null matching on foreign keys is limited to the SIMPLE match type (null values satisfy the constraint). The full and partial match types are not supported. The referential action is currently limited to RESTRICT, i.e. an update fails if other columns have references to it.

column_constraint_type:
     NOT NULL
   | NULL
   | UNIQUE
   | PRIMARY KEY
   | REFERENCES table_name [ '(' column_name [ ',' ... ')' ] ]   [ match_options ]   [ ref_actions ]

match_options:
     MATCH { FULL | PARTIAL | SIMPLE }

ref_actions:
     { ON UPDATE | ON DELETE } { NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT }

table_constraint_type:
     UNIQUE '(' column_name [ ',' ... ] ')'
   | PRIMARY KEY '(' column_name [ ',' ... ] ')'
   | FOREIGN KEY '(' column_name [ ',' ... ] ')' REFERENCES table_name [ '(' column_name [ ',' ... ] ')' ]   [ match_options ]   [ ref_actions ]

Note: The column CHECK constraint definitions are no longer accepted by the parser as of Nov2019 (11.35.3) release. They used to be accepted (for ease of migration) but were not enforced nor recorded in a data dictionary table.

A table constraint definition is not tied to a particular column, but rather to a column group. A column constraint is a notational convenience when the constraint only affects one column.

A constraint over a referenced value can be either a FULL, PARTIAL or SIMPLE (default) match. A full match requires all column values to be not null unless all are null. The simple match is more relaxed; any of the keys may be null. The partial match is considered noise for the time being.

The serial columns are a slight extension over the serial types defined earlier. In particular, we can designate RESTART option when the sequence is exhausted. This may involve a subquery over the database.

serial_parameters:
     [ START WITH nonzero-bigint ]
   | [ RESTART | RESTART WITH subquery
   | RESTART WITH nonzero-bigint ]
   | [INCREMENT BY nonzero-bigint ]
   | [MINVALUE nonzero-bigint | NOMINVALUE]
   | [MAXVALUE nonzero-bigint | NOMAXVALUE ]
   | [CACHE nonzero-bigint ]
   | [CYCLE | NOCYCLE]

Index definitions

Index definitions mk Sat, 03/27/2010 - 22:46

The index statements in the SQL standard are recognized, but their implementation is different from competitive products. MonetDB/SQL interprets these statements as an advice and often freely neglects it, relying on its own decision to create and maintain indexes for fast access. Also the UNIQUE qualifier is not honored or enforced. To add a uniqueness constraint for a table or column use UNIQUE in the ALTER TABLE ADD CONSTRAINT statement.

index_def:
    CREATE [ UNIQUE ] INDEX ident ON [ schema name . ] table name '(' column name [ ','... ] ')'

See also: CREATE INDEX command

 

MonetDB supports two special kinds of secondary indices: IMPRINTS and ORDERED INDEX, introduced in release Dec2016 (v11.25.3). These index types are experimental and have some limitations: Only 1 column can be indexed per index. Only columns of numeric data type (tinyint, smallint, integer, bigint, hugeint, float, real, double, decimal) can be indexed. As of release Mar2018 (11.29.3) the ORDERED INDEX does not have this retriction anymore. The index is NOT maintained automatically and will become inactive when inserts, deletes or updates are done on the column data. Since creating these indices can be expensive, they are only used when explicitly created. They are useful for large static (or read only) tables.

index_def:
    CREATE IMPRINTS INDEX ident ON [ schema name . ] table name '(' numeric column name ')'

Creates a new imprints index on one numerical column of a specific table. This index stores meta data (min, max, nulls) on segments of column data. It can speed-up queries which have a column selection condition (such as: AGE IS NULL OR AGE >= 18). It is a very compact index.

See also: CREATE IMPRINTS INDEX command and MonetDBInternals/MALModules/Imprints

index_def:
    CREATE ORDERED INDEX ident ON [ schema name . ] table name '(' column name ')'

Creates a new ordered index on one column of a specific table. This index is a special single column index where the values are stored in ascending order. It can speed-up queries which have a column range or point selection condition (such as: AGE BETWEEN 18 AND 30) or need sorting such as when used in a GROUP BY clause.

See also: CREATE ORDERED INDEX command

 

Alter statement

Alter statement mk Sat, 03/27/2010 - 22:39

The ALTER statement can be used to change the TABLE properties, it requires authorization to do so. Addition of a column follows the same syntax and functionality as the CREATE TABLE statement. If the table is used in a foreign key relationship the actions to be take can be further specified as RESTRICT to forbid it if there are external references to the column values. The CASCADE nullifies all references to the values being removed.

alter_statement:

         ALTER TABLE [IF EXISTS] qname ADD [COLUMN] column_name { data_type [ column_option ... ] | SERIAL | BIGSERIAL }
      |  ALTER TABLE [IF EXISTS] qname ALTER [COLUMN] column_name SET [NOT] NULL
      |  ALTER TABLE [IF EXISTS] qname ALTER [COLUMN] column_name SET DEFAULT value
      |  ALTER TABLE [IF EXISTS] qname ALTER [COLUMN] column_name DROP DEFAULT
      |  ALTER TABLE [IF EXISTS] qname ALTER [COLUMN] column_name SET STORAGE {string | NULL}
      |  ALTER TABLE [IF EXISTS] qname DROP [COLUMN] column_name [RESTRICT | CASCADE]
      |  ALTER TABLE [IF EXISTS] qname ADD [ CONSTRAINT ident ]  table_constraint_type
      |  ALTER TABLE [IF EXISTS] qname DROP CONSTRAINT ident [RESTRICT | CASCADE]

As of Apr2019 release (11.33.3) you can also change the name of a column, the name of the table itself or the schema the table is belonging to, provided there are no objects which depend on the table or column name.

alter_rename_statement:

         ALTER TABLE [IF EXISTS] qname RENAME [COLUMN] column_name TO new_column_name
      |  ALTER TABLE [IF EXISTS] qname RENAME TO new_table_name
      |  ALTER TABLE [IF EXISTS] qname SET SCHEMA schema_name

An individual table can be protected using the READ ONLY and INSERT ONLY mode. All attempts to update are flagged as a SQL error. The reverse operation is ALTER TABLE qname READ WRITE, which makes the table accessible for all update operations.

alter_access_statement:

         ALTER TABLE [IF EXISTS] qname SET READ ONLY
      |  ALTER TABLE [IF EXISTS] qname SET INSERT ONLY
      |  ALTER TABLE [IF EXISTS] qname SET READ WRITE

The ALTER statement has been extended for merge tables with ADD TABLE and DROP TABLE options to attach and detach a partition table to/from a MERGE TABLE and with SET TABLE p AS PARTITION to change the partitioning specification of the partition table.

alter_statement_for_merge_tables:

        ALTER TABLE [IF EXISTS] qname ADD TABLE qname [ AS PARTITION partition_spec ]
      | ALTER TABLE [IF EXISTS] qname SET TABLE qname AS PARTITION partition_spec
      | ALTER TABLE [IF EXISTS] qname DROP TABLE qname [ RESTRICT | CASCADE ]