SQL Commands Overview

Data Definition Language (DDL)

CREATE SCHEMA SET SCHEMA	COMMENT ON SCHEMA	ALTER SCHEMA	DROP SCHEMA

CREATE SEQUENCE	COMMENT ON SEQUENCE	ALTER SEQUENCE	DROP SEQUENCE

CREATE TABLE	COMMENT ON TABLE	COMMENT ON COLUMN	DROP TABLE
CREATE TABLE AS	ALTER TABLE ADD COLUMN	ALTER TABLE ALTER COLUMN	ALTER TABLE DROP COLUMN
CREATE TABLE FROM LOADER	ALTER TABLE ADD CONSTRAINT		ALTER TABLE DROP CONSTRAINT
	ALTER TABLE RENAME TO	ALTER TABLE RENAME COLUMN	ALTER TABLE SET SCHEMA
	ALTER TABLE SET INSERT ONLY	ALTER TABLE SET READ ONLY	ALTER TABLE SET READ WRITE

CREATE MERGE TABLE	ALTER TABLE ADD TABLE	ALTER TABLE SET TABLE	ALTER TABLE DROP TABLE
CREATE REMOTE TABLE
CREATE REPLICA TABLE
CREATE STREAM TABLE
CREATE TEMPORARY TABLE

CREATE INDEX CREATE IMPRINTS INDEX CREATE ORDERED INDEX	COMMENT ON INDEX	DROP INDEX

CREATE TRIGGER		DROP TRIGGER

CREATE VIEW	COMMENT ON VIEW	DROP VIEW

CREATE FUNCTION CREATE FUNCTION EXTERNAL CREATE FUNCTION LANGUAGE	COMMENT ON FUNCTION	DROP FUNCTION	DROP ALL FUNCTION
CREATE PROCEDURE CREATE PROCEDURE EXTERNAL	COMMENT ON PROCEDURE	DROP PROCEDURE	DROP ALL PROCEDURE
CREATE AGGREGATE EXTERNAL CREATE AGGREGATE LANGUAGE	COMMENT ON AGGREGATE	DROP AGGREGATE	DROP ALL AGGREGATE
CREATE FILTER FUNCTION EXTERN	COMMENT ON FILTER FUNCTION	DROP FILTER FUNCTION	DROP ALL FILTER FUNCTION
CREATE LOADER	COMMENT ON LOADER	DROP LOADER	DROP ALL LOADER
CREATE WINDOW EXTERNAL	COMMENT ON WINDOW	DROP WINDOW	DROP ALL WINDOW

CREATE TYPE EXTERNAL	DROP TYPE
DECLARE

Data Manipulation Language (DML)

SELECT	SELECT INTO	WITH SELECT
INSERT VALUES	INSERT SELECT
UPDATE
MERGE
DELETE
TRUNCATE

PREPARE	EXECUTE	DEALLOCATE
CALL
SET	SET TIME ZONE	SET CURRENT_TIMEZONE

Bulk Data Import Commands

COPY INTO FROM

COPY INTO FROM STDIN

COPY BINARY INTO FROM

COPY LOADER INTO FROM

Bulk Data Export Commands

COPY INTO

COPY INTO STDOUT

Authorization, Data Access Control Language

CREATE USER	SET USER SET CURRENT_USER SET SESSION_USER	ALTER USER RENAME ALTER USER SET PASSWORD ALTER USER SET SCHEMA	DROP USER
GRANT PRIVILEGES	REVOKE PRIVILEGES
CREATE ROLE	SET ROLE	SET SESSION AUTHORIZATION	DROP ROLE
GRANT ROLE	REVOKE ROLE

Transaction Control Commands

START TRANSACTION	COMMIT	ROLLBACK
SET TRANSACTION	SET LOCAL TRANSACTION
SAVEPOINT	RELEASE SAVEPOINT	ROLLBACK TO SAVEPOINT

Commands to Analyse / Optimize SQL Execution

Pseudo Columns

CURRENT_SCHEMA

CURRENT_USER USER SESSION_USER

CURRENT_DATE NOW

Details

CREATE SCHEMA

   CREATE SCHEMA [ IF NOT EXISTS ] [ schema_name ]
    [ AUTHORIZATION auth_name ]
    [ DEFAULT CHARACTER SET character_set_name ]
    [ PATH schema_name [, schema_name] [, ... ] ]
    [ schema_element [, schema_element] [, ... ] ]

   schema_element:
        create_statement | alter_statement | drop_statement | grant | revoke

Define a new schema

Examples:

CREATE SCHEMA tst;
CREATE SCHEMA AUTHORIZATION hrm;
CREATE SCHEMA hr AUTHORIZATION hrm;
SET SCHEMA tst;
SELECT CURRENT_SCHEMA;

Note: You must specify either a schema_name or an auth_name or both, see examples.
The auth_name can be either an existing role or user name.
The ownership of a schema can be assigned to only one user/role, and it can not be modified after its creation. Therefore, to share the ownership of a schema, one must assign the ownership of a schema to a role at the creation of the schema. Subsequently, the role can be granted to multiple users to own the schema.
Only the 'monetdb' user and the 'sysadmin' role can create a new schema. Therefore, to allow other users to create schemas, the 'monetdb' user should assign the 'sysadmin' role to the intended users. The DEFAULT CHARACTER SET and PATH options are not (yet) implemented and here for compatibility reasons with the SQL standard. The default character set is UTF-8 and not changable.
The "IF NOT EXISTS" option is supported from release Jul2017 (11.27.1) onwards.

For details see: Documentation/SQLReference/DataDefinition/SchemaDefinitions
See also: SET SCHEMA CURRENT_SCHEMA COMMENT ON SCHEMA DROP SCHEMA

Associated system table: sys.schemas

ALTER SCHEMA

   ALTER SCHEMA [ IF NOT EXISTS ] schema_name RENAME TO new_schema_name

Change the name of a schema

Example:

CREATE SCHEMA tst;
ALTER SCHEMA tst RENAME TO tst2;

It is only allowed to change the name of a schema if no objects exists which depend on the schema name, such as tables, views, functions, etc.
Note: This command is supported from release Apr2019 onwards.

For details see: Documentation/SQLReference/DataDefinition/SchemaDefinitions
See also: SET SCHEMA CURRENT_SCHEMA COMMENT ON SCHEMA DROP SCHEMA

Associated system table: sys.schemas

CREATE SEQUENCE

   CREATE SEQUENCE [ schema_name . ] sequence_name
    [ AS datatype ]
    [ START WITH bigint# ]
    [ INCREMENT BY bigint# ]
    [ MINVALUE bigint# | NO MINVALUE ]
    [ MAXVALUE bigint# | NO MAXVALUE ]
    [ CACHE bigint# ]
    [ [ NO ] CYCLE ]

Define a new integer number sequence generator

For details see: Documentation/SQLreference/DataTypes/SerialDataTypes
See also: ALTER SEQUENCE COMMENT ON SEQUENCE NEXT VALUE FOR DROP SEQUENCE

Associated system table: sys.sequences

CREATE TABLE

   CREATE TABLE [ IF NOT EXISTS ] [ schema_name . ] table_name
    ( column_definition(s)_and_optional_table-constraints_list )

Define a new table including data integrity constraints

Note: WARNING: Column CHECK constraint definitions are accepted but not enforced! They are also not stored in the data dictionary, so will be lost when using msqldump.
The "IF NOT EXISTS" option is supported from release Jul2017 (11.27.1) onwards.
You can also use LIKE qname as part of the column definition to copy the column definitions of qname excluding their constraints. For instance CREATE TABLE webshop.products_new (LIKE webshop.products, descr VARCHAR(9999), pict BLOB);

For details see: Documentation/SQLReference/TableDefinitions and: Documentation/SQLreference/TableDefinitions/TableIElements
See also: ALTER TABLE ADD COLUMN ALTER TABLE ADD CONSTRAINT COMMENT ON TABLE COMMENT ON COLUMN DROP TABLE

Associated system table: sys.tables where type = 0

CREATE TABLE AS

   CREATE TABLE [ IF NOT EXISTS ] [ schema_name . ] table_name
    [ ( column_name [, column_name] [, ... ] ) ]
    AS SELECT_query
    [ WITH [ NO ] DATA ]

Define a new table from the results of a query. By default the table will be populated with the data of the query. Specify WITH NO DATA to only create the table.

Note: Default behavior is WITH DATA.
The "IF NOT EXISTS" option is supported from release Jul2017 (11.27.1) onwards.

For details see: Documentation/SQLReference/TableDefinitions
See also: ALTER TABLE ADD COLUMN ALTER TABLE ADD CONSTRAINT COMMENT ON TABLE COMMENT ON COLUMN DROP TABLE

Associated system table: sys.tables where type = 0

CREATE TABLE FROM LOADER

   CREATE TABLE [ IF NOT EXISTS ] [ schema_name . ] table_name
    FROM LOADER function_name ( [ arg1_val [ , arg2_val ] [, ... ] ] )
    [ WITH [ NO ] DATA ]

Define a new table from the results of a (Python) loader function. By default the table will be populated with the data of the loader function. Specify WITH NO DATA to only create the table.

Note: This command is MonetDB specific. It is introduced in release Dec2016 (11.25.3).
The "IF NOT EXISTS" option is supported from release Jul2017 (11.27.1) onwards.

For details see: blog/monetdbpython-loader-functions
See also: CREATE LOADER COPY LOADER INTO FROM COMMENT ON TABLE COMMENT ON COLUMN DROP TABLE

Associated system table: sys.tables where type = 0

CREATE MERGE TABLE

   CREATE MERGE TABLE [ IF NOT EXISTS ] [ schema_name . ] table_name
    ( column_definition(s)_and_optional_table-constraints_list )
    [ PARTITION BY { RANGE | VALUES }  { ON ( column_name )  |  USING ( expression ) } ]

Define a new merge table to create a horizontally partitioned table.
A merge table logically combines the data of multiple other tables (called partition tables which are added via ALTER TABLE merge_table ADD TABLE partition_table) which all must have the exact same table definition. This merge table is easier to extend/change with new partitions than a view which combines multiple SELECT queries (via UNION ALL) and can processes queries faster. Also with "PARTITION BY" specified, the virtual merge table becomes updatable, so allow inserts, updates, deletes and truncate on the merge table directly instead of the partition tables.

Note: This command is MonetDB specific.
The "PARTITION BY" option is supported from release Apr2019 (11.33.3) onwards. See updatable-merge-tables for details.
The "IF NOT EXISTS" option is supported from release Jul2017 (11.27.1) onwards.
A merge table is removed (including all its partition table information, but not the partition tables) using the ordinary DROP TABLE statement. There is no DROP MERGE TABLE statement.

For details see: Documentation/ServerProgramming/SystemCatalog/TableDataPartitioning and updatable-merge-tables
and: Documentation/SQLReference/TableDefinitions
See also: ALTER TABLE ADD TABLE ALTER TABLE DROP TABLE COMMENT ON TABLE COMMENT ON COLUMN DROP TABLE

Associated system tables: sys.tables where type = 3; sys.table_partitions

CREATE REMOTE TABLE

   CREATE REMOTE TABLE [ IF NOT EXISTS ] [ schema_name . ] table_name
    ( column definition(s) )
    ON 'mapi:monetdb://host:port/dbname'
    [ WITH [ USER 'user_login_name_nm' ]   [ [ ENCRYPTED ] PASSWORD 'password' ] ]

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.
The format of the remote server URL must conform to: 'mapi:monetdb://host:port/dbname', where all three parameters (host, port and dbname) must be specified.

Note: This command is MonetDB specific.
The "IF NOT EXISTS" option is supported from release Jul2017 (11.27.1) onwards.
The "WITH USER ... PASSWORD ..." option is supported from release Aug2018 (11.31.7) onwards. See this blog post for more information.
A remote table definition is removed using the ordinary DROP TABLE statement. There is no DROP REMOTE TABLE statement.

For details see: Documentation/ServerAdministration/DistributedQueryProcessing and: Documentation/SQLReference/TableDefinitions
See also: COMMENT ON TABLE COMMENT ON COLUMN DROP TABLE

Associated system table: sys.tables where type = 5

CREATE REPLICA TABLE

   CREATE REPLICA TABLE [ IF NOT EXISTS ] [ schema_name . ] table_name
    ( column_definition(s)_and_optional_table-constraints_list )

Define a new replica table

Note: This command is MonetDB specific.
The "IF NOT EXISTS" option is supported from release Jul2017 (11.27.1) onwards.
A replica table is removed using the ordinary DROP TABLE statement. There is no DROP REPLICA TABLE statement.

For details see: Documentation/SQLReference/TableDefinitions and: Documentation/SQLReference/TableDefinitions/TableIElements
See also: COMMENT ON TABLE COMMENT ON COLUMN DROP TABLE

Associated system table: sys.tables where type = 6

CREATE STREAM TABLE

   CREATE STREAM TABLE [ IF NOT EXISTS ] [ schema_name . ] table_name
    ( column_definition(s)_and_optional_table-constraints_list )

Define a new stream table

Example:

CREATE STREAM TABLE room_event (ts timestamp, room integer, payload decimal(8,2));

Note: This command is MonetDB specific.
The "IF NOT EXISTS" option is supported from release Jul2017 (11.27.1) onwards.
A stream table is removed using the ordinary DROP TABLE statement. There is no DROP STREAM TABLE statement.

For details see: Documentation/ServerProgramming/ExtendingSQL/Streams/Catalog
See also: COMMENT ON TABLE COMMENT ON COLUMN DROP TABLE

Associated system table: sys.tables where type = 4

CREATE TEMPORARY TABLE

   CREATE [ LOCAL | GLOBAL ] { TEMPORARY | TEMP } TABLE [ IF NOT EXISTS ] [ schema_name . ] table_name
    { ( column_definition(s)_and_optional_table-constraints_list )  |
      [ ( column_name [, column_name ] [, ... ] ) ] AS SELECT_query [ WITH [ NO ] DATA ] }
    [ ON COMMIT { DELETE ROWS | PRESERVE ROWS | DROP } ]

Define a new temporary table. The visibility of the table to other session users can be controlled by using GLOBAL. Default is LOCAL. A temporary table will be removed automatically after the user session is terminated.
When ON COMMIT clause is not specified then the default behavior is ON COMMIT DELETE ROWS, complying to the SQL standard.
When using AS SELECT ... the default is WITH DATA.

Example:

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;

IMPORTANT: specify ON COMMIT PRESERVE ROWS to keep rows when working in autocommit mode, because default behavior is ON COMMIT DELETE ROWS.
Note: The "IF NOT EXISTS" option is supported from release Jul2017 (11.27.1) onwards.
It is not possible to add comments on temporary tables or columns thereof.
A temporary table is removed using the ordinary DROP TABLE statement. There is no DROP TEMPORARY TABLE statement.

For details see: Documentation/SQLReference/TableDefinitions and: Documentation/SQLreference/TableDefinitions/TableIElements
See also: ALTER TABLE ADD COLUMN ALTER TABLE ADD CONSTRAINT DROP TABLE

Associated system table: sys.tables where type in (20, 30)

CREATE INDEX

   CREATE [ UNIQUE ] INDEX index_name ON [ schema_name . ] table_name ( column_name [, column_name ] [, ... ] )

Define a new secondary index on one or more columns of a specific table

Note: Although CREATE INDEX commands are accepted by the MonetDB parser for SQL compliance purposes, it currently does not create a physical secondary index via this SQL command. Instead MonetDB internally decides which column search accelerator(s) to create, persist and use during SQL query execution.
The index name must be unique within the schema of the table. You can not specify the schema for the index. It will be placed in the same schema as the table.
WARNING: the UNIQUE keyword does NOT enforce a uniqueness constraint. To create a unique constraint use: ALTER TABLE s.t ADD CONSTRAINT t_uc UNIQUE (c1, c2) instead.

For details see: Documentation/SQLreference/TableDefinitions/IndexDefinitions
See also: CREATE ORDERED INDEX ALTER TABLE ADD CONSTRAINT COMMENT ON INDEX DROP INDEX

Associated system table: sys.idxs

CREATE IMPRINTS INDEX

   CREATE IMPRINTS INDEX index_name ON [ schema_name . ] table_name ( column_name )

Define a new imprints index (a simple but efficient cache conscious secondary index) on one numerical column of a specific table. This index is a special single column index (for numeric columns only) which stores meta data (min, max, nulls) on segments of column data. An imprint is used during query evaluation to limit data access and thus minimize memory traffic. It can speedup queries which have a column selection condition (such as: AGE IS NULL OR AGE BETWEEN 25 AND 65). The storage overhead is just a few percent over the size of the columns being indexed.

Example:

CREATE IMPRINTS INDEX my_impr_idx ON myschema.mytable ( my_num_column );

Note: This command is MonetDB specific. It is introduced in release Dec2016 (11.25.3).
The imprints index is a new type of experimental column index.
Limitations are: Only 1 column can be indexed per index. Only columns of fixed size data type (so not on: char, varchar, clob, blob, url, json, inet and uuid) can be indexed.
The index name must be unique within the schema of the table. You can not specify the schema for the index. It will be placed in the same schema as the table.
Warning: The index is not maintained automatically and will become inactive after inserts, deletes or updates are done on the column data.
An imprints index is removed using the ordinary DROP INDEX statement. There is no DROP IMPRINTS INDEX statement.

For details see: Documentation/SQLreference/TableDefinitions/IndexDefinitions
and: Home/ScienceLibrary
See also: CREATE ORDERED INDEX CREATE INDEX COMMENT ON INDEX DROP INDEX

Associated system table: sys.idxs sys.storage

CREATE ORDERED INDEX

   CREATE ORDERED INDEX index_name ON [ schema_name . ] table_name ( column_name )

Define 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 speedup queries which have a column selection condition (such as: AGE >=18 or AGE BETWEEN 18 AND 30) or need sorting such as when GROUP BY is used.

Example:

CREATE ORDERED INDEX my_ord_idx ON myschema.mytable ( my_column );

Note: This command is MonetDB specific. It is introduced in release Dec2016 (11.25.3).
The ordered index is a new type of experimental column index.
Limitations: Only 1 column can be indexed per index.
The index name must be unique within the schema of the table. You can not specify the schema for the index. It will be placed in the same schema as the table.
Releases before Mar2018 (11.29.3) only allowed columns of fixed size data type (so not on: char, varchar, clob, blob, url, json, inet and uuid) could be indexed. This limitation has been removed in Mar2018 (and future) releases.
Warning: The index is not maintained automatically and will become inactive after inserts, deletes or updates are done on the column data.
A ordered index is removed using the ordinary DROP INDEX statement. There is no DROP ORDERED INDEX statement.

For details see: Documentation/SQLreference/TableDefinitions/IndexDefinitions
See also: CREATE IMPRINTS INDEX CREATE INDEX COMMENT ON INDEX DROP INDEX

Associated system table: sys.idxs sys.storage

CREATE VIEW

   CREATE [ OR REPLACE ] VIEW [ schema_name . ] view_name
    [ ( column_name [, column_name ] [, ... ] ) ]
    AS SELECT_query
    [ WITH CHECK OPTION ]

Define a new SQL view. A view is a virtual table based on the result set of a stored SELECT query. A view does not physically store data. It is useful to reduce query complexity as it can include joins, computations, unions, aggregations. It can increase standardisation and reusability and be used to control data access, provide an abstraction layer between applications and physical tables, simplify reporting.

Limitation: Views are not updatable. The "WITH CHECK OPTION" is accepted for compliance but has no effect.
Recursive views and reference-able views are not supported.
Note: An "ORDER BY" clause in the SELECT-query is supported from release Jul2017 (11.27.1) onwards.
The "OR REPLACE" option is supported from release Mar2018 (11.29.3) onwards.

For details see: Documentation/SQLReference/DataDefinition/ViewDefinitions
See also: COMMENT ON VIEW COMMENT ON COLUMN DROP VIEW

Associated system table: sys.tables where type = 1

CREATE AGGREGATE EXTERNAL

   CREATE [ OR REPLACE ] AGGREGATE [ FUNCTION ] [ schema_name . ] aggregate_name ( [ arg1_nm_type [ , arg2_nm_type ] [, ... ] ] )
    RETURNS datatype
    EXTERNAL NAME  MAL_function_name

Define a new user-defined aggregation function for which the implementation is specified externally using MAL and C

Example:

CREATE AGGREGATE quantile(val bigint, q double) RETURNS bigint EXTERNAL NAME "aggr"."quantile";

Note: This command is MonetDB specific. External implies language MAL.
To allow other users to invoke a user-defined aggregate function, you must grant the other users (or PUBLIC) EXECUTE privilege for the aggregate function.
The "OR REPLACE" option is supported from release Jul2017 (11.27.1) onwards.

For built-in aggregate functions see: Documentation/SQLReference/FunctionsAndOperators/AggregateFunctions
See also: COMMENT ON AGGREGATE GRANT PRIVILEGES DROP AGGREGATE DROP ALL AGGREGATE

Associated system table: sys.functions where type = 3 and language = 1

CREATE AGGREGATE LANGUAGE

   CREATE [ OR REPLACE ] AGGREGATE [ FUNCTION ] [ schema_name . ] aggregate_name ( [ arg1_nm_type [ , arg2_nm_type ] [, ... ] ] )
    RETURNS datatype
    LANGUAGE { C | CPP | R | PYTHON | PYTHON_MAP | PYTHON3 | PYTHON3_MAP }
    '{' function_body '}'

Define a new user-defined aggregation function for which the body implementation is specified in the specific script language

Example:

CREATE AGGREGATE python_aggregate(val INTEGER) 
RETURNS INTEGER 
LANGUAGE PYTHON {
    try:
        unique = numpy.unique(aggr_group)
        x = numpy.zeros(shape=(unique.size))
        for i in range(0, unique.size):
            x[i] = numpy.sum(val[aggr_group==unique[i]])
    except NameError:
        # aggr_group doesn't exist. no groups, aggregate on all data
        x = numpy.sum(val)
    return(x)
};

Note: This command is MonetDB specific. Supported languages are: C, C++, R and Python.
If your Python code requires Python 3 to work correctly use PYTHON3 instead of PYTHON. If your Python code can be executed in parallel (using multiple threads) without side effects, use PYTHON_MAP instead of PYTHON. Idem for PYTHON3_MAP.
Note: Python 2 and the language keywords PYTHON2 and PYTHON2_MAP are no longer supported from release Jun2020 (11.37.7) onwards.
For languages C and CPP a C/C++ compiler must be available on the deployment server and the MonetDB server started with option: --set embedded_c=true.
For language R the R script interpreter software must be available on the deployment server and the MonetDB server started with option: --set embedded_r=true.
For language PYTHON the Python script interpreter software must be available on the deployment server and the MonetDB server started with option: --set embedded_py=true or --set embedded_py=3.
To allow other users to invoke a user-defined aggregate function, you must grant the other users (or PUBLIC) EXECUTE privilege for the aggregate function.
The "OR REPLACE" option is supported from release Jul2017 (11.27.1) onwards.

For details see: JIT C/C++ UDFs and embedded-pythonnumpy-monetdb and embedded-r-monetdb
See also: COMMENT ON AGGREGATE GRANT PRIVILEGES DROP AGGREGATE CREATE FUNCTION LANGUAGE

Associated system table: sys.functions where type = 3 and language > 2; sys.function_languages

CREATE FUNCTION

   CREATE [ OR REPLACE ] FUNCTION [ schema_name . ] function_name ( [ arg1_nm_type [ , arg2_nm_type ] [, ... ] ] )
    RETURNS datatype
    BEGIN function_body END

Define a new user-defined function

Example:

CREATE FUNCTION heapspace(tpe string, i bigint, w int) returns bigint
begin
 if tpe <> 'varchar' and tpe <> 'clob' then return 0;
 end if;
 return 10240 + i * w;
end;

Note: Besides returning a scalar value (type = 1), functions can also be defined to return a Table as data type (type = 5).
To allow other users to invoke a user-defined function, you must grant the other users (or PUBLIC) EXECUTE privilege for the function.
The "OR REPLACE" option is supported from release Jul2017 (11.27.1) onwards.

For built-in functions see: Documentation/SQLReference/FunctionsAndOperators
See also: COMMENT ON FUNCTION GRANT PRIVILEGES DROP FUNCTION DROP ALL FUNCTION

Associated system table: sys.functions where type in (1,5) and language = 2

CREATE FUNCTION EXTERNAL

   CREATE [ OR REPLACE ] FUNCTION [ schema_name . ] function_name ( [ arg1_nm_type [ , arg2_nm_type ] [, ... ] ] )
    RETURNS datatype
    EXTERNAL NAME  MAL_function_name

Define a new user-defined function for which the implementation is specified externally using MAL and C

Example:

CREATE FUNCTION isa_uuid(s string) RETURNS boolean EXTERNAL NAME uuid."isaUUID";

CREATE OR REPLACE FUNCTION pcre_match(s string, pattern string) RETURNS boolean EXTERNAL NAME pcre."match";
CREATE OR REPLACE FUNCTION pcre_imatch(s string, pattern string) RETURNS boolean EXTERNAL NAME pcre."imatch";
CREATE OR REPLACE FUNCTION pcre_replace(s string, pattern string, repl string, flags string)
 RETURNS string EXTERNAL NAME pcre."replace";
CREATE OR REPLACE FUNCTION pcre_replacefirst(s string, pattern string, repl string, flags string)
 RETURNS string EXTERNAL NAME pcre."replace_first";

Note: This command is MonetDB specific. External implies language MAL.
Besides returning a scalar value (type = 1), functions can also be defined to return a Table as data type (type = 5).
To allow other users to invoke a user-defined function, you must grant the other users (or PUBLIC) EXECUTE privilege for the function.
The "OR REPLACE" option is supported from release Jul2017 (11.27.1) onwards.
The pcre example functions are copied from use PCRE pattern matching.

For built-in functions see: Documentation/SQLReference/FunctionsAndOperators
and: Documentation/ServerProgramming/ExtendingSQL/UserDefinedFunctions
See also: COMMENT ON FUNCTION GRANT PRIVILEGES DROP FUNCTION CREATE FUNCTION CREATE FUNCTION LANGUAGE

Associated system table: sys.functions where type in (1,5) and language = 1

CREATE FUNCTION LANGUAGE

   CREATE [ OR REPLACE ] FUNCTION [ schema_name . ] function_name ( [ arg1_nm_type [ , arg2_nm_type ] [, ... ] ] )
    RETURNS datatype
    LANGUAGE { C | CPP | R | PYTHON | PYTHON_MAP | PYTHON3 | PYTHON3_MAP }
    '{' function_body '}'

Define a new user-defined function for which the implementation is specified in a specific script language

Note: This command is MonetDB specific. Supported languages are: C, C++, R and Python.
If your Python code requires Python 3 to work correctly use PYTHON3 instead of PYTHON. If your Python code can be executed in parallel (using multiple threads) without side effects, use PYTHON_MAP instead of PYTHON. Idem for PYTHON3_MAP.
Note: Python 2 and the language keywords PYTHON2 and PYTHON2_MAP are no longer supported from release Jun2020 (11.37.7) onwards.
For languages C and CPP a C/C++ compiler must be available on the deployment server and the MonetDB server started with option: --set embedded_c=true.
For language R the R script interpreter software must be available on the deployment server and the MonetDB server started with option: --set embedded_r=true.
For language PYTHON the Python script interpreter software must be available on the deployment server and the MonetDB server started with option: --set embedded_py=true or --set embedded_py=3.
Besides returning a scalar value (type = 1), functions can also be defined to return a Table as data type (type = 5).
To allow other users to invoke a user-defined function, you must grant the other users (or PUBLIC) EXECUTE privilege for the function.
The "OR REPLACE" option is supported from release Jul2017 (11.27.1) onwards.

For details see: JIT C/C++ UDFs and embedded-pythonnumpy-monetdb and embedded-r-monetdb and voter-classification-using-monetdbpython
See also: COMMENT ON FUNCTION GRANT PRIVILEGES DROP FUNCTION CREATE AGGREGATE LANGUAGE CREATE LOADER

Associated system table: sys.functions where type in (1,5) and language > 2; sys.function_languages

CREATE FILTER FUNCTION EXTERNAL

   CREATE [ OR REPLACE ] FILTER [ FUNCTION ] [ schema_name . ] function_name ( [ arg1_nm_type [ , arg2_nm_type ] [, ... ] ] )
    RETURNS datatype
    EXTERNAL NAME  MAL_function_name

Define a new user-defined filter function for which the implementation is specified externally using MAL and C

Example:

CREATE FILTER FUNCTION "re_like"(val string, pat string) external name algebra."rexpr_like";

Note: This command is MonetDB specific. External implies language MAL.
To allow other users to invoke a user-defined filter function, you must grant the other users (or PUBLIC) EXECUTE privilege for the filter function.
The "OR REPLACE" option is supported from release Jul2017 (11.27.1) onwards.

See also: COMMENT ON FILTER FUNCTION GRANT PRIVILEGES DROP FILTER FUNCTION DROP ALL FILTER FUNCTION

Associated system table: sys.functions where type = 4 and language = 1

CREATE LOADER

   CREATE [ OR REPLACE ] LOADER [ FUNCTION ] [ schema_name . ] function_name ( [ arg1_nm_type [ , arg2_nm_type ] [, ... ] ] )
    LANGUAGE PYTHON
    '{' python_code_with_emit.emit()_function '}'

Define a new user-defined loader function for which the implementation is done in Python language. A loader function can for instance read data from an external file is a specific format such as XML, json, bson, ods, xlsx, etc.

Example:

CREATE LOADER json_loader(filename STRING) LANGUAGE PYTHON {
    import json
    f = open(filename)
    _emit.emit(json.load(f))
    f.close()
};

Note: This command is MonetDB specific. It is introduced in release Dec2016 (11.25.3).
For language PYTHON the Python script interpreter software must be available on the deployment server and the MonetDB server started with option: --set embedded_py=true or --set embedded_py=3.
To allow other users to invoke a user-defined loader function, you must grant the other users (or PUBLIC) EXECUTE privilege for the loader function.
The "OR REPLACE" option is supported from release Jul2017 (11.27.1) onwards.

For details see: blog/monetdbpython-loader-functions
See also: COMMENT ON LOADER GRANT PRIVILEGES DROP LOADER CREATE TABLE FROM LOADER COPY LOADER INTO FROM

Associated system table: sys.functions where type = 7 and language > 2

CREATE PROCEDURE

   CREATE [ OR REPLACE ] PROCEDURE [ schema_name . ] procedure_name ( [ arg1_nm_type [ , arg2_nm_type ] [, ... ] ] )
    BEGIN procedure_body END

Define a new user-defined procedure

Note: To allow other users to call and execute a user-defined procedure, you must grant the other users (or PUBLIC) EXECUTE privilege for the procedure.
The "OR REPLACE" option is supported from release Jul2017 (11.27.1) onwards.

For details see: Documentation/SQLreference/ProgrammingSQL/Procedures
See also: COMMENT ON PROCEDURE DECLARE CALL GRANT PRIVILEGES DROP PROCEDURE DROP ALL PROCEDURE

Associated system table: sys.functions where type = 2 and language = 2

CREATE PROCEDURE EXTERNAL

   CREATE [ OR REPLACE ] PROCEDURE [ schema_name . ] procedure_name ( [ arg1_nm_type [ , arg2_nm_type ] [, ... ] ] )
    EXTERNAL NAME  MAL_procedure_name

Define a new user-defined procedure for which the implementation is specified externally using MAL and C

Example:

CREATE PROCEDURE sys.createorderindex(sys string, tab string, col string) external name sql.createorderindex;

Note: This command is MonetDB specific. External implies language MAL.
To allow other users to call and execute a user-defined procedure, you must grant the other users (or PUBLIC) EXECUTE privilege for the procedure.
The "OR REPLACE" option is supported from release Jul2017 (11.27.1) onwards.

For details see: Documentation/SQLreference/ProgrammingSQL/Procedures
See also: COMMENT ON PROCEDURE CALL GRANT PRIVILEGES DROP PROCEDURE DROP ALL PROCEDURE

Associated system table: sys.functions where type = 2 and language = 1

CREATE WINDOW EXTERNAL

   CREATE [ OR REPLACE ] WINDOW [ FUNCTION ] [ schema_name . ] window_name ( [ arg1_nm_type [ , arg2_nm_type ] [, ... ] ] )
    RETURNS datatype
    EXTERNAL NAME  MAL_function_name

Define a new user-defined window function for which the implementation is specified externally using MAL and C

Example:

CREATE OR REPLACE WINDOW stddev(val bigint) RETURNS double EXTERNAL NAME "sql"."stdevp";

Note: This command is MonetDB specific and supported from release Jun2020 (11.37.7) onwards. External implies language MAL.
To allow other users to invoke a user-defined window function, you must grant the other users (or PUBLIC) EXECUTE privilege for the window function.

For built-in window functions see: Documentation/SQLReference/FunctionsAndOperators/WindowFunctions
See also: COMMENT ON WINDOW GRANT PRIVILEGES DROP WINDOW DROP ALL WINDOW

Associated system table: sys.functions where type = 6 and language = 1

CREATE TYPE EXTERNAL

   CREATE TYPE [ schema_name . ] type_name
    EXTERNAL NAME  MAL type_name

Declare a new user-defined data type. The implementation (structure, operators & functions both scalar and bulk) must be specified externally in C code and MAL script. For examples see the C implementations of data types: inet, json, url and uuid.

Note: This command is MonetDB specific. External implies language MAL.

For details see: Documentation/SQLReference/DataTypes/UserDefinedTypes
See also: DROP TYPE CREATE TABLE

Associated system table: sys.types where eclass = 16

CREATE TRIGGER

   CREATE [ OR REPLACE ] TRIGGER trigger_name
    { BEFORE | AFTER }
    { INSERT | DELETE | TRUNCATE | UPDATE [ OF column_name [, column_name] [, ... ] ] }
    ON [ schema_name . ] table_name
    [ REFERENCING { { OLD | NEW } { [ ROW ] | TABLE } [ AS ] ident } [...] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( search_condition ) ]
    { trigger_procedure_statement | BEGIN ATOMIC trigger_procedure_statement_list END }

Define a new trigger on a table update event

Note: FOR EACH STATEMENT is the default if not specified.
The "OR REPLACE" option and the TRUNCATE trigger event are supported from release Mar2018 (11.29.3) onwards.
The option to specify a schema name for the trigger name has been removed since release Oct2020-SP2 (11.39.11). Previous the schema name of a full qualified trigger name already had to be the same as the schema name of the table.

For details see: Documentation/SQLreference/ProgrammingSQL/Triggers
See also: DROP TRIGGER DECLARE GRANT PRIVILEGES

Associated system table: sys.triggers

COMMENT ON SCHEMA

   COMMENT ON SCHEMA schema_name IS  { 'comment text' | NULL | '' }

Add or update or remove a comment for a schema

Example:

COMMENT ON SCHEMA prj4 IS 'schema of new project 4';
COMMENT ON SCHEMA prj0 IS '';
COMMENT ON SCHEMA prjX IS NULL;

Note: By specifying IS NULL or IS '' you remove the comment for the schema.
If the schema is dropped, the associated comment is also removed.
This command is supported from release Mar2018 (11.29.3) onwards.

For details see: bugzilla/show_bug.cgi?id=6109#c5
See also: CREATE SCHEMA DROP SCHEMA

Associated system table: sys.comments

COMMENT ON TABLE

   COMMENT ON TABLE [ schema_name . ] table_name IS  { 'comment text' | NULL | '' }

Add or update or remove a comment for a table

Example:

COMMENT ON TABLE sys.comments IS 'contains comments on all db objects'

Note: By specifying IS NULL or IS '' you remove the comment for the table object.
If the table is dropped, the associated comment (including the comments for the columns) is also removed.
This command is supported from release Mar2018 (11.29.3) onwards.
Note: it is not allowed or possible to add comments for temporary tables in schema "tmp".

For details see: bugzilla/show_bug.cgi?id=6109#c5
See also: CREATE TABLE DROP TABLE

Associated system table: sys.comments

COMMENT ON VIEW

   COMMENT ON VIEW [ schema_name . ] view_name IS  { 'comment text' | NULL | '' }

Add or update or remove a comment for a view

Example:

COMMENT ON VIEW mysch.articles_aggr IS 'view lists aggregated info on articles'

Note: By specifying IS NULL or IS '' you remove the comment for the view.
If the view is dropped, the associated comment (including the comments for the columns) is also removed.
This command is supported from release Mar2018 (11.29.3) onwards.

For details see: bugzilla/show_bug.cgi?id=6109#c5
See also: CREATE VIEW DROP VIEW

Associated system table: sys.comments

COMMENT ON COLUMN

   COMMENT ON COLUMN [ schema_name . ] table_or_view_name . column_name IS  { 'comment text' | NULL | '' }

Add or update or remove a comment for a column of a table or view

Example:

COMMENT ON COLUMN sys.comments.remark IS 'contains description text'

Note: By specifying IS NULL or IS '' you remove the comment for the column.
If the column (or table or view) is dropped, the associated comment is also removed.
This command is supported from release Mar2018 (11.29.3) onwards.
Note: it is not allowed or possible to add comments for columns of temporary tables in schema "tmp".

For details see: bugzilla/show_bug.cgi?id=6109#c5
See also: ALTER TABLE ADD COLUMN ALTER TABLE DROP COLUMN

Associated system table: sys.comments

COMMENT ON INDEX

   COMMENT ON INDEX [ schema_name . ] index_name IS  { 'comment text' | NULL | '' }

Add or update or remove a comment for an index

Example:

COMMENT ON INDEX mysch.article_id IS 'unique index of id key column of article table'

Note: By specifying IS NULL or IS '' you remove the comment for the index.
If the index is dropped, the associated comment is also removed.
This command is supported from release Mar2018 (11.29.3) onwards.
Note: it is not allowed or possible to add comments for indexes of temporary tables in schema "tmp".

For details see: bugzilla/show_bug.cgi?id=6109#c5
See also: CREATE INDEX DROP INDEX

Associated system table: sys.comments

COMMENT ON SEQUENCE

   COMMENT ON SEQUENCE [ schema_name . ] sequence_name IS  { 'comment text' | NULL | '' }

Add or update or remove a comment for a sequence

Example:

COMMENT ON SEQUENCE mysch.article_id_seq IS 'sequence for article id column'

Note: By specifying IS NULL or IS '' you remove the comment for the sequence.
If the sequence is dropped, the associated comment is also removed.
This command is supported from release Mar2018 (11.29.3) onwards.

For details see: bugzilla/show_bug.cgi?id=6109#c5
See also: CREATE SEQUENCE DROP SEQUENCE

Associated system table: sys.comments

COMMENT ON FUNCTION

   COMMENT ON FUNCTION [ schema_name . ] function_name IS  { 'comment text' | NULL | '' }

Add or update or remove a comment for a function

Example:

COMMENT ON FUNCTION sys.upper IS 'converts text into uppercase'

Note: By specifying IS NULL or IS '' you remove the comment for the function.
If the function is dropped, the associated comment is also removed.
This command is supported from release Mar2018 (11.29.3) onwards.

For details see: bugzilla/show_bug.cgi?id=6109#c5
See also: CREATE FUNCTION DROP FUNCTION

Associated system table: sys.comments

COMMENT ON PROCEDURE

   COMMENT ON PROCEDURE [ schema_name . ] procedure_name IS  { 'comment text' | NULL | '' }

Add or update or remove a comment for a procedure

Example:

COMMENT ON PROCEDURE mysch.load_articles IS 'proc which reloads the articles from external file article.csv'

Note: By specifying IS NULL or IS '' you remove the comment for the procedure.
If the procedure is dropped, the associated comment is also removed.
This command is supported from release Mar2018 (11.29.3) onwards.

For details see: bugzilla/show_bug.cgi?id=6109#c5
See also: CREATE PROCEDURE DROP PROCEDURE

Associated system table: sys.comments

COMMENT ON AGGREGATE

   COMMENT ON AGGREGATE [ schema_name . ] aggregate_name IS  { 'comment text' | NULL | '' }

Add or update or remove a comment for an aggregate function

Example:

COMMENT ON AGGREGATE sys.std_dev IS 'computes the standard deviation of a group of numeric values'

Note: By specifying IS NULL or IS '' you remove the comment for the aggregate function.
If the aggregate function is dropped, the associated comment is also removed.
This command is supported from release Mar2018 (11.29.3) onwards.

For details see: bugzilla/show_bug.cgi?id=6109#c5
See also: CREATE AGGREGATE LANGUAGE DROP AGGREGATE

Associated system table: sys.comments

COMMENT ON FILTER FUNCTION

   COMMENT ON FILTER FUNCTION [ schema_name . ] function_name IS  { 'comment text' | NULL | '' }

Add or update or remove a comment for a filter function

Example:

COMMENT ON FILTER FUNCTION sys."ilike"(clob, clob) IS 'case insensitive pattern matching';
COMMENT ON FILTER FUNCTION sys."ilike"(clob, clob, clob) IS 'case insensitive pattern matching with user specified escape character';

Note: By specifying IS NULL or IS '' you remove the comment for the filter function.
If the filter function is dropped, the associated comment is also removed.
This command is supported from release Mar2018 (11.29.3) onwards.

For details see: bugzilla/show_bug.cgi?id=6109#c5
See also: CREATE FILTER FUNCTION EXTERNAL DROP FILTER FUNCTION

Associated system table: sys.comments

COMMENT ON LOADER

   COMMENT ON LOADER [ schema_name . ] function_name IS  { 'comment text' | NULL | '' }

Add or update or remove a comment for a loader function

Example:

COMMENT ON LOADER mysch.load_xls_data IS 'custom loader to extract data from external xls file'

Note: By specifying IS NULL or IS '' you remove the comment for the loader function.
If the loader function is dropped, the associated comment is also removed.
This command is supported from release Mar2018 (11.29.3) onwards.

For details see: bugzilla/show_bug.cgi?id=6109#c5
See also: CREATE LOADER DROP LOADER

Associated system table: sys.comments

COMMENT ON WINDOW

   COMMENT ON WINDOW [ FUNCTION ] [ schema_name . ] window_name IS  { 'comment text' | NULL | '' }

Add or update or remove a comment for a window function

Example:

COMMENT ON WINDOW FUNCTION sys.stddev IS 'computes the standard deviation of a group of numeric values'

Note: By specifying IS NULL or IS '' you remove the comment for the window function.
If the window function is dropped, the associated comment is also removed.
This command is supported from release Jun2020 (11.37.7) onwards.

See also: CREATE WINDOW EXTERNAL DROP WINDOW

Associated system table: sys.comments

DECLARE

   DECLARE [ schema_name . ] variable_name [, ... ] datatype

Declared a new variable or list of variables of the same type

Example:

DECLARE ts1 timestamp;
SET ts1 = now();
SELECT ts1;
SELECT * FROM sys.var() WHERE name NOT IN (SELECT var_name FROM sys.var_values);

Note: The declared variable is not persistent. It will be lost after closing the connection or session.
The option to specify the schema name is supported from release Oct2020 (11.39.5) onwards.

For details see: Documentation/SQLreference/ProgrammingSQL/Variables
See also: SET SELECT

Associated system table: sys.var()

ALTER SEQUENCE

   ALTER SEQUENCE [ schema_name . ] sequence_name [ AS datatype  ]
    [ RESTART [WITH bigint# ] ]
    [ INCREMENT BY bigint# ]
    [ MINVALUE bigint# | NO MINVALUE ]
    [ MAXVALUE bigint# | NO MAXVALUE ]
    [ CACHE bigint# ]
    [ [ NO ] CYCLE ]

Change definition of a sequence generator

For details see: Documentation/SQLReference/DataTypes/SerialDatatypes
See also: DROP SEQUENCE CREATE SEQUENCE

Associated system table: sys.sequences

ALTER TABLE ADD COLUMN

   ALTER TABLE [ IF EXISTS ] [ schema_name . ] table_name
    ADD [ COLUMN ] column_name { data_type [ column_option ... ] | SERIAL | BIGSERIAL }

Add a column to a table

Note: The "IF EXISTS" option is supported from release Apr2019 (11.33.3) onwards.

For details see: Documentation/SQLreference/TableDefinitions/AlterStatement
See also: ALTER TABLE ADD CONSTRAINT ALTER TABLE ALTER COLUMN ALTER TABLE DROP COLUMN

Associated system table: sys.columns

ALTER TABLE ALTER COLUMN

   ALTER TABLE [ IF EXISTS ] [ schema_name . ] table_name
    ALTER [ COLUMN ] column_name { SET NULL | SET NOT NULL | SET DEFAULT value | DROP DEFAULT | SET STORAGE {string | NULL} }

Change column nullability or default value or storage

Note: Change of the data type of a column is not supported. Instead use command sequence:
  ALTER TABLE tbl ADD COLUMN new_column new_data_type;
  UPDATE tbl SET new_column = CONVERT(old_column, new_data_type);
  ALTER TABLE tbl DROP COLUMN old_column RESTRICT;
  ALTER TABLE tbl RENAME COLUMN new_column TO old_column;

The "IF EXISTS" option is supported from release Apr2019 (11.33.3) onwards.

For details see: Documentation/SQLreference/TableDefinitions/AlterStatement
See also: ALTER TABLE RENAME COLUMN   ALTER TABLE ADD COLUMN   ALTER TABLE DROP COLUMN   ALTER TABLE DROP CONSTRAINT

Associated system table: sys.columns

ALTER TABLE DROP COLUMN

   ALTER TABLE [ IF EXISTS ] [ schema_name . ] table_name
    DROP [ COLUMN ] column_name
    [ RESTRICT | CASCADE ]

Remove a column from a table

Note: You cannot drop a column if it is referenced (e.g. from a view, an index, a merge table, a trigger, a foreign key constraint, a function or procedure or another db object except comments). Use option CASCADE to specify to also drop those referencing objects.
The "IF EXISTS" option is supported from release Apr2019 (11.33.3) onwards.

For details see: Documentation/SQLreference/TableDefinitions/AlterStatement
See also: ALTER TABLE DROP CONSTRAINT ALTER TABLE ALTER COLUMN DROP TABLE

Associated system table: sys.columns

ALTER TABLE ADD CONSTRAINT

   ALTER TABLE [ IF EXISTS ] [ schema_name . ] table_name
    ADD [ CONSTRAINT constraint_name ]
    { PRIMARY KEY ( column_name [ , column_name ] [, ... ] )
    |      UNIQUE ( column_name [ , column_name ] [, ... ] )
    | FOREIGN KEY ( column_name [ , column_name ] [, ... ] ) REFERENCES [ schema_name . ] table_name
        [ ( column_name [ , column_name ] [, ... ] ) ]   [ match_options ]   [ ref_actions ]
    }

Add a table constraint to a table

Examples:

ALTER TABLE "tblnm" ADD PRIMARY KEY ("C1_id");
ALTER TABLE if exists "schnm"."tblnm" ADD CONSTRAINT "tblnm_uc" UNIQUE ("name", "desc");
ALTER TABLE "tblnm" ADD CONSTRAINT "tblnm_fk1" FOREIGN KEY ("f_id", "f_seq")
                        REFERENCES "schnm2"."fun" ("id", "seq")  ON UPDATE RESTRICT  ON DELETE CASCADE;

Note: Only one PRIMARY KEY constraint can be defined per table. When a primary key constraint is added, all the primary key columns will become NOT NULLable implicitly.
If no constraint_name is specified a constraint_name will be composed implicitly from the table name, column name(s) and constraint type.
The CHECK constraint is not (yet) supported.
The "IF EXISTS" option is supported from release Apr2019 (11.33.3) onwards.

For details see: Documentation/SQLreference/TableDefinitions/AlterStatement
See also: ALTER TABLE DROP CONSTRAINT ALTER TABLE ALTER COLUMN

Associated system table: sys.keys

ALTER TABLE DROP CONSTRAINT

   ALTER TABLE [ IF EXISTS ] [ schema_name . ] table_name
    DROP CONSTRAINT  constraint_name
    [ RESTRICT | CASCADE ]

Remove a table/column constraint from a table

Note: You cannot drop a constraint if it is referenced (e.g. from a foreign key constraint). Use option CASCADE to specify to also drop those referencing objects.
To remove a NOT NULL column constraint use: ALTER TABLE sch.tbl ALTER COLUMN column_name SET NULL.
The "IF EXISTS" option is supported from release Apr2019 (11.33.3) onwards.

For details see: Documentation/SQLreference/TableDefinitions/AlterStatement
See also: ALTER TABLE ADD CONSTRAINT ALTER TABLE ALTER COLUMN

Associated system table: sys.keys

ALTER TABLE RENAME TO

   ALTER TABLE [ IF EXISTS ] [ schema_name . ] table_name
    RENAME TO  new_table_name

Change the name of a table

Note: It is only allowed to change the name of a table if no objects exists which depends on the table name, such as foreign key constraints, views, triggers, indices, functions, procedures, etc.
To move a table to s different schema use command: ALTER TABLE ... SET SCHEMA ...
This command is supported from release Apr2019 (11.33.3) onwards.

For details see: Documentation/SQLreference/TableDefinitions/AlterStatement
See also: CREATE TABLE AS ALTER TABLE SET SCHEMA

Associated system table: sys.tables

ALTER TABLE RENAME COLUMN

   ALTER TABLE [ IF EXISTS ] [ schema_name . ] table_name
    RENAME [ COLUMN ]  column_name TO  new_column_name

Change the name of a column

Note: It is only allowed to change the name of a column if no objects exists which depends on the column name, such as constraints, views, functions, etc.
This command is supported from release Apr2019 (11.33.3) onwards.

For details see: Documentation/SQLreference/TableDefinitions/AlterStatement
See also: ALTER TABLE ADD COLUMN ALTER TABLE DROP COLUMN ALTER TABLE DROP CONSTRAINT

Associated system table: sys.columns

ALTER TABLE SET SCHEMA

   ALTER TABLE [ IF EXISTS ] [ schema_name . ] table_name
    SET SCHEMA  new_schema_name

Change the schema name of a table

Note: It is only allowed to change the schema name of a table if no objects exists which depends on the table name, such as foreign key constraints, views, triggers, indices, functions, procedures, etc.
This command is supported from release Apr2019 (11.33.3) onwards.

For details see: Documentation/SQLreference/TableDefinitions/AlterStatement
See also: CREATE TABLE AS ALTER TABLE RENAME TO

Associated system table: sys.tables

ALTER TABLE ADD TABLE

   ALTER TABLE [ IF EXISTS ] [ schema_name . ] merge_table_name
    ADD TABLE [ schema_name . ] table_name
    [ AS PARTITION  partition_spec ]

partition_spec:

      IN ( expression [ , expression ] [, ... ] )  [ WITH NULL VALUES ]
    | FROM  { RANGE MINVALUE | expression }  TO  { RANGE MAXVALUE | expression }  [ WITH NULL VALUES ]
    | FOR NULL VALUES

Add a table reference to a merge table set optionally with an partitioning specification

Note: This command is MonetDB specific.
Limitations: The added table must have the same column definitions and layout as the merge table.
Only user defined tables and merge tables can be added to a merge table set.
When the merge table was created with a PARTITION BY clause, the AS PARTITION clause must be specified.
The "AS PARTITION" option is supported from release Apr2019 (11.33.3) onwards.
The "IF EXISTS" option is supported from release Apr2019 (11.33.3) onwards.

For details see: Documentation/ServerAdministration/DistributedQueryProcessing/DataPartitioning and updatable-merge-tables
and: Documentation/SQLreference/TableDefinitions/AlterStatement
See also: CREATE MERGE TABLE ALTER TABLE SET TABLE ALTER TABLE DROP TABLE

Associated system tables: sys.tables where type = 3; sys.range_partitions; sys.value_partitions;

ALTER TABLE SET TABLE

   ALTER TABLE [ IF EXISTS ] [ schema_name . ] merge_table_name
    SET TABLE [ schema_name . ] table_name
    AS PARTITION  partition_spec

partition_spec:

      IN ( expression [ , expression ] [, ... ] )  [ WITH NULL VALUES ]
    | FROM  { RANGE MINVALUE | expression }  TO  { RANGE MAXVALUE | expression }  [ WITH NULL VALUES ]
    | FOR NULL VALUES

Change the partitioning specification of a partition table

Note: This command is MonetDB specific. It is supported from release Apr2019 (11.33.3) onwards.

For details see: updatable-merge-tables and Documentation/SQLreference/TableDefinitions/AlterStatement
See also: CREATE MERGE TABLE ALTER TABLE ADD TABLE ALTER TABLE DROP TABLE

Associated system tables: sys.tables where type = 3; sys.range_partitions; sys.value_partitions;

ALTER TABLE DROP TABLE

   ALTER TABLE [ IF EXISTS ] [ schema_name . ] merge_table_name
    DROP TABLE [ schema_name . ] table_name
    [ RESTRICT | CASCADE ]

Remove a table reference from a merge table set. The partition table itself will not be dropped.

Note: This command is MonetDB specific.
The "IF EXISTS" option is supported from release Apr2019 (11.33.3) onwards.

For details see: Documentation/ServerAdministration/DistributedQueryProcessing/DataPartitioning
and: Documentation/SQLreference/TableDefinitions/AlterStatement
See also: CREATE MERGE TABLE ALTER TABLE ADD TABLE

Associated system tables: sys.tables where type = 3; sys.range_partitions; sys.value_partitions;

ALTER TABLE SET INSERT ONLY

   ALTER TABLE [ IF EXISTS ] [ schema_name . ] table_name
    SET INSERT ONLY

Change access of a table to allow only inserts

Note: The "IF EXISTS" option is supported from release Apr2019 (11.33.3) onwards.

For details see: Documentation/SQLreference/TableDefinitions/AlterStatement
See also: ALTER TABLE SET READ ONLY ALTER TABLE SET READ WRITE

Associated system table: sys.tables where type in (0, 3, 4, 5, 6)

ALTER TABLE SET READ ONLY

   ALTER TABLE [ IF EXISTS ] [ schema_name . ] table_name
    SET READ ONLY

Change access of a table to allow only reads (select queries)

Note: The "IF EXISTS" option is supported from release Apr2019 (11.33.3) onwards.

For details see: Documentation/SQLreference/TableDefinitions/AlterStatement
See also: ALTER TABLE SET READ WRITE ALTER TABLE SET INSERT ONLY

Associated system table: sys.tables

ALTER TABLE SET READ WRITE

   ALTER TABLE [ IF EXISTS ] [ schema_name . ] table_name
    SET READ WRITE

Restore access of a table to allow inserts, updates, deletes and reads

Note: The "IF EXISTS" option is supported from release Apr2019 (11.33.3) onwards.

For details see: Documentation/SQLreference/TableDefinitions/AlterStatement
See also: ALTER TABLE SET READ ONLY ALTER TABLE SET INSERT ONLY

Associated system table: sys.tables where type in (0, 3, 4, 5, 6)

DROP SCHEMA

   DROP SCHEMA [ IF EXISTS ] schema_name
    [ RESTRICT | CASCADE ]

Remove a database schema, potentially including all its dependent objects

Example:

DROP SCHEMA IF EXISTS "my_project" RESTRICT;

Note: You cannot drop a schema if it contains objects (e.g. tables, views, functions or procedures, except comments). Use option CASCADE to specify to also drop those referencing objects. The default behavior is RESTRICT.
System schemas (sys, tmp, profiler, json) can not be dropped as they are needed by the system.
Warning: in releases before Mar2018 (11.29.3) the default behavior was CASCADE always. The option RESTRICT was recognised but ignored without warning. This has been corrected in release Mar2018.
The "IF EXISTS" option is supported from release Jul2017 (11.27.1) onwards.

For details see: Documentation/SQLreference/Schema
See also: CREATE SCHEMA CURRENT_SCHEMA SET SCHEMA

Associated system table: sys.schemas

DROP SEQUENCE

   DROP SEQUENCE [ schema_name . ] sequence_name

Remove an existing sequence generator

Note: You cannot drop a sequence if it is referenced from another object e.g. from a column, a function or procedure.

For details see: Documentation/SQLReference/DataTypes/SerialDatatypes
and: Documentation/SQLReference/DataDefinition/DropStatement
See also: ALTER SEQUENCE CREATE SEQUENCE

Associated system table: sys.sequences

DROP TABLE

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

Remove a table, potentially including all its dependent objects

Example:

DROP TABLE IF EXISTS "my_project"."my_Table" CASCADE;

Note: You cannot drop a table if it is referenced from a view, a merge table, a trigger, a foreign key constraint, a function, a procedure. Use option CASCADE to specify to also drop those referencing objects. Objects which are part of the table such as columns, pkey/unique/fkey constraints, indices, comments, table_partitions, range_partitions and value_partitions are removed also when a table is dropped.
Warning: When dropping a merge table, also all the related table partitioning information (in sys.table_partitions, sys.range_partitions and sys.value_partitions) is removed.
System tables can also not be dropped as they are needed by the system.
The "IF EXISTS" option is supported from release Jul2017 (11.27.1) onwards.

For details see: Documentation/ServerAdministration/DistributedQueryProcessing/DataPartitioning
and: Documentation/SQLReference/DataDefinition/DropStatement
See also: CREATE TABLE

Associated system tables: sys.tables; sys.columns; sys.comments; sys.idxs; sys.table_partitions; sys.range_partitions; sys.value_partitions; sys.dependencies_vw

DROP INDEX

   DROP INDEX [ schema_name . ] index_name

Remove an existing table index

Example:

DROP TABLE "my_project"."my_Index";

For details see: Documentation/SQLReference/DataDefinition/DropStatement
See also: CREATE INDEX CREATE ORDERED INDEX CREATE IMPRINTS INDEX

Associated system table: sys.idxs

DROP VIEW

   DROP VIEW [ IF EXISTS ] [ schema_name . ] view_name
    [ RESTRICT | CASCADE ]

Remove a view

Example:

DROP VIEW IF EXISTS "my_project"."my_View" CASCADE;

Note: You cannot drop a view if it is referenced (e.g. from another view, a function or procedure or another db object except comments). Use option CASCADE to specify to also drop those referencing objects.
System views can also not be dropped as they are needed by the system.
The "IF EXISTS" option is supported from release Jul2017 (11.27.1) onwards.

For details see: Documentation/SQLReference/DataDefinition/DropStatement
See also: CREATE VIEW

Associated system table: sys.tables where type = 1

DROP AGGREGATE

   DROP AGGREGATE [ FUNCTION ] [ IF EXISTS ] [ schema_name . ] aggregate_function_name
    [ ( [ arg1_type [ , arg2_type ] [, ... ] ] ) ]
    [ RESTRICT | CASCADE ]

Remove a specific user-defined aggregation function. If multiple aggregate functions exist with the same name, supply the full signature.

Example:

DROP AGGREGATE testaggr(int, double);

Note: This command is MonetDB specific.
Use option CASCADE to specify to also drop referencing objects.
System aggregates can not be dropped as they are part by the system.
The "IF EXISTS" option is supported from release Mar2018 (11.29.3) onwards.

For details see: Documentation/SQLReference/DataDefinition/DropStatement
See also: DROP ALL AGGREGATE CREATE AGGREGATE LANGUAGE CREATE AGGREGATE EXTERNAL

Associated system table: sys.functions where type = 3

DROP ALL AGGREGATE

   DROP ALL AGGREGATE [ FUNCTION ] [ schema_name . ] aggregate_function_name
    [ RESTRICT | CASCADE ]

Remove all user-defined aggregation functions which share the same name but have different signatures.

Example:

DROP ALL AGGREGATE testaggr;

Note: This command is MonetDB specific.
Use option CASCADE to specify to also drop referencing objects.

For details see: Documentation/SQLReference/DataDefinition/DropStatement
See also: DROP AGGREGATE CREATE AGGREGATE LANGUAGE CREATE AGGREGATE EXTERNAL

Associated system table: sys.functions where type = 3

DROP FUNCTION

   DROP FUNCTION [ IF EXISTS ] [ schema_name . ] function_name
    [ ( [ arg1_type [ , arg2_type ] [, ... ] ] ) ]
    [ RESTRICT | CASCADE ]

Remove a specific user-defined function. If multiple functions exist with the same name, supply the full signature.

Example:

DROP FUNCTION testfun(int, double);

Note: Use option CASCADE to specify to also drop referencing objects.
System functions can not be dropped as they are part by the system.
The "IF EXISTS" option is supported from release Mar2018 (11.29.3) onwards.

For details see: Documentation/SQLReference/DataDefinition/DropStatement
See also: DROP ALL FUNCTION CREATE FUNCTION CREATE FUNCTION LANGUAGE

Associated system table: sys.functions where type in (1,5)

DROP ALL FUNCTION

   DROP ALL FUNCTION [ schema_name . ] function_name
    [ RESTRICT | CASCADE ]

Remove all user-defined functions which share the same name but have different signatures.

Example:

DROP ALL FUNCTION testfun;

Note: This command is MonetDB specific.
Use option CASCADE to specify to also drop referencing objects.

For details see: Documentation/SQLReference/DataDefinition/DropStatement
See also: DROP FUNCTION CREATE FUNCTION CREATE FUNCTION LANGUAGE

Associated system table: sys.functions where type in (1,5)

DROP FILTER FUNCTION

   DROP FILTER [ FUNCTION ] [ IF EXISTS ] [ schema_name . ] function_name
    [ ( [ arg1_type [ , arg2_type ] [, ... ] ] ) ]
    [ RESTRICT | CASCADE ]

Remove a specific user-defined filter function. If multiple filter functions exist with the same name, supply the full signature.

Example:

DROP FILTER FUNCTION my_like(string, varchar(100));

Note: This command is MonetDB specific.
System filter functions can not be dropped as they are part by the system.
Use option CASCADE to specify to also drop referencing objects.
The "IF EXISTS" option is supported from release Mar2018 (11.29.3) onwards.

For details see: Documentation/SQLReference/DataDefinition/DropStatement
See also: DROP ALL FILTER FUNCTION CREATE FILTER FUNCTION EXTERNAL

Associated system table: sys.functions where type = 4

DROP ALL FILTER FUNCTION

   DROP ALL FILTER [ FUNCTION ] [ schema_name . ] function_name
    [ RESTRICT | CASCADE ]

Remove all user-defined filter functions which share the same name but have different signatures.

Example:

DROP ALL FILTER FUNCTION my_like;

Note: This command is MonetDB specific.
Use option CASCADE to specify to also drop referencing objects.

For details see: Documentation/SQLReference/DataDefinition/DropStatement
See also: DROP FILTER FUNCTION CREATE FILTER FUNCTION EXTERNAL

Associated system table: sys.functions where type = 4

DROP LOADER

   DROP LOADER [ FUNCTION ] [ IF EXISTS ] [ schema_name . ] function_name
    [ ( [ arg1_type [ , arg2_type ] [, ... ] ] ) ]
    [ RESTRICT | CASCADE ]

Remove a specific user-defined loader function. If multiple loader functions exist with the same name, supply the full signature.

Note: This command is MonetDB specific. It is introduced in release Dec2016 (11.25.3).
The "IF EXISTS" option is supported from release Mar2018 (11.29.3) onwards.

For details see: blog/monetdbpython-loader-functions
See also: DROP ALL LOADER CREATE LOADER CREATE FUNCTION LANGUAGE

Associated system table: sys.functions where type = 7 and language > 2

DROP ALL LOADER

   DROP ALL LOADER [ FUNCTION ] [ schema_name . ] function_name
    [ RESTRICT | CASCADE ]

Remove all user-defined loader functions which share the same name but have different signatures.

Example:

DROP ALL LOADER my_ods_loader;

Note: This command is MonetDB specific.
Use option CASCADE to specify to also drop referencing objects.

For details see: Documentation/SQLReference/DataDefinition/DropStatement
See also: DROP LOADER CREATE LOADER

Associated system table: sys.functions where type = 7 and language > 2

DROP PROCEDURE

   DROP PROCEDURE [ IF EXISTS ] [ schema_name . ] procedure_name
    [ ( [ arg1_type [ , arg2_type ] [, ... ] ] ) ]
    [ RESTRICT | CASCADE ]

Remove a specific user-defined procedure. If multiple procedures exist with the same name, supply the full signature.

Example:

DROP PROCEDURE testproc(int, double);

Note: Use option CASCADE to specify to also drop referencing objects.
System procedures can not be dropped as they are part by the system.
The "IF EXISTS" option is supported from release Mar2018 (11.29.3) onwards.

For details see: Documentation/SQLReference/DataDefinition/DropStatement
See also: DROP ALL PROCEDURE CREATE PROCEDURE CREATE PROCEDURE EXTERNAL

Associated system table: sys.functions where type = 2

DROP ALL PROCEDURE

   DROP ALL PROCEDURE [ schema_name . ] procedure_name
    [ RESTRICT | CASCADE ]

Remove all user-defined procedures which share the same name but have different signatures.

Example:

DROP ALL PROCEDURE testproc;

Note: This command is MonetDB specific.
Use option CASCADE to specify to also drop referencing objects.

For details see: Documentation/SQLReference/DataDefinition/DropStatement
See also: DROP PROCEDURE CREATE PROCEDURE CREATE PROCEDURE EXTERNAL

Associated system table: sys.functions where type = 2

DROP WINDOW

   DROP WINDOW [ FUNCTION ] [ IF EXISTS ] [ schema_name . ] window_function_name
    [ ( [ arg1_type [ , arg2_type ] [, ... ] ] ) ]
    [ RESTRICT | CASCADE ]

Remove a specific user-defined window function. If multiple window functions exist with the same name, supply the full signature.

Example:

DROP WINDOW stddev(bigint);

Note: This command is MonetDB specific and supported from release Jun2020 (11.37.7) onwards.
Use option CASCADE to specify to also drop referencing objects.
System window functions can not be dropped as they are part by the system.

For details see: Documentation/SQLReference/DataDefinition/DropStatement
See also: DROP ALL WINDOW CREATE WINDOW EXTERNAL

Associated system table: sys.functions where type = 6 and language = 1 and not system

DROP ALL WINDOW

   DROP ALL WINDOW [ FUNCTION ] [ schema_name . ] window_function_name
    [ RESTRICT | CASCADE ]

Remove all user-defined window functions which share the same name but have different signatures.

Example:

DROP ALL WINDOW my_window_func;

Note: This command is MonetDB specific and supported from release Jun2020 (11.37.7) onwards.
Use option CASCADE to specify to also drop referencing objects.

For details see: Documentation/SQLReference/DataDefinition/DropStatement
See also: DROP WINDOW CREATE WINDOW EXTERNAL

Associated system table: sys.functions where type = 6 and language = 1 and not system

DROP TYPE

   DROP TYPE [ schema_name . ] type_name
    [ RESTRICT | CASCADE ]

Remove a user-defined type

For details see: Documentation/SQLReference/DataTypes/UserDefinedTypes
See also: CREATE TYPE EXTERNAL

Associated system table: sys.types

DROP TRIGGER

   DROP TRIGGER [ IF EXISTS ] [ schema_name . ] trigger_name

Remove an existing trigger

Note: The "IF EXISTS" option is supported from release Mar2018 (11.29.3) onwards.

For details see: Documentation/SQLReference/DataDefinition/DropStatement
See also: CREATE TRIGGER

Associated system table: sys.triggers

SELECT

   SELECT selection_list
    [ FROM table_view_subselect_function_list [ [ AS ] table_alias ] ]
    [ WINDOW window_definition_list ]
    [ WHERE condition_list ]
    [ GROUP BY grouping_list ]
    [ HAVING group conditon_list ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] [ CORRESPONDING [ BY '(' column_ref_commalist ')' ] ] SELECT_query ]
    [ ORDER BY ordering_list [ ASC | DESC ]  ]
    [ LIMIT nr_of_rows ]
    [ OFFSET row_nr ]
    [ SAMPLE sample_size [ SEED integer ] ]

Execute a database query and return the resultset data rows

Example:

-- query to list all non-system tables:
SELECT tbl.id, table_type_name, sch.name AS "Schema", tbl.name as "Table"
    , (select count(*) from sys.columns where table_id = tbl.id) as nr_of_columns
  FROM sys."tables" as tbl
 INNER JOIN sys.table_types ON tbl.type = table_type_id
 INNER JOIN sys."schemas" as sch ON tbl.schema_id = sch.id
 WHERE NOT tbl.system	-- exclude system tables and views
   AND table_type_name <> 'VIEW'	-- exclude user views
 ORDER by "Schema", "Table";

-- example using window functions
SELECT id, emp_name, dep_name
     , ROW_NUMBER()           OVER (PARTITION BY dep_name ORDER BY id) AS row_number_in_frame
     , NTH_VALUE(emp_name, 2) OVER (PARTITION BY dep_name ORDER BY id) AS second_row_in_frame
     , LEAD(emp_name, 2)      OVER (PARTITION BY dep_name ORDER BY id) AS two_rows_ahead
     , SUM(salary)  OVER (PARTITION BY dep_name ORDER BY salary  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sum_over_1or2or3_rows
     , SUM(salary)  OVER (PARTITION BY dep_name ORDER BY salary  GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_over_groups
     , SUM(salary)  OVER (PARTITION BY dep_name ORDER BY salary  RANGE BETWEEN 100.0 PRECEDING AND 50.0 FOLLOWING) AS sum_over_range
  FROM employee
 ORDER BY dep_name, id;

-- example using (values(tuple),(tuple),...)
select *
 from ( values(0,'cero'),(1,'uno'),(2,'dos'),(3,'tres'),(4,'cuatro'),(5,'cinco'),(6,'seis'),(7,'siete'),(8,'ocho'),(9,'nueve'),(10,'diez') ) as nr_es(nr, nm)

Note: It is also possible to select data without a FROM-clause, such as: SELECT CURRENT_DATE, (1+2) * 3, pi();
The "WINDOW window_definition_list" option and "SEED integer" option are supported from release Apr2019 (11.33.3) onwards.

For details see: Documentation/SQLReference/DataManipulation/TableExpressions , Documentation/SQLReference/FunctionsAndOperators/WindowFunctions ,
Documentation/ServerAdministration/Sampling and wiki/Structured_Query_Language/Window_functions

Associated system table: sys.queue and sys.querylog_history

SELECT INTO

   SELECT selection_list
    INTO variable_list
    [ FROM table_view_subselect_function_list [ [ AS ] table_alias ] ]
    [ WINDOW window_definition_list ]
    [ WHERE condition_list ]
    [ GROUP BY grouping_list ]
    [ HAVING group conditon_list ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] [ CORRESPONDING [ BY '(' column_ref_commalist ')' ] ] SELECT_query ]
    [ ORDER BY ordering_list [ ASC | DESC ]  ]
    [ LIMIT nr_of_rows ]
    [ OFFSET row_nr ]
    [ SAMPLE sample_size [ SEED integer ] ]

Execute a database query and store the resultset data in the named variables

Note: The "WINDOW window_definition_list" option and "SEED integer" option are supported from release Apr2019 (11.33.3) onwards.

For details see: Documentation/SQLReference/DataManipulation/TableExpressions
and: Documentation/ServerAdministration/Monitoring

Associated system table: sys.queue and sys.querylog_history

WITH SELECT

   WITH cte_alias AS SELECT_query
     [, cte_alias AS SELECT_query] [, ... ]
   SELECT selection_list
    [ INTO variable_list ]
    [ FROM cte_alias_table_view_subselect_function_list [ [ AS ] table_alias ] ]
    [ WINDOW window_definition_list ]
    [ WHERE condition_list ]
    [ GROUP BY grouping_list ]
    [ HAVING group conditon_list ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] [ CORRESPONDING [ BY '(' column_ref_commalist ')' ] ] SELECT_query ]
    [ ORDER BY ordering_list [ ASC | DESC ]  ]
    [ LIMIT nr_of_rows ]
    [ OFFSET row_nr ]
    [ SAMPLE sample_size [ SEED integer ] ]

Execute a database query using the results of the queries defined in the WITH clause and return the resultset data rows

Note: Recursive queries are NOT supported yet. This feature request is logged in bugzilla.
The "WINDOW window_definition_list" option and "SEED integer" option are supported from release Apr2019 (11.33.3) onwards.

For details see: Documentation/SQLReference/DataManipulation/TableExpressions

Associated system table: sys.queue and sys.querylog_history

INSERT VALUES

   INSERT INTO [ schema_name . ] table_name
     [ ( column_name [, column_name] [, ... ] ) ]
    VALUES ( values_list )   [, ( values_list ) [, ... ] ]

Add data row(s) to an existing table

Example:

INSERT INTO "mySch"."myTab" (id, name) VALUES (1, 'a'), (2, 'b'), (3, 'c');

Note: It is also possible to add multiple rows of values in one insert statement by adding extra ", (values_list)" parts.

For details see: Documentation/SQLReference/DataManipulation/TableUpdates
and: Documentation/ServerAdministration/LoadingBulkData

Associated system table: sys.querylog_history

INSERT SELECT

     [ WITH cte_alias AS SELECT_query [, cte_alias AS SELECT_query] [, ... ] ]
   INSERT INTO [ schema_name . ] table_name
     [ ( column_name [, column_name] [, ... ] ) ]
     SELECT_query

Execute a database query and add the resultset data rows into the existing table

Note: The "WITH cte_alias AS SELECT_query" option is supported from release Apr2019 (11.33.3) onwards.

For details see: Documentation/SQLReference/DataManipulation/TableUpdates

Associated system table: sys.querylog_history

UPDATE

     [ WITH cte_alias AS SELECT_query [, cte_alias AS SELECT_query] [, ... ] ]
   UPDATE [ schema_name . ] table_name [ [ AS ] table_alias ]
     SET column_name = expression_value
        [ , column_name = expression_value ] [, ... ]
     [ FROM cte_alias_table_view_subselect_function_list [ [ AS ] table_alias ] ]
     [ WHERE condition_expression ]

Change values of specific column(s) and row(s) of an existing table. If the optional WHERE clause is specified only the rows which match the condition(s) are changed. Table alias option is available since Apr2019 release.

Example:

UPDATE employee SET salary = salary * 1.07, bonus = 1200 WHERE id = 12345;

Note: The "WITH cte_alias AS SELECT_query" option is supported from release Apr2019 (11.33.3) onwards.

For details see: Documentation/SQLReference/DataManipulation/TableUpdates

Associated system table: sys.querylog_history

MERGE

     [ WITH cte_alias AS SELECT_query [, cte_alias AS SELECT_query] [, ... ] ]
   MERGE INTO [ schema_name . ] table_name [ [ AS ] target_alias ]
    USING table_or_cte_ref [ [ AS ] source_alias ]
       ON merge_search_condition
     WHEN merge_action
     [ WHEN merge_action ]

merge_action:

     NOT MATCHED THEN INSERT [ (column1 [, column2 ...] ) ] [ { VALUES (value1 [, value2 ...] ) | DEFAULT VALUES } ]
   | MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ] [, ... ]
   | MATCHED THEN DELETE

The MERGE INTO command is used to make changes in one table (target) based on values matched from another (source). It can be used to combine insert and update or insert and delete operations into one command, instead of multiple INSERT and UPDATE/DELETE commands. You must specify a merge search condition which determines whether a record will be inserted or updated/deleted.

Example:

MERGE INTO ProductTarget T
     USING ProductSource S
        ON S.ProductID = T.ProductID
      WHEN MATCHED     THEN UPDATE SET Name = S.Name, ProductNumber = S.ProductNumber, Color = S.Color
      WHEN NOT MATCHED THEN INSERT (ProductID, Name, ProductNumber, Color)
                            VALUES (S.ProductID, S.Name, S.ProductNumber, S.Color);

Note: This SQL:2003 command is supported from release Apr2019 (11.33.3) onwards.
Limitations: Multiple WHEN MATCHED clauses or multiply WHEN NOT MATCHED clauses in a merge command are not allowed.
Also WHEN MATCHED AND condition THEN ... syntax is not supported.

For details see: merge_statements_now_supported and en.wikipedia.org/wiki/Merge_(SQL)

Associated system table: sys.querylog_history

DELETE

     [ WITH cte_alias AS SELECT_query [, cte_alias AS SELECT_query] [, ... ] ]
   DELETE FROM [ schema_name . ] table_name [ [ AS ] table_alias ]
     [ WHERE condition_expression ]

Remove data row(s) from an existing table. If the optional WHERE clause is specified only the rows which match the condition(s) are removed. Table alias option is available since Apr2019 release.

Example:

DELETE FROM web.log WHERE "timestamp" <= '2016-12-23 23:59:59';

Note: The "WITH cte_alias AS SELECT_query" option is supported from release Apr2019 (11.33.3) onwards.

For details see: Documentation/SQLreference/Updates
See also: TRUNCATE

Associated system table: sys.querylog_history

TRUNCATE

   TRUNCATE [ TABLE ] [ schema_name . ] table_name
    [ CONTINUE IDENTITY | RESTART IDENTITY ]
    [ RESTRICT | CASCADE ]

Remove all data row(s) from an existing table quickly

Example:

TRUNCATE TABLE mysch.imp_article CONTINUE IDENTITY CASCADE;

Note: The option RESTART IDENTITY can be used to reset the sequence start value of an identity column for new data inserts after the truncate. Default behavior is to CONTINUE IDENTITY sequence numbering.
The CASCADE option instructs to truncate referencing table(s) also if the referencing table(s) have foreign key references to this table. The default behavior is RESTRICT.
This command is supported from release Mar2018 (11.29.3) onwards.
Note: it is possible to use TRUNCATE statements in a transaction and thus to rollback the effects of a truncate.

For details see: Documentation/SQLReference/DataManipulation/TableUpdates
See also: DELETE

Associated system table: sys.querylog_history

CALL

   CALL [ schema_name . ] procedure_name ( [ arg1_val [ , arg2_val ] [, ... ] ] )

Execute a stored procedure

Example:

CALL sys."settimeout"(3000);
CALL sys.hot_snapshot(R'D:\MonetDB\Backups\demodb.tar.gz');

Note: The current user must have EXECUTE privilege for the called procedure.

For details see: Documentation/SQLreference/Flowofcontrol
See also: CREATE PROCEDURE GRANT PRIVILEGES

Associated system table: sys.querylog_calls

PREPARE

   PREPARE SQL_DML_statement-with-optional-question_mark-parameter-markers

Compiles a SQL statement into its execution plan. This is useful for statements which need to be executed many times but with different values each time, such as an INSERT or UPDATE or SELECT query.

Example:

PREPARE INSERT INTO towns (name, country, citizens) VALUES (?, ?, ?);

-- to find out which prepared statement id is assigned, run query:
SELECT statementid, statement, created FROM sys.prepared_statements;

EXECUTE 5('Amsterdam', 'NL', 856124);
EXECUTE 5('Berlin', 'DE', 3715930);


PREPARE SELECT id, name, schema_id FROM tables WHERE name like ?;

-- to find out which prepared statement id is assigned, run query:
SELECT statementid, statement, created FROM sys.prepared_statements
 WHERE statement ILIKE 'PREPARE SELECT id, name, schema_id FROM tables WHERE name like ?%';

EXECUTE 7( '%type%' );
EXECUTE 7('%id%');

DEALLOCATE 7;
DEALLOCATE ALL;

Note: You can prepare any DML command, such as SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, etc.
The prepared statement will be given a system identifier number which can be used in the EXECUTE and DEALLOCATE commands.
Instead of keyword PREPARE you may also use PREP.

For details see: Documentation/SQLreference/RuntimeFeatures/PrepareExec
and: Documentation/ServerAdministration/LoadingBulkData
See also: EXECUTE and DEALLOCATE

Associated system tables: sys.prepared_statements and sys.prepared_statements_args

EXECUTE

   EXECUTE prepared-SQL-id ( [ parm1_val [ , parm2_val ] [, ... ] ] )

Execute the prepared statement with specific parameter values

Example:

-- first find out which prepared statement id is assigned, run query:
SELECT statementid, statement, created FROM sys.prepared_statements;

EXECUTE 5('Amsterdam', 'NL', 856124);

EXECUTE 19('c-data');

Note: Instead of keyword EXECUTE you may also use EXEC.
WARNING: when an error occurs in the user session, all the user prepared statement(s) are removed !!

For details see: Documentation/SQLreference/RuntimeFeatures/PrepareExec
See also: PREPARE and DEALLOCATE

Associated system tables: sys.prepared_statements and sys.prepared_statements_args

DEALLOCATE

   DEALLOCATE [ PREPARE ] { prepared-SQL-id | ALL }

Close a specific or all prepared statements created in this user session

Example:

-- first find out which prepared statement id's exist, run query:
SELECT statementid, statement, created FROM sys.prepared_statements;

DEALLOCATE PREPARE 65;
DEALLOCATE ALL;

Note: This command is supported from release Jun2020 (11.37.7) onwards.

For details see: Documentation/SQLreference/RuntimeFeatures/PrepareExec
See also: PREPARE

Associated system table: sys.prepared_statements

SET

   SET [ schema_name . ] variable_name = new_value_expression

Change the value of a declared variable

Note: The option to specify the schema name is supported from release Oct2020 (11.39.5) onwards.

For details see: Documentation/SQLreference/ProgrammingSQL/Variables
See also: DECLARE SELECT

Associated system table: sys.var()

SET CURRENT_TIMEZONE

   SET CURRENT_TIMEZONE = minutes

Change the current timezone offset from GMT

Example:

SELECT CURRENT_TIMEZONE;
SET CURRENT_TIMEZONE = 3600;

For details see: Documentation/SQLreference/ProgrammingSQL/Variables
See also: CURRENT_TIMEZONE

Associated system table: sys.var_values

SET TIME ZONE

   SET TIME ZONE { LOCAL | INTERVAL 'hh:mi' HOUR TO MINUTE }

Change the current timezone

Example:

SELECT CURRENT_TIMEZONE;
SET TIME ZONE LOCAL;
SELECT CURRENT_TIMEZONE;
SET TIME ZONE INTERVAL '+02:00' HOUR TO MINUTE;
SET TIME ZONE INTERVAL '3' HOUR;
SET TIME ZONE INTERVAL '240' MINUTE;
SET TIME ZONE INTERVAL '-3600' SECOND;

For details see: Documentation/SQLreference/ProgrammingSQL/Variables Documentation/SQLReference/DataTypes/TemporalTypes

CREATE ROLE

   CREATE ROLE role_name
    [ WITH ADMIN grantor ]

Define a new role. You can use the role to grant privileges and next grant a role (or multiple roles) to specific users. This eases the maintenance of specifying privileges to new users or changing privileges for a set of users which have the same role.

Example:

CREATE ROLE controller;

For details see: Documentation/SQLReference/DataDefinition/Privileges/Roles
and: Documentation/SQLReference/DataDefinition/Privileges
See also: DROP ROLE GRANT PRIVILEGES GRANT ROLE SET ROLE

Associated system table: sys.roles and sys.auths

CREATE USER

   CREATE USER user_login_name
    WITH [ ENCRYPTED | UNENCRYPTED ] PASSWORD pass_phrase
    NAME full_name
    SCHEMA schema_name

Define a new database user account. Only monetdb administrator may create new database users.

Example:

CREATE USER "Donald" WITH UNENCRYPTED PASSWORD 'Secret' NAME 'Donald Duck' SCHEMA "app1";

For details see: Documentation/SQLReference/DataDefinition/Privileges/Users
and: Documentation/SQLReference/DataDefinition/Privileges
See also: ALTER USER RENAME ALTER USER SET PASSWORD GRANT PRIVILEGES DROP USER

Associated system table: sys.users and sys.auths where name in (select name from sys.users)

ALTER USER RENAME

   ALTER USER user_login_name
    RENAME TO new_user_login_name

Change the user id name of a database user account

For details see: Documentation/SQLReference/DataDefinition/Privileges/Users
See also: ALTER USER SET PASSWORD ALTER USER SET SCHEMA GRANT PRIVILEGES

Associated system table: sys.users

ALTER USER SET PASSWORD

   ALTER USER SET [ ENCRYPTED | UNENCRYPTED ] PASSWORD new_pass_phrase
    USING OLD PASSWORD old_pass_phrase

Change the password of the current user account

Example to change the default password of system user monetdb (when connected as monetdb):

SELECT CURRENT_USER;
ALTER USER SET PASSWORD 'make it private' USING OLD PASSWORD 'monetdb';

For details see: Documentation/SQLReference/DataDefinition/Privileges/Users
See also: ALTER USER SET SCHEMA ALTER USER RENAME GRANT PRIVILEGES

Associated system table: sys.users

ALTER USER SET SCHEMA

   ALTER USER user_login_name
    [ WITH [ ENCRYPTED | UNENCRYPTED ] PASSWORD pass_phrase ]
    SET SCHEMA schema_name

Change the default schema of a database user account

For details see: Documentation/SQLReference/DataDefinition/Privileges/Users
See also: ALTER USER SET PASSWORD ALTER USER RENAME GRANT PRIVILEGES

Associated system table: sys.users and sys.auths

DROP ROLE

   DROP ROLE role_name
    [ WITH ADMIN grantor ]

Remove an existing role

Example:

DROP ROLE controller;

Note: Associated role privileges and granted user_role records will also be removed from sys.privileges and sys.user_role

For details see: Documentation/SQLReference/DataDefinition/Privileges/Roles
and: Documentation/SQLReference/DataDefinition/DropStatement
See also: CREATE ROLE GRANT ROLE SET ROLE

Associated system table: sys.roles and sys.user_role

DROP USER

   DROP USER user_login_name

Remove an existing database login account

Note: Associated user privileges and granted user_role records will also be removed from sys.privileges and sys.user_role

For details see: Documentation/SQLReference/DataDefinition/Privileges/Users
and: Documentation/SQLReference/DataDefinition/DropStatement
See also: CREATE USER ALTER USER RENAME

Associated system table: sys.users and sys.auths where name in (select name from sys.users)

GRANT PRIVILEGES

   GRANT { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | ALL [ PRIVILEGES ] | EXECUTE | COPY INTO | COPY FROM }
    [, ... ]
    ON [ TABLE | FUNCTION | PROCEDURE | AGGREGATE | WINDOW ] object_name
    TO { user_login_name | role_name | PUBLIC } [, ... ]
    [ WITH GRANT OPTION ]

Add privileges on a table or function (incl. procedure, filter function, loader) or aggregate function for a user or role or all users (PUBLIC).
For tables the privileges can be: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, ALL, COPY INTO, COPY FROM. ALL implies privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE and REFERENCES. Note: privilege REFERENCES is currently implemented as a synonym for SELECT.
For aggregates, functions and procedures the privilege can be: EXECUTE or ALL.
The privileges COPY INTO, COPY FROM enable execution of those COPY INTO, COPY FROM commands to users other than the monetdb system user.

Example:

GRANT ALL ON TABLE web_event TO PUBLIC;
GRANT SELECT, INSERT ON TABLE "web_log" TO jan, piet, controller WITH GRANT OPTION;
GRANT EXECUTE ON FUNCTION refresh_func TO PUBLIC;
GRANT COPY INTO, COPY FROM ON TABLE new_facts TO mrdata;

Note: The current user must have permission to GRANT privileges.
The COPY INTO and COPY FROM privileges are MonetDB specific SQL. They are introduced in release Jun2016 (11.23.3).

For details see: Documentation/SQLReference/DataDefinition/Privileges
See also: REVOKE PRIVILEGES GRANT ROLE CREATE USER

Associated system table: sys.privileges

REVOKE PRIVILEGES

   REVOKE [ GRANT OPTION FOR ]
    { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | ALL [ PRIVILEGES ] | EXECUTE | COPY INTO | COPY FROM }
    [, ... ]
    ON [ TABLE | FUNCTION | AGGREGATE | WINDOW ] object_name
    FROM { user_login_name | role_name | PUBLIC } [, ... ]

Remove privileges on a table or function (incl. procedure, filter function, loader) or aggregate function from a user or role or PUBLIC

Example:

REVOKE INSERT, DELETE, TRUNCATE ON TABLE web_event FROM jan;
REVOKE EXECUTE ON FUNCTION refresh_func FROM piet;

Note: The current user must have permission to GRANT privileges.
The COPY INTO and COPY FROM privileges are MonetDB specific SQL. They are introduced in release Jun2016 (11.23.3).

For details see: Documentation/SQLReference/DataDefinition/Privileges
See also: GRANT PRIVILEGES REVOKE ROLE DROP USER

Associated system table: sys.privileges

GRANT ROLE

   GRANT role_name [, ... ] TO user_login_name [, ... ]
    [ WITH ADMIN OPTION ]
    [ WITH ADMIN grantor ]

Add one or more role privileges to one or more users

Example:

GRANT controller TO jan WITH ADMIN OPTION;

For details see: Documentation/SQLReference/DataDefinition/Privileges
and: Documentation/SQLReference/DataDefinition/Privileges/Roles
See also: REVOKE ROLE GRANT PRIVILEGES CREATE ROLE

Associated system table: sys.user_role

REVOKE ROLE

   REVOKE [ ADMIN OPTION FOR ] role_name [, ... ] FROM user_login_name [, ... ]

Remove one or more role privileges from one or more users

Example:

REVOKE controller FROM jan;

For details see: Documentation/SQLReference/DataDefinition/Privileges
and: Documentation/SQLReference/DataDefinition/Privileges/Roles
See also: GRANT ROLE DROP ROLE

Associated system table: sys.user_role

SET SCHEMA

   SET SCHEMA schema_name

Change the current schema

Example:

SELECT CURRENT_SCHEMA;
SET SCHEMA profiler;
SELECT CURRENT_SCHEMA;

For details see: Documentation/SQLReference/DataDefinition/SchemaDefinitions
and: Documentation/SQLreference/ProgrammingSQL/Variables
See also: CURRENT_SCHEMA

Associated system table: sys.var_values

SET ROLE

   SET ROLE role_name

Change the current role

Example:

SELECT CURRENT_ROLE;
SET ROLE controller;
SELECT CURRENT_ROLE;

For details see: Documentation/SQLReference/DataDefinition/Privileges/Roles
and: Documentation/SQLreference/ProgrammingSQL/Variables
See also: CURRENT_ROLE GRANT ROLE

Associated system table: sys.var_values

SET USER

   SET USER = user_login_name

Change the current user to the specified user

Example:

SELECT USER;
SET USER = jan;
SELECT USER;

Note: USER is a synonym for CURRENT_USER

For details see: Documentation/SQLReference/DataDefinition/Privileges/Users
and: Documentation/SQLreference/ProgrammingSQL/Variables
See also: USER GRANT PRIVILEGES

Associated system table: sys.var_values

SET CURRENT_USER

   SET CURRENT_USER = user_login_name

Change the current user to the specified user

Example:

SELECT CURRENT_USER;
SET CURRENT_USER = piet;
SELECT CURRENT_USER;

For details see: Documentation/SQLReference/DataDefinition/Privileges/Users
and: Documentation/SQLreference/ProgrammingSQL/Variables
See also: CURRENT_USER

Associated system table: sys.var_values

SET SESSION_USER

   SET SESSION_USER = user_login_name

Change the session user to the specified user

Example:

SET SESSION_USER = isabel;
SELECT SESSION_USER;

For details see: Documentation/SQLReference/DataDefinition/Privileges/Users
and: Documentation/SQLreference/ProgrammingSQL/Variables
See also: SESSION_USER

Associated system table: sys.var_values

SET SESSION AUTHORIZATION

   SET SESSION AUTHORIZATION role_name

Change the session authorization to the specified role

For details see: Documentation/SQLReference/DataDefinition/Privileges/Roles
and: Documentation/SQLreference/ProgrammingSQL/Variables

COPY INTO FROM

   COPY [ int_val [ OFFSET int_val ] RECORDS ]
    INTO [ schema_name . ] table_name
         [ '(' column_name [ ',' column_name [ ... ] ] ')' ]
    FROM file_name [ ',' file_name [ ... ] ]
     [ '(' ident [ STRING ] [ ',' ident [ STRING ] [ ... ] ] ')' ]
     [ ON { CLIENT | SERVER } ]
     [ [ USING ] DELIMITERS  field_separator
       [ ',' record_separator [ ',' string_quote ] ] ]
     [ NULL [ AS ] null_string ]
     [ LOCKED ]
     [ BEST EFFORT ]
     [ NO CONSTRAINT ]
     [ FWF '(' pos [ ',' pos [ ... ] ] ')' ]

Read data (in UTF-8 encoding) from an external (compressed) csv/tsv/txt file and bulk insert it into an existing table

Note: This command is MonetDB specific.
By default the file(s) must be accessible from the server where mserver5 process is running. From release Apr2019 (11.33.3) onwards it is possible to read the file(s) from the mclient program by specifying ON CLIENT. Default behavior is ON SERVER. The ON CLIENT option is *not* supported in other programs or APIs (ODBC, JDBC, python DBI, etc.).
It can also read directly from a compressed file if the file name has extension: .bz2 or .gz or .xz or .lz4. On Windows OS extensions .xz and .lz4 are not yet supported. Extension .zip is not recognised, use extension .gz instead.
The default field_separator is the pipe '|' character. To change to tab character specify: '\t'.
The default record_separator is the newline '\n' character.
The default string_quote character is a double quote: ".
IMPORTANT: All character string data in the csv/tsv/txt file must be stored in UTF-8 character encoding. Other character encodings are not supported.

For details see: Documentation/ServerAdministration/LoadingBulkData/CSVBulkLoads
and: Documentation/ServerAdministration/LoadingBulkData

Associated system table: sys.querylog_history

COPY INTO FROM STDIN

   COPY [ int_val [ OFFSET int_val ] RECORDS ]
    INTO [ schema_name . ] table_name
         [ '(' column_name [ ',' column_name [ ... ] ] ')' ]
    FROM STDIN
     [ '(' ident [ STRING ] [ ',' ident [ STRING ] [ ... ] ] ')' ]
     [ [ USING ] DELIMITERS  field_separator
       [ ',' record_separator [ ',' string_quote ] ] ]
     [ NULL [ AS ] null_string ]
     [ LOCKED ]
     [ BEST EFFORT ]
     [ NO CONSTRAINT ]

Read data (in UTF-8 encoding) from console (stdin) and bulk insert it into an existing table

Note: This command is MonetDB specific.
The default field_separator is the pipe '|' character. To change to tab character specify: '\t'.
The default record_separator is the newline '\n' character.
The default string_quote character is a double quote: ".

For details see: Documentation/ServerAdministration/LoadingBulkData/CSVBulkLoads
and: Documentation/ServerAdministration/LoadingBulkData
and: SQLcopyinto.java example program

Associated system table: sys.querylog_history

COPY BINARY INTO FROM

   COPY BINARY
    INTO [ schema_name . ] table_name
         [ '(' column_name [ ',' column_name [ ... ] ] ')' ]
    FROM file_name [ ',' file_name [ ... ] ]
     [ ON { CLIENT | SERVER } ]
     [ NO CONSTRAINT ]

Attach column data from an external binary file(s) to very quickly make it available to MonetDB as storage column(s)

Note: This command is MonetDB specific.
By default the files must be accessible from the server where mserver5 process is running. From release Apr2019 (11.33.3) onwards it is possible to read the file from the mclient program by specifying ON CLIENT. Default behavior is ON SERVER. The ON CLIENT option is *not* supported in other programs or APIs (ODBC, JDBC, python DBI, etc.).
IMPORTANT: not all datatypes are supported. Especially all variable length data types such as varchar are *not* supported. Also a NULL value must be represented as a special value, which is different per datatype.

For details see: Documentation/ServerAdministration/LoadingBulkData/BinaryBulkLoad

Associated system table: sys.querylog_history

COPY LOADER INTO FROM

   COPY LOADER INTO [ schema_name . ] table_name
    FROM function_name ( [ arg1_val [ , arg2_val ] [, ... ] ] )

Read data via a loader function and bulk insert it into an existing table

Example:

COPY LOADER INTO tbl FROM web_log_loader();

Note: This command is MonetDB specific. It is introduced in release Dec2016 (11.25.3).
For a loader function defined in language PYTHON the Python script interpreter software must be available on the deployment server.

For details see: blog/monetdbpython-loader-functions
See also: CREATE LOADER CREATE TABLE FROM LOADER

Associated system table: sys.querylog_history

COPY INTO

   COPY SELECT_query
    INTO file_name
    [ ON { CLIENT | SERVER } ]
    [ [ USING ] DELIMITERS field_separator
      [ ',' record_separator [ ',' string_quote ] ] ]
    [ NULL AS null_string ]

Write query result data in bulk to a (compressed) file on disk in UTF-8 encoding

Note: This command is MonetDB specific.
By default the file path must be accessible from the server where mserver5 process is running. From release Apr2019 (11.33.3) onwards it is now possible to write the file locally by the mclient program by specifying ON CLIENT. Default behavior is ON SERVER. The ON CLIENT option is *not* supported in other programs or APIs (ODBC, JDBC, python DBI, etc.).
The default field_separator is the pipe '|' character. To change to tab character specify: '\t'.
The default record_separator is the newline '\n' character.
The default string_quote character is a double quote: ".
Character data will be exported in UTF-8 encoding. Other encodings are not supported.
The file can also be written directly as compressed file if the file name has extension: .bz2 or .gz or .xz or .lz4. On Windows OS extensions .xz and .lz4 are not yet supported. Extension .zip is not recognised, use extension .gz instead.

For details see: Documentation/ServerAdministration/ExportingBulkData
and: Documentation/SQLReference/DataManipulation/BulkInputOutput

Associated system table: sys.querylog_history

COPY INTO STDOUT

   COPY SELECT_query
    INTO STDOUT
    [ [ USING ] DELIMITERS field_separator
      [ ',' record_separator [ ',' string_quote ] ] ]
    [ NULL AS null_string ]

Write query result data to console (stdout) in UTF-8 encoding

Note: This command is MonetDB specific.
The default field_separator is the pipe '|' character. To change to tab character specify: '\t'.
The default record_separator is the newline '\n' character.
The default string_quote character is a double quote: ".

For details see: Documentation/ServerAdministration/ExportingBulkData
and: Documentation/SQLReference/DataManipulation/BulkInputOutput

Associated system table: sys.querylog_history

SET TRANSACTION

   SET TRANSACTION
    [ READ ONLY | READ WRITE | ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } | DIAGNOSTICS sqlSize ]

Start a transaction (turn auto-commit off) with optionally a transaction mode or isolation level

Example:

SET TRANSACTION;
ROLLBACK;
SET TRANSACTION READ ONLY;
ROLLBACK;
SET TRANSACTION READ WRITE;
ROLLBACK;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
ROLLBACK;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
ROLLBACK;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
ROLLBACK;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ROLLBACK;

SET LOCAL TRANSACTION

   SET LOCAL TRANSACTION
    [ READ ONLY | READ WRITE | ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } | DIAGNOSTICS sqlSize ]

Start a local transaction (turn auto-commit off) with optionally a transaction mode or isolation level

START TRANSACTION

   { START | BEGIN } TRANSACTION
    [ READ ONLY | READ WRITE | ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } | DIAGNOSTICS sqlSize ]

Disable auto-commit and start a user controlled transaction

Note: A transaction can also include data definition (DDL) commands such as CREATE, ALTER, DROP.

For details see: Documentation/SQLreference/Transactions
and: blog/monetdb-sql-transaction-management-scheme
See also: COMMIT ROLLBACK

COMMIT

   COMMIT [ WORK ]
      [ AND [ NO ] CHAIN ]

Make all changes done since the start of the transaction persistent

For details see: Documentation/SQLreference/Transactions
and: blog/monetdb-sql-transaction-management-scheme
See also: ROLLBACK START TRANSACTION

ROLLBACK

   ROLLBACK [ WORK ]
      [ AND [ NO ] CHAIN ]

Undo all changes done since the start of the transaction

For details see: Documentation/SQLreference/Transactions
and: blog/monetdb-sql-transaction-management-scheme
See also: COMMIT START TRANSACTION

SAVEPOINT

   SAVEPOINT savepoint_id_name

Add a savepoint within a transaction

Example:

START TRANSACTION;
SAVEPOINT sp1;

Note: You need to start a transaction first before you can use savepoints

See also: START TRANSACTION RELEASE SAVEPOINT ROLLBACK TO SAVEPOINT

ROLLBACK TO SAVEPOINT

   ROLLBACK [ WORK ]
      [ AND [ NO ] CHAIN ]
      TO SAVEPOINT savepoint_id_name

Undo all changes done since the start of the savepoint

Example:

ROLLBACK TO SAVEPOINT sp1;

RELEASE SAVEPOINT

   RELEASE SAVEPOINT savepoint_id_name

Add all changes done since the start of the savepoint to the current transaction

Example:

RELEASE SAVEPOINT sp1;

SET OPTIMIZER

   SET [ sys. ] optimizer = optimizer_pipeline_name

Change the execution optimizer pipeline

Example:

SELECT sys.optimizer;
SELECT * FROM sys.optimizers;
SET sys.optimizer = 'sequential_pipe';

SET schema sys;
SELECT optimizer;
SET optimizer = 'default_pipe';
SELECT optimizer;
SET optimizer = 'ceci_nest_pas_une_pipe';
SELECT optimizer;

Note: The option to include the sys. prefix is supported from release Oct2020 (11.39.5) onwards.

For details see: Documentation/SQLReference/PerformanceOptimization/OptimizerPipelines
and: Documentation/MonetDBInternals/MALOptimizers
See also: PLAN EXPLAIN

Associated system table: sys.optimizers and sys.environment where name = 'sql_optimizer';

EXPLAIN

   EXPLAIN The SQL command

Show the MAL execution plan for the SQL command.

For details see: Documentation/SQLreference/RuntimeFeatures/Explain Documentation/MonetDBInternals/MALReference
and: Documentation/SQLReference/PerformanceOptimization/OptimizerPipelines
See also: PLAN

PLAN

   PLAN The SQL command

Show the relational execution plan for the SQL command after it has been optimized by the SQL optimzer.

Note: This command is MonetDB specific.

For details see: Documentation/SQLreference/RuntimeFeatures/PlanSQL
and: Documentation/SQLReference/PerformanceOptimization/OptimizerPipelines
See also: EXPLAIN

TRACE

   TRACE The SQL command

Executes the SQL command and return a table with the execution ticks of the MAL instructions.

For details see: Documentation/SQLreference/RuntimeFeatures/Trace

Associated system table: sys.tracelog()

DEBUG

   DEBUG The SQL command

Executes the SQL command in DEBUG mode

For details see: Documentation/SQLreference/RuntimeFeatures/Debug

ANALYZE

   ANALYZE schema_name [ . table_name [ ( column_name [, column_name] [, ... ] ) ] ]
    [ SAMPLE size ]
    [ MINMAX ]

Gather statistics on column(s) of table(s) in a specific schema and store or update them in the system table sys.statistics for analysis

Example:

-- derive the statistics for table sys.statistics
ANALYZE sys.statistics;
SELECT * FROM sys.statistics;

-- update statistics of specific columns of sys.statistics
ANALYZE sys.statistics ("sample", count, "unique", minval, maxval) SAMPLE 1000;

SELECT * FROM sys.statistics
 WHERE column_id IN (SELECT id FROM sys.columns
 WHERE table_id  IN (SELECT id FROM sys.tables
 WHERE schema_id IN (SELECT id FROM sys.schemas
 WHERE name = 'sys') AND name = 'statistics'));

-- remove all the statistics
DELETE FROM sys.statistics;

Note: The statistics information can be used by the query optimizer to choose the optimal execution plan. Rerun the ANALYZE command after many table manipulations (insert/update/delete).
For large tables this command can take some time. Specify a SAMPLE size to reduce the amount of data values to analyze. Add MINMAX to exclude the expensive unique count operation.

For details see: Documentation/ServerAdministration/TableStatistics

Associated system table: sys.statistics

USER

   USER

Example:

SELECT USER;

Note: USER is a synonym for CURRENT_USER

See also: SET USER

Associated system table: sys.var_values

CURRENT_USER

   CURRENT_USER

Example:

SELECT CURRENT_USER;

See also: SET CURRENT_USER

Associated system table: sys.var_values

SESSION USER

   SESSION_USER

Example:

SELECT SESSION_USER;

See also: SET SESSION_USER

Associated system table: sys.var_values

CURRENT_ROLE

   CURRENT_ROLE

Example:

SELECT CURRENT_ROLE;

See also: SET ROLE

Associated system table: sys.var_values

CURRENT_SCHEMA

   CURRENT_SCHEMA

Example:

SELECT CURRENT_SCHEMA;

See also: SET SCHEMA

Associated system table: sys.var_values

CURRENT_TIMEZONE

   CURRENT_TIMEZONE

Example:

SELECT CURRENT_TIMEZONE;

For details see: Documentation/ServerAdministration/DateTimeFunctionality
See also: SET CURRENT_TIMEZONE

Associated system table: sys.var_values

NOW

NOW

Example:

SELECT NOW;

For details see: Documentation/ServerAdministration/DateTimeFunctionality

CURRENT_DATE

   CURRENT_DATE

Example:

SELECT CURRENT_DATE;

For details see: Documentation/ServerAdministration/DateTimeFunctionality

CURRENT_TIME

   CURRENT_TIME

Example:

SELECT CURRENT_TIME;

For details see: Documentation/ServerAdministration/DateTimeFunctionality

CURRENT_TIMESTAMP

   CURRENT_TIMESTAMP

Example:

SELECT CURRENT_TIMESTAMP;

For details see: Documentation/ServerAdministration/DateTimeFunctionality

LOCALTIME

   LOCALTIME

Example:

SELECT LOCALTIME;

For details see: Documentation/ServerAdministration/DateTimeFunctionality

LOCALTIMESTAMP

   LOCALTIMESTAMP

Example:

SELECT LOCALTIMESTAMP;

For details see: Documentation/ServerAdministration/DateTimeFunctionality

NEXT VALUE FOR

   NEXT VALUE FOR sequence_name

Example:

CREATE SEQUENCE "my_test_seq" as integer START WITH 2;
CREATE TABLE test (t int DEFAULT NEXT VALUE FOR "my_test_seq", v varchar(10));
INSERT INTO test(v) VALUES ('ab');
SELECT * FROM test;

For details see: Documentation/SQLReference/DataTypes/SerialDatatypes

Printer-friendly version