Skip to main content

SQL Syntax Overview

Data Definition Language (DDL)

CREATE SCHEMA COMMENT ON SCHEMA SET SCHEMA DROP SCHEMA
CREATE TABLE COMMENT ON TABLE COMMENT ON COLUMN DROP TABLE
  ALTER TABLE ADD COLUMN ALTER TABLE ALTER COLUMN ALTER TABLE DROP COLUMN
  ALTER TABLE ADD CONSTRAINT ALTER TABLE DROP CONSTRAINT
  ALTER TABLE SET INSERT ONLY ALTER TABLE SET READ ONLY ALTER TABLE SET READ WRITE
CREATE TABLE AS
CREATE TABLE FROM LOADER
CREATE MERGE TABLE ALTER TABLE ADD TABLE ALTER TABLE DROP TABLE
CREATE REMOTE TABLE
CREATE REPLICA TABLE
CREATE STREAM TABLE
CREATE TEMPORARY TABLE
CREATE VIEW COMMENT ON VIEW COMMENT ON COLUMN DROP VIEW
CREATE INDEX
CREATE IMPRINTS INDEX
CREATE ORDERED INDEX
COMMENT ON INDEX DROP INDEX
CREATE FUNCTION
CREATE FUNCTION EXTERNAL
CREATE FUNCTION LANGUAGE
COMMENT ON FUNCTION DROP FUNCTION DROP ALL FUNCTION
CREATE AGGREGATE EXTERNAL
CREATE AGGREGATE LANGUAGE
COMMENT ON AGGREGATE DROP AGGREGATE DROP ALL AGGREGATE
CREATE FILTER FUNCTION EXTERNAL COMMENT ON FILTER FUNCTION DROP FILTER FUNCTION DROP ALL FILTER FUNCTION
CREATE LOADER COMMENT ON LOADER DROP LOADER  
CREATE PROCEDURE
CREATE PROCEDURE EXTERNAL
COMMENT ON PROCEDURE DROP PROCEDURE DROP ALL PROCEDURE
CREATE SEQUENCE COMMENT ON SEQUENCE ALTER SEQUENCE DROP SEQUENCE
CREATE TYPE EXTERNAL DROP TYPE
CREATE TRIGGER DROP TRIGGER
DECLARE

Data Manipulation Language (DML)

SELECT SELECT INTO WITH SELECT
INSERT VALUES INSERT SELECT
UPDATE
DELETE
TRUNCATE
PREPARE EXECUTE
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

Authorisation, 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 DROP ROLE
GRANT ROLE REVOKE ROLE
SET SESSION AUTHORIZATION

Transaction Control Commands

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

Commands to Analyse / Optimize SQL Execution

EXPLAIN
PLAN
TRACE
DEBUG
SET OPTIMIZER
ANALYZE

Pseudo Columns

CURRENT_SCHEMA
CURRENT_USER USER  SESSION_USER
CURRENT_ROLE
CURRENT_DATE NOW
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIMEZONE
LOCALTIME
LOCALTIMESTAMP
NEXT VALUE FOR

Details

CREATE SCHEMA

   CREATE SCHEMA [ IF NOT EXISTS ] <name>
    [ AUTHORISATION <auth name> ]

Define a new schema

Example:

CREATE SCHEMA tst;
SET SCHEMA tst;
SELECT CURRENT_SCHEMA;


Note: The "IF NOT EXISTS" option is supported from release Jul2017 onwards.

For details see: Documentation/SQLreference/Schema
See also: SET SCHEMA   CURRENT_SCHEMA   COMMENT ON SCHEMA   DROP SCHEMA

Associated system table: sys.schemas
 

CREATE SEQUENCE

   CREATE SEQUENCE [ <schema name> . ] <name>
    [ AS datatype ]
    [ START WITH nonzero-bigint ]
    [ INCREMENT BY nonzero-bigint ]
    [ MINVALUE nonzero-bigint | NO MINVALUE ]
    [ MAXVALUE nonzero-bigint | NO MAXVALUE ]
    [ CACHE nonzero-bigint ]
    [ [ NO ] CYCLE ]

Define a new integer number sequence generator

For details see: Documentation/Manuals/SQLreference/SerialTypes
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> . ] <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 onwards.

For details see: Documentation/Manuals/SQLreference/Tables
and: Documentation/SQLreference/TableIdentityColumn
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> . ] <name>
    [ ( <column name(s) list> ) ]
    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 onwards.

For details see: Documentation/Manuals/SQLreference/Tables
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> . ] <name>
    FROM LOADER <loader function name> ( [ <arguments> ] )
    [ WITH [NO] DATA ]

Define a new table from the results of a (Phyton) 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 SQL. It is introduced in Dec2016 release.
The "IF NOT EXISTS" option is supported from release Jul2017 onwards.

For details see: blog/monetdbpython-loader-functions
See also: CREATE LOADER   COPY LOADER INTO FROM   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 MERGE TABLE

   CREATE MERGE TABLE [ IF NOT EXISTS ] [ <schema name> . ] <name>
    ( <column definition(s) and optional table-constraints list> )

Define a new merge table. A merge table combines the data of multiple other tables (partitions added via ALTER TABLE union_t1 ADD TABLE part_n) which all must have the exact same table definition

Note: This command is MonetDB specific SQL.
The "IF NOT EXISTS" option is supported from release Jul2017 onwards.
A merge table is removed using the ordinary DROP TABLE statement. There is no DROP MERGE TABLE statement.

For details see: Documentation/Cookbooks/SQLrecipes/DataPartitioning
and: Documentation/Manuals/SQLreference/Tables
See also: ALTER TABLE ADD TABLE   ALTER TABLE DROP TABLE   COMMENT ON TABLE   COMMENT ON COLUMN   DROP TABLE

Associated system table: sys.tables where type = 3
 

CREATE REMOTE TABLE

   CREATE REMOTE TABLE [ IF NOT EXISTS ] [ <schema name> . ] <name>
    ( <column definition(s)> )
    ON <remote table URL> [WITH [USER 'username'] [[ENCRYPTED] PASSWORD 'password']]

Define a alias for a new remote table. The remote table must be an existing table on a running MonetDB server

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

For details see: Documentation/Cookbooks/SQLrecipes/DistributedQueryProcessing
and: Documentation/Manuals/SQLreference/Tables
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> . ] <name>
    ( <column definition(s) and optional table-constraints list> )

Define a new replica table

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

For details see: Documentation/Manuals/SQLreference/Tables
and: Documentation/SQLreference/TableIdentityColumn
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> . ] <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 SQL.
The "IF NOT EXISTS" option is supported from release Jul2017 onwards.
A stream table is removed using the ordinary DROP TABLE statement. There is no DROP STREAM TABLE statement.

For details see: Documentation/Extensions/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> . ] <name>
    { ( <column definition(s) and optional table-constraints list> )  |
      [ ( <column name(s)> ) ] 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, 'one');
-- 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, 'drie');
SELECT * FROM tmp.names;
-- shows 3 rows
DROP TABLE tmp.names;

CREATE GLOBAL TEMP TABLE tmp.name_lengths
 AS SELECT DISTINCT name, LENGTH(name) as length FROM sys.ids ORDER BY 1
 WITH DATA
 ON COMMIT PRESERVE ROWS;
SELECT * FROM tmp.name_lengths WHERE name ILIKE '%id%';
SELECT COUNT(*) AS count_names, AVG(length) AS avg_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 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/Manuals/SQLreference/Tables
and: Documentation/SQLreference/TableIdentityColumn
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 list> )

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 ADD CONSTRAINT UNIQUE instead.

For details see: Documentation/Manuals/SQLreference/Indices
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 SQL. It is introduced in the Dec2016 feature release (v11.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.
A imprints index is removed using the ordinary DROP INDEX statement. There is no DROP IMPRINTS INDEX statement.

For details see: Documentation/Manuals/SQLreference/Indices
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 SQL. It is introduced in the Dec2016 feature release (v11.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.
Before Mar2018 release only 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 release.
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/Manuals/SQLreference/Indices
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 list> ) ]
    AS <SELECT query without LIMIT>
    [ WITH CHECK OPTION ]

Define a new SQL view. A view can be seen as a stored SELECT query. It is useful to reduce query complexity as it can include joins, unions and aggregations, increase standardisation and reusability and support server-based reporting.

Note: Limitations: Views are NOT updatable.
An ORDER BY clause is supported from release Jul2017 onwards.
The "OR REPLACE" option is supported from release Mar2018 onwards.

For details see: Documentation/Manuals/SQLreference/Views
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 [ <schema name> . ] <name> ( [ <arguments> ] )
    RETURNS <data type>
    EXTERNAL NAME <MAL name>

Define a new user-defined aggregation function for which the implementation is done externally

Example:

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


Note: This command is MonetDB specific SQL. 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 onwards.

For details see: Documentation/Manuals/SQLreference/Functions
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 [ <schema name> . ] <name> ( [ <arguments> ] )
    RETURNS <data type>
    LANGUAGE [ C | CPP | R | PYTHON | PYTHON_MAP | PYTHON2 | PYTHON2_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 {
    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]])
    return(x)
};


Note: This command is MonetDB specific SQL. Supported languages are: C, C++, R and Python.
If your Python code requires Python 2 to work correctly use PYTHON2 instead of 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 PYTHON2_MAP and PYTHON3_MAP.
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.
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 onwards.

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

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

CREATE FUNCTION

   CREATE [ OR REPLACE ] FUNCTION [ <schema name> . ] <name> ( [ <arguments> ] )
    RETURNS <data type>
    BEGIN <function logic> 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 onwards.

For details see: Documentation/Manuals/SQLreference/Functions
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> . ] <name> ( [ <arguments> ] )
    RETURNS <data type>
    EXTERNAL NAME <MAL name>

Define a new user-defined function for which the implementation is done externally

Example:

CREATE FUNCTION isa_uuid(s string) returns boolean external name uuid."isaUUID";


Note: This command is MonetDB specific SQL. 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 onwards.

For details see: Documentation/Manuals/SQLreference/Functions
and: Documentation/Cookbooks/SQLrecipes/UserDefinedFunction
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> . ] <name> ( [ <arguments> ] )
    RETURNS <data type>
    LANGUAGE [ C | CPP | R | PYTHON | PYTHON_MAP | PYTHON2 | PYTHON2_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 SQL. Supported languages are: C, C++, R and Python.
If your Python code requires Python 2 to work correctly use PYTHON2 instead of 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 PYTHON2_MAP and PYTHON3_MAP.
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.
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 onwards.

For details see: JIT C/C++ UDFs and blog/embedded-pythonnumpy-monetdb
and: blog/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
 

CREATE FILTER FUNCTION EXTERNAL

   CREATE [ OR REPLACE ] FILTER FUNCTION [ <schema name> . ] <name> ( [ <arguments> ] )
    RETURNS <data type>
    EXTERNAL NAME <MAL name>

Define a new user-defined filter function for which the implementation is done externally

Example:

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


Note: This command is MonetDB specific SQL. 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 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 [ <schema name> . ] <name> ( [ <arguments> ] )
    LANGUAGE PYTHON
    { <phyton _emit.emit() function code> }

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 SQL. It is introduced in Dec2016 release.
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.
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 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> . ] <name> ( [ <arguments> ] )
    BEGIN <procedure logic> 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 onwards.

For details see: Documentation/Manuals/SQLreference/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> . ] <name> ( [ <arguments> ] )
    EXTERNAL NAME <MAL name>

Define a new user-defined procedure for which the implementation is done externally

Example:

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


Note: This command is MonetDB specific SQL. 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 onwards.

For details see: Documentation/Manuals/SQLreference/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 TYPE EXTERNAL

   CREATE TYPE [ <schema name> . ] <type name>
    EXTERNAL NAME <MAL name>

Declare a new user-defined data type. The implementation (structure, operators & functions both scalar and bulk) must be done externally in compiled C code and MAL script.

Note: This command is MonetDB specific SQL. External implies language MAL. It adds a new user-defined type which is implemented in external C code. For examples see the C implementation of types: inet, json, url and uuid.

For details see: Documentation/Manuals/SQLreference/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 ] [ UPDATE [ OF <column name list> ] ] [ DELETE ] [ TRUNCATE ] ON [ <schema name> . ] <table name>
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    BEGIN <trigger action logic> END

Define a new trigger

Note: The "OR REPLACE" option is supported from release Mar2018 onwards.
The TRUNCATE event is supported from release Mar2018 onwards.

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

Associated system table: sys.triggers
 

COMMENT ON SCHEMA

   COMMENT ON 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 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> . ] <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 onwards.
Note: it is not allowed or possible to add comments for temporary tables or objects 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> . ] <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 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 onwards.
Note: it is not allowed or possible to add comments for columns of temporary tables or objects 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 onwards.

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> . ] <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 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> . ] <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 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> . ] <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 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> . ] <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 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> . ] <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 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> . ] <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 onwards.

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

Associated system table: sys.comments
 

DECLARE

   DECLARE <variable name> <datatype>

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.

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

Associated system table: sys.var()
 

ALTER SEQUENCE

   ALTER SEQUENCE [ <schema name> . ] <name> [ AS datatype ]
    [ RESTART [WITH nonzero-bigint ] ]
    [ INCREMENT BY nonzerol-bigint ]
    [ MINVALUE nonzero-bigint | NO MINVALUE ]
    [ MAXVALUE nonzero-bigint | NO MAXVALUE ]
    [ CACHE nonzero-bigint ]
    [ [ NO ] CYCLE ]

Change definition of a sequence generator

For details see: Documentation/Manuals/SQLreference/SerialTypes
See also: DROP SEQUENCE   CREATE SEQUENCE

Associated system table: sys.sequences
 

ALTER TABLE ADD COLUMN

   ALTER TABLE [ <schema name> . ] <name>
    ADD [ COLUMN ] <new column name> <column spec>

Add a column to a table

For details see: Documentation/SQLreference/Alter
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 [ <schema name> . ] <name>
    ALTER [ COLUMN ] <column name> { SET DEFAULT value | SET [NOT] NULL | SET STORAGE {string | NULL} | DROP DEFAULT }

Change column default value or nullability or storage

Note: Change of column name or data type is not supported. Instead use command sequence:
  ALTER TABLE tbl ADD COLUMN new_column <column spec>;
  UPDATE tbl SET new_column = CAST(old_column AS <new_column data type>);
  ALTER TABLE tbl DROP COLUMN old_column RESTRICT;

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

Associated system table: sys.columns
 

ALTER TABLE DROP COLUMN

   ALTER TABLE [ <schema name> . ] <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.

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

Associated system table: sys.columns
 

ALTER TABLE ADD CONSTRAINT

   ALTER TABLE [ <schema name> . ] <name>
    ADD CONSTRAINT <constraint name> { PRIMARY KEY | UNIQUE | FOREIGN KEY }
    <table constraint specification>

Add a table constraint to a table

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.
The CHECK constraint is not (yet) supported.

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

Associated system table: sys.keys
 

ALTER TABLE DROP CONSTRAINT

   ALTER TABLE [ <schema name> . ] <name>
    DROP CONSTRAINT <constraint name>
    [ RESTRICT | CASCADE ]

Remove a table 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.

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

Associated system table: sys.keys
 

ALTER TABLE ADD TABLE

   ALTER TABLE [ <schema name> . ] <merge table name>
    ADD TABLE <table name>

Add a table reference to a merge table set

Note: This command is MonetDB specific SQL.
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.

For details see: Documentation/Cookbooks/SQLrecipes/DataPartitioning
and: Documentation/SQLreference/Alter
See also: CREATE MERGE TABLE   ALTER TABLE DROP TABLE

Associated system table: sys.tables where type = 3
 

ALTER TABLE DROP TABLE

   ALTER TABLE [ <schema name> . ] <merge table name>
    DROP TABLE <table name>
    [ RESTRICT | CASCADE ]

Remove a table reference from a merge table set

Note: This command is MonetDB specific SQL.

For details see: Documentation/Cookbooks/SQLrecipes/DataPartitioning
and: Documentation/SQLreference/Alter
See also: CREATE MERGE TABLE   ALTER TABLE ADD TABLE

Associated system table: sys.tables where type = 3
 

ALTER TABLE SET INSERT ONLY

   ALTER TABLE [ <schema name> . ] <name>
    SET INSERT ONLY

Change access of a table to allow only inserts

For details see: Documentation/SQLreference/Alter
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 [ <schema name> . ] <name>
    SET READ ONLY

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

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

Associated system table: sys.tables
 

ALTER TABLE SET READ WRITE

   ALTER TABLE [ <schema name> . ] <name>
    SET READ WRITE

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

For details see: Documentation/SQLreference/Alter
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 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 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 (e.g. from a column, a function or procedure).

For details see: Documentation/Manuals/SQLreference/SerialTypes
and: Documentation/Manuals/SQLreference/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 (e.g. from a view, a merge table, a trigger, a foreign key constraint, a function, a procedure or another db object except indices and comments). Use option CASCADE to specify to also drop those referencing objects.
System tables can not be dropped as they are needed by the system.
The "IF EXISTS" option is supported from release Jul2017 onwards.

For details see: Documentation/Cookbooks/SQLrecipes/DataPartitioning
and: Documentation/Manuals/SQLreference/DropStatement
See also: CREATE TABLE

Associated system table: sys.tables
 

DROP INDEX

   DROP INDEX [ <schema name> . ] <index name>

Remove an existing index

Example:

DROP TABLE "my_project"."my_Index";


For details see: Documentation/Manuals/SQLreference/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 not be dropped as they are needed by the system.
The "IF EXISTS" option is supported from release Jul2017 onwards.

For details see: Documentation/Manuals/SQLreference/DropStatement
See also: CREATE VIEW

Associated system table: sys.tables where type = 1
 

DROP AGGREGATE

   DROP AGGREGATE [ IF EXISTS ] [ <schema name> . ] <aggregate function name>
    [ ( [ arg1_type [ , argn_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 SQL.
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 onwards.

For details see: Documentation/Manuals/SQLreference/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 [ <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 SQL.
Use option CASCADE to specify to also drop referencing objects.

For details see: Documentation/Manuals/SQLreference/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 [ , argn_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 onwards.

For details see: Documentation/Manuals/SQLreference/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 SQL.
Use option CASCADE to specify to also drop referencing objects.

For details see: Documentation/Manuals/SQLreference/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 [ , argn_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 SQL.
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 onwards.

For details see: Documentation/Manuals/SQLreference/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 SQL.
Use option CASCADE to specify to also drop referencing objects.

For details see: Documentation/Manuals/SQLreference/DropStatement
See also: DROP FILTER FUNCTION   CREATE FILTER FUNCTION EXTERNAL

Associated system table: sys.functions where type = 4
 

DROP LOADER

   DROP LOADER [ IF EXISTS ] [ <schema name> . ] <loader name>
    [ ( [ arg1_type [ , argn_type ] ] ) ]

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 SQL. It is introduced in Dec2016 release.
The "IF EXISTS" option is supported from release Mar2018 onwards.

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

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

DROP PROCEDURE

   DROP PROCEDURE [ IF EXISTS ] [ <schema name> . ] <procedure name>
    [ ( [ arg1_type [ , argn_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 onwards.

For details see: Documentation/Manuals/SQLreference/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 SQL.
Use option CASCADE to specify to also drop referencing objects.

For details see: Documentation/Manuals/SQLreference/DropStatement
See also: DROP PROCEDURE   CREATE PROCEDURE   CREATE PROCEDURE EXTERNAL

Associated system table: sys.functions where type = 2
 

DROP TYPE

   DROP TYPE [ <schema name> . ] <type name>
    [ RESTRICT | CASCADE ]

Remove a user-defined type

For details see: Documentation/Manuals/SQLreference/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 onwards.

For details see: Documentation/Manuals/SQLreference/DropStatement
See also: CREATE TRIGGER

Associated system table: sys.triggers
 

SELECT

   SELECT <selection list>
    [ FROM <table_view_subselect_function list> [<alias>] ]
    [ WHERE <condition list> ]
    [ GROUP BY <grouping list> ]
    [ HAVING <group conditon list> ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <select query> ]
    [ ORDER BY <ordering list [ASC | DESC] > ]
    [ LIMIT <pos nr> ]
    [ OFFSET <pos nr> ]
    [ SAMPLE <sample size> ]

Execute a database query and return the resultset data rows

Example:

-- query to lists all user created tables:
SELECT tbl.id, sch.name AS "Schema", tbl.name as "Table", (select table_type_name from sys.table_types where table_type_id = type) as table_type
  FROM sys."tables" as tbl join sys."schemas" as sch on sch.id = tbl.schema_id
 WHERE query is NULL and NOT tbl.system
 ORDER by "Schema", "Table";


Note: It is also possible to select data without a FROM-clause, such as: SELECT CURRENT_DATE, (1+2) * 3, pi();

For details see: Documentation/SQLreference/TableExpressions
and: Documentation/Cookbooks/SQLrecipes/Sampling

Associated system table: sys.queue and sys.querylog_history
 

SELECT INTO

   SELECT <selection list>
    INTO <variable list>
    [ FROM <table_view_subselect_function list> [<alias>] ]
    [ WHERE <condition list> ]
    [ GROUP BY <grouping list> ]
    [ HAVING <group conditon list> ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <select query> ]
    [ ORDER BY <ordering list [ASC | DESC] > ]
    [ LIMIT <pos nr> ]
    [ OFFSET <pos nr> ]
    [ SAMPLE <sample size> ]

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

For details see: Documentation/SQLreference/TableExpressions
and: Documentation/Cookbooks/SQLrecipes/monitor

Associated system table: sys.queue and sys.querylog_history
 

WITH SELECT

   WITH <alias name> AS <select query without LIMIT>
   SELECT <selection list>
    [ FROM <alias_table_view_subselect_function list> [<alias>] ]
    [ WHERE <condition list> ]
    [ GROUP BY <grouping list> ]
    [ HAVING <group conditon list> ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <select query> ]
    [ ORDER BY <ordering list [ASC | DESC] > ]
    [ LIMIT <pos nr> ]
    [ OFFSET <pos nr> ]
    [ SAMPLE <sample size> ]

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

For details see: Documentation/SQLreference/TableExpressions

Associated system table: sys.queue and sys.querylog_history
 

INSERT VALUES

   INSERT INTO [ <schema name> . ] <table name> [ ( <column name list> ) ]
    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/Updates
and: Documentation/Cookbooks/SQLrecipes/LoadingBulkData

Associated system table: sys.querylog_history
 

INSERT SELECT

   INSERT INTO [ <schema name> . ] <table name>
    [ ( <column name list> ) ]
    SELECT <select query>

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

For details see: Documentation/SQLreference/Updates

Associated system table: sys.querylog_history
 

UPDATE

   UPDATE [ <schema name> . ] <table name>
    SET <column name> = <expression value>
      [ , <column name> = <expression value>]
    [ 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.

Example:

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


For details see: Documentation/SQLreference/Updates

Associated system table: sys.querylog_history
 

DELETE

   DELETE FROM [ <schema name> . ] <table name>
    [ 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.

Example:

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


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 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/Updates
See also: DELETE

Associated system table: sys.querylog_history
 

CALL

   CALL [ <schema name> . ] <procedure name> ( [ <argument list> ] )

Execute a stored procedure

Example:

CALL "sys"."settimeout"(3000);


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

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

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

Example:

PREPARE SELECT id, name, schema_id FROM tables WHERE name like ?;
EXECUTE 3('%type%');
EXECUTE 3('%id%');
PREPARE INSERT INTO towns (name, country, citizens) VALUES (?, ?, ?);
EXECUTE 5('Amsterdam', 'NL', 856123);
EXECUTE 5('Berlin', 'DE', 3715930);


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

For details see: Documentation/Manuals/SQLreference/PrepareExec
and: Documentation/Cookbooks/SQLrecipes/LoadingBulkData
See also: EXECUTE

Associated system table: sys.querylog_catalog
 

EXECUTE

   EXECUTE <prepared SQL nr> ( [ <parm1_val> [, ... ] ] )

Execute the prepared statement with parameter values

Example:

EXECUTE 5('c-data');
EXECUTE 7('Amsterdam', 'NL', 856123);


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

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

Associated system table: sys.querylog_calls
 

SET

   SET <variable name> = <new value>

Change the value of a declared variable

For details see: Documentation/Manuals/SQLreference/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/Manuals/SQLreference/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/Manuals/SQLreference/Variables    Documentation/SQLreference/Temporal
 

CREATE ROLE

   CREATE ROLE <role name>
    [ WITH ADMIN <grantor name> ]

Define a new role

Example:

CREATE ROLE controller;


For details see: Documentation/SQLreference/Roles
and: Documentation/SQLreference/Permissions
See also: DROP ROLE   GRANT PRIVILEGES   GRANT ROLE   SET ROLE

Associated system table: sys.roles and sys.auths
 

CREATE USER

   CREATE USER <login name>
    WITH [ ENCRYPTED | UNENCRYPTED ] PASSWORD <login pass phrase>
    NAME <full name>
    SCHEMA <schema name>

Define a new database login account

Example:

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


For details see: Documentation/SQLreference/Users
and: Documentation/SQLreference/Permissions
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 <login name>
    RENAME TO <new login name>

Change the login name of a database login account

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

Associated system table: sys.users
 

ALTER USER SET PASSWORD

   ALTER USER <login name>
    SET [ ENCRYPTED | UNENCRYPTED ] PASSWORD <new pass phrase> USING OLD PASSWORD <old pass phrase>

Change the password of a database login account

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

Associated system table: sys.users
 

ALTER USER SET SCHEMA

   ALTER USER <login name>
    [ WITH [ ENCRYPTED | UNENCRYPTED ] PASSWORD <pass phrase> ]
    SET SCHEMA <new schema name>

Change the default schema of a database login account

For details see: Documentation/SQLreference/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 name> ]

Remove an existing role

Example:

DROP ROLE controller;


Note: Associated role privileges will also be removed

For details see: Documentation/SQLreference/Roles
and: Documentation/Manuals/SQLreference/DropStatement
See also: CREATE ROLE   GRANT ROLE   SET ROLE

Associated system table: sys.roles and sys.user_role
 

DROP USER

   DROP USER <login name>

Remove an existing database login account

Note: Associated user privileges and user roles will also be removed

For details see: Documentation/SQLreference/Users
and: Documentation/Manuals/SQLreference/DropStatement
See also: CREATE USER   ALTER USER RENAME

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

GRANT PRIVILEGES

   GRANT <ALL or specific privileges>
    ON [ TABLE | FUNCTION | AGGREGATE ] <table or function or procedure or aggregate name>
    TO <user/role name(s) or PUBLIC >
    [ WITH GRANT OPTION ]

Add privileges on a table or function or procedure or aggregate function for a user or role or to PUBLIC.
For tables the privileges can be: INSERT, DELETE, UPDATE, SELECT, REFERENCES, TRUNCATE.
For aggregates, functions and procedures the privilege can be: EXECUTE.
The privileges COPY INTO, COPY FROM enable execution of those commands to users other than the monetdb system user.

Example:

GRANT SELECT ON TABLE web_event TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON TABLE web_event TO jan, piet, controller;
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 admin rights.
This COPY INTO and COPY FROM privileges are MonetDB specific SQL. They are introduced in Jun2016 release.

For details see: Documentation/SQLreference/Permissions
See also: REVOKE PRIVILEGES   GRANT ROLE   CREATE USER

Associated system table: sys.privileges
 

REVOKE PRIVILEGES

   REVOKE [ GRANT OPTION FOR ] <ALL or specific priviliges>
    ON [ TABLE | FUNCTION | AGGREGATE ] <table or function or procedure or aggregate name>
    FROM <user/role name(s) or PUBLIC>

Remove privileges on a table or function or procedure 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 admin rights.
This COPY INTO and COPY FROM privileges are MonetDB specific SQL. They are introduced in Jun2016 release.

For details see: Documentation/SQLreference/Permissions
See also: GRANT PRIVILEGES   REVOKE ROLE   DROP USER

Associated system table: sys.privileges
 

GRANT ROLE

   GRANT <role> TO <user name>
    [ WITH ADMIN OPTION ]
    [ WITH ADMIN <grantor> ]

Add role privileges to a user

Example:

GRANT controller TO jan WITH ADMIN OPTION;


For details see: Documentation/SQLreference/Permissions
and: Documentation/SQLreference/Roles
See also: REVOKE ROLE   GRANT PRIVILEGES   CREATE ROLE

Associated system table: sys.user_role
 

REVOKE ROLE

   REVOKE [ ADMIN OPTION FOR ] <role> FROM <user name(s)>

Remove role privileges from a user

Example:

REVOKE controller FROM jan;


For details see: Documentation/SQLreference/Permissions
and: Documentation/SQLreference/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/Schema
and: Documentation/Manuals/SQLreference/Variables
See also: CURRENT_SCHEMA

Associated system table: sys.var_values
 

SET ROLE

   SET ROLE <role>

Change the current role

Example:

SELECT CURRENT_ROLE;
SET ROLE controller;
SELECT CURRENT_ROLE;


For details see: Documentation/SQLreference/Roles
and: Documentation/Manuals/SQLreference/Variables
See also: CURRENT_ROLE   GRANT ROLE

Associated system table: sys.var_values
 

SET USER

   SET 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/Users
and: Documentation/Manuals/SQLreference/Variables
See also: USER   GRANT PRIVILEGES

Associated system table: sys.var_values
 

SET CURRENT_USER

   SET CURRENT_USER = <user 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/Users
and: Documentation/Manuals/SQLreference/Variables
See also: CURRENT_USER

Associated system table: sys.var_values
 

SET SESSION_USER

   SET SESSION_USER = <user name>

Change the session user to the specified user

Example:

SET SESSION_USER = isabel;
SELECT SESSION_USER;


For details see: Documentation/SQLreference/Users
and: Documentation/Manuals/SQLreference/Variables
See also: SESSION_USER

Associated system table: sys.var_values
 

SET SESSION AUTHORIZATION

   SET SESSION AUTHORIZATION <role>

Change the session authorization to the specified role

For details see: Documentation/SQLreference/Roles
and: Documentation/Manuals/SQLreference/Variables
 

COPY INTO FROM

   COPY [ int_val [ OFFSET int_val ] RECORDS ]
     INTO  [ <schema name> . ] <table name>
        [ '(' column_name ',' ... ')' ]
    FROM <file name> [ ',' <file name> [ ... ] ]
     [ '(' header [ STRING ] ',' ... ')' ]
     [ [ USING ] DELIMITERS  field_separator
       [ ',' record_separator [ ',' string_quote ] ] ]
     [ NULL [ AS ] null_string ]
     [ LOCKED ]
     [ BEST EFFORT ]
     [ NO CONSTRAINT ]

Read data from an external csv/txt file and bulk insert it into an existing table

Note: This command is MonetDB specific SQL.
The file must be accesssable from the server where mserver5 process is running. It can read directly from a compressed file if the name has one of suffix: .gz, .bz2, .xz or .zip.
The default field_separator is the pipe '|' character.
The default record_separator is the newline '
' character.
The default string_quote character is a double quote: ".
IMPORTANT: All character string data in the csv/txt file must be stored in UTF-8 character encoding. Other character encodings are not supported.

For details see: Documentation/Cookbooks/SQLrecipes/CSV_bulk_loads
and: Documentation/Cookbooks/SQLrecipes/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 ',' ... ')' ]
    FROM STDIN
     [ '(' header [ STRING ] ',' ... ')' ]
     [ [ USING ] DELIMITERS  field_separator
       [ ',' record_separator [ ',' string_quote ] ] ]
     [ NULL [ AS ] null_string ]
     [ LOCKED ]
     [ BEST EFFORT ]
     [ NO CONSTRAINT ]

Read data from console (stdin) and bulk insert it into an existing table

Note: This command is MonetDB specific SQL.
The default field_separator is the pipe '|' character.
The default record_separator is the newline '
' character.
The default string_quote character is a double quote: ".

For details see: Documentation/Cookbooks/SQLrecipes/CSV_bulk_loads
and: Documentation/Cookbooks/SQLrecipes/LoadingBulkData

Associated system table: sys.querylog_history
 

COPY BINARY INTO FROM

   COPY BINARY
    INTO [ <schema name> . ] <table name>
    FROM ( <file name> [ ',' <file name> [ ... ] ] )

Attach column data from an external binary file(s) to very quickly make it available in the RDBMS

Note: This command is MonetDB specific SQL.
The files must be accesssable from the server where mserver5 process is running.
IMPORTANT: not all datatypes are supported. Also a NULL value must be represented as a special value, which is different per datatype.

For details see: Documentation/Cookbooks/SQLrecipes/BinaryBulkLoad

Associated system table: sys.querylog_history
 

COPY LOADER INTO FROM

   COPY LOADER INTO [ <schema name> . ] <table name>
    FROM <loader function name>( [ <args> ] )

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

Example:

COPY LOADER INTO tbl FROM partial_loader();


Note: This command is MonetDB specific SQL. It is introduced in Dec2016 release.
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 <data query> INTO <file name>
    [ [USING] DELIMITERS field_separator
      [ ',' record_separator [ ',' string_quote ] ] ]
    [ NULL AS null_string ]

Write query result data in bulk to an external csv/txt file

Note: This command is MonetDB specific SQL.
The file path must be accesssable from the server where mserver5 process is running.
The default field_separator is the pipe '|' character.
The default record_separator is the newline '
' 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 one of suffix: .gz, .bz2, .xz or .zip.

For details see: Documentation/Cookbooks/SQLrecipes/ExportingBulkData
and: Documentation/Manuals/SQLreference/CopyInto

Associated system table: sys.querylog_history
 

COPY INTO STDOUT

   COPY <data query> INTO STDOUT
    [ [USING] DELIMITERS field_separator
      [ ',' record_separator [ ',' string_quote ] ] ]
    [ NULL AS null_string ]

Write query result data to console (stdout)

Note: This command is MonetDB specific SQL.
The default field_separator is the pipe '|' character.
The default record_separator is the newline '
' character.
The default string_quote character is a double quote: ".

For details see: Documentation/Cookbooks/SQLrecipes/ExportingBulkData
and: Documentation/Manuals/SQLreference/CopyInto

Associated system table: sys.querylog_history
 

SET TRANSACTION

   SET TRANSACTION
    [ READ ONLY | READ WRITE | [ ISOLATION LEVEL [ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE ] ] ]

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 ] ] ]

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

 

START TRANSACTION

   START TRANSACTION

Start a user controlled transaction

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

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

COMMIT

   COMMIT

Make all changes done since the start of the transaction persistent

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

ROLLBACK

   ROLLBACK

Undo all changes done since the start of the transaction

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

SAVEPOINT

   SAVEPOINT <svpt 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 TO SAVEPOINT <svpt name>

Undo all changes done since the start of the savepoint

Example:

ROLLBACK TO SAVEPOINT sp1;


See also: SAVEPOINT   RELEASE SAVEPOINT
 

RELEASE SAVEPOINT

   RELEASE SAVEPOINT <svpt name>

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

Example:

RELEASE SAVEPOINT sp1;


See also: SAVEPOINT   ROLLBACK TO SAVEPOINT
 

SET OPTIMIZER

   SET optimizer = <(name of) optimizer pipeline>

Change the execution optimizer pipeline

Example:

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


For details see: Documentation/Cookbooks/SQLrecipes/OptimizerPipelines
and: Documentation/Manuals/MonetDB/Optimizers
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/Manuals/SQLreference/Explain
and: Documentation/Cookbooks/SQLrecipes/OptimizerPipelines
 

PLAN

   PLAN <The SQL command>

Show the relational execution plan for the SQL command

Note: This command is MonetDB specific SQL.

For details see: Documentation/Manuals/SQLreference/PlanSQL
and: Documentation/Cookbooks/SQLrecipes/OptimizerPipelines
 

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/Manuals/SQLreference/Trace

Associated system table: sys.tracelog()
 

DEBUG

   DEBUG <The SQL command>

Executes the SQL command in DEBUG mode

For details see: Documentation/Manuals/SQLreference/Debug
 

ANALYZE

   ANALYZE <schemaname> [ '.' <tablename> [ '( '<columnname> [, <columnname>] ')' ] ]
    [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/Cookbooks/SQLrecipes/statistics

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/Cookbooks/SQLrecipes/DateTimeFunctionality
See also: SET CURRENT_TIMEZONE

Associated system table: sys.var_values
 

NOW

   NOW

Example:

SELECT NOW;


For details see: Documentation/Cookbooks/SQLrecipes/DateTimeFunctionality
 

CURRENT_DATE

   CURRENT_DATE

Example:

SELECT CURRENT_DATE;


For details see: Documentation/Cookbooks/SQLrecipes/DateTimeFunctionality
 

CURRENT_TIME

   CURRENT_TIME

Example:

SELECT CURRENT_TIME;


For details see: Documentation/Cookbooks/SQLrecipes/DateTimeFunctionality
 

CURRENT_TIMESTAMP

   CURRENT_TIMESTAMP

Example:

SELECT CURRENT_TIMESTAMP;


For details see: Documentation/Cookbooks/SQLrecipes/DateTimeFunctionality
 

LOCALTIME

   LOCALTIME

Example:

SELECT LOCALTIME;


For details see: Documentation/Cookbooks/SQLrecipes/DateTimeFunctionality
 

LOCALTIMESTAMP

   LOCALTIMESTAMP

Example:

SELECT LOCALTIMESTAMP;


For details see: Documentation/Cookbooks/SQLrecipes/DateTimeFunctionality
 

NEXT VALUE FOR

   NEXT VALUE FOR <sequencename>

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);
INSERT INTO test(v) VALUES ('ab');
SELECT * FROM test;


For details see: Documentation/Manuals/SQLreference/SerialTypes