Reference

Reference giulia Mon, 02/24/2020 - 11:43

Here you will find some man pages for MonetDB applications

MonetDB Client Applications

MonetDB Client Applications giulia Mon, 02/24/2020 - 15:03

Under constrruction

mclient

mclient sjoerd Tue, 04/26/2011 - 12:34

NAME

mclient − the MonetDB command-line tool

SYNOPSIS

mclient [ options ] [ file or database [ file ... ] ]
mclient −−help

DESCRIPTION

MonetDB is a database management system that is developed from a main-memory perspective with use of a fully decomposed storage model, automatic index management, extensibility of data types and search accelerators, and an SQL front end.

Mclient is the command-line interface to the MonetDB server.

If the −−statement=query (−s query) option is given, the query is executed. If any files are listed after the options, queries are read from the files and executed. The special filename refers to standard input. Note that if there is both a −−statement option and filename arguments, the query given with −−statement is executed first. If no −−statement option is given and no files are specified on the command line, mclient reads queries from standard input.

When reading from standard input, if standard input is a terminal or if the −−interactive (−i) option is given, mclient interprets lines starting with \ (backslash) specially. See the section BACKSLASH COMMANDS below.

Before mclient starts parsing command line options, it reads a .monetdb file. If the environment variable DOTMONETDBFILE is set, it reads the file pointed to by that variable instead. When unset, mclient searches for a .monetdb file in the current working directory, and if that doesn’t exist, in the current user’s home directory. This file can contain defaults for the flags user, password, language, database, save_history, format, and width. For example, an entry in a .monetdb file that sets the default language for mclient to mal looks like this: language=mal. To disable reading the .monetdb file, set the variable DOTMONETDBFILE to the empty string in the environment.

OPTIONS

General Options
−−help
(−?)

Print usage information and exit.

−−version (−v)

Print version information and exit.

−−encoding=encoding (−E encoding)

Specify the character encoding of the input. The option applies to both the standard input of mclient and to the argument of the −−statement (−s) option but not to the contents of files specified on the command line (except for which refers to standard input) or files specified using the \< command (those must be encoded using UTF-8). The default encoding is taken from the locale.

−−language=language (−l language)

Specify the query language. The following languages are recognized: mal and sql. A unique prefix suffices. When the −−language option is omitted, the default of sql is assumed.

−−database=database (−d database)

Specify the name or URI of the database to connect to. The −d can be omitted if an equally named file does not exist in the current directory. As such, the first non-option argument will be interpreted as database to connect to if the argument does not exist as file. Valid URIs are as returned by ‘monetdb discover‘, see monetdb(1), and look like mapi:monetdb://hostname:port/database.

−−host=hostname (−h hostname)

Specify the name of the host on which the server runs (default: localhost). When the argument starts with a forward slash (/), host is assumed to be the directory where the UNIX sockets are stored for platforms where these are supported.

−−port=portnr (−p portnr)

Specify the portnumber of the server (default: 50000).

−−interactive (−i)

When reading from standard input, interpret lines starting with \ (backslash) specially. See the section BACKSLASH COMMANDS below.

−−timer=timermode (−t timermode)

The timer command controls the format of the time reported for queries. The default mode is none which turns off timing reporting. The timer mode clock reports the client-side wall-clock time ("clk") in a human-friendly format. The timer mode performance reports client-side wall-clock time ("clk") as well as detailed server-side timings, all in milliseconds (ms): the time to parse the SQL query, optimize the logical relational plan and create the initial physical (MAL) plan ("sql"); the time to optimize the physical (MAL) plan ("opt"); the time to execute the physical (MAL) plan ("run"). All timings are reported on stderr.
Note that the client-measured wall-clock time is reported per query only when options −−interactive or −−echo are used, because only then does mclient send individual lines (statements) of the SQL script to the server. Otherwise, when mclient sends the SQL script in large(r) batch(es), only the total wall-clock time per batch is measured and reported. The server-measured detailed performance timings are always measured and reported per query.

−−user=user (−u user)

Specify the user to connect as. If this flag is absent, the client will ask for a user name, unless a default was found in .monetdb file.

−−format=format (−f format)

Specify the output format. The possible values are sql, expanded, x, csv, tab, raw, xml, and trash. csv is comma-separated values, tab is tab-separated values, raw is no special formatting (data is dumped the way the server sends it to the client), sql is a pretty format which is meant for human consumption where columns are clearly shown, expanded and x are synonyms and are another pretty format meant for human consumption where column values are printed in full and below each other, xml is a valid (in the XML sense) document, and trash does not render any output, enabling performance measurements free of any output rendering/serialization costs. In addition to plain csv, two other forms are possible. csv=c uses c as column separator; csv+c uses c as column separator and produces a single header line in addition to the data.

−−echo (−e)

Echo the query. Note that using this option slows down processing.

−−history (−H)

Load and save the command line history (default off).

−−log=logfile (−L logfile)

Save client/server interaction in the specified file.

−−statement=stmt (−s stmt)

Execute the specified query. The query is run before any queries from files specified on the command line are run.

−−timezone (−z)

Do not tell the client’s timezone to the server.

−−Xdebug (−X)

Trace network interaction between mclient and the server.

−−pager=cmd (−| cmd)

Send query output through the specified cmd. One cmd is started for each query. Note that the | will have to be quoted or else the shell will interpret it.

SQL Options
−−null=
nullstr (−n nullstr)

Set the string to be used as NULL representation when using the sql, csv, or tab output formats. If not used, NULL values are represented by the string "null" in the sql output format, and as the empty string in the csv and tab output formats. Note that an argument is required, so in order to use the empty string, use −n "" (with the space) or −−null=.

−−autocommit (−a)

Switch autocommit mode off. By default, autocommit mode is on.

−−allow−remote (−R)

Allow remote content (URLs) in the COPY INTO table FROM file ON CLIENT ... query. Remote content is retrieved by mclient.

−−rows=nr (−r nr)

If specified, query results will be paged by an internal pager at the specified number of lines.

−−width=nr (−w nr)

Specify the width of the screen. The default is the (initial) width of the terminal.

−−dump (−D)

Create an SQL dump.

−−inserts (−N)

Use INSERT INTO statements instead of COPY INTO + CSV values when dumping the data of a table. This option can be used when trying to load data from MonetDB into another database, or when e.g. JDBC applications are used to reload the dump.

BACKSLASH COMMANDS

General Commands

 

\?

 

Show a help message explaining the backslash commands.

 

\q

 

Exit mclient.

\< file

Read input from the named file.

\> file

Write output to the named file. If no file is specified, write to standard output.

\| command

Pipe output to the given command. Each query is piped to a new invocation of the command. If no command is given, revert to writing output to standard output.

 

\h

 

Show the readline(3) history.

 

\L file

Log client/server interaction in the given file. If no file is specified, stop logging information.

 

\X

 

Trace what mclient is doing. This is mostly for debugging purposes.

 

\e

 

Echo the query in SQL formatting mode.

\f format

Use the specified format mode to format the output. Possible modes the same as for the −−format (−f) option.

\w width

Set the maximum page width for rendering in the sql formatting mode. If width is −1, the page width is unlimited, when width is 0, use the terminal width. If width is greater than 0, use the given width.

\r rows

Use an internal pager using rows per page. If rows is −1, stop using the internal pager.

SQL Commands

 

\D

 

Dump the complete database. This is equivalent to using the program msqldump(1).

\D table

Dump the given table.

 

\d

 

Alias for \dvt.

 

\d[Stvsfn]+

List database objects of the given type. Multiple type specifiers can be used at the same time. The specifiers S, t, v, s, f and n stand for System, table, view, sequence, function and schema respectively. Note that S simply switches on viewing system catalog objects, which is orthogonal to the other specifiers.

\d[Stvsfn]+ object

Describe the given object in the database using SQL statements that reconstruct the object. The same specifiers as above can be used, following the same rules. When no specifiers are given, vt is assumed. The object can be given with or without a schema, separated by a dot. The object name can contain the wildcard characters * and _ that represent zero or more, and exactly one character respectively. An object name is converted to lowercase, unless the object name is quoted by double quotes ("). Examples of this, are e.g. *.mytable, tabletype*, or "myschema.FOO". Note that wildcard characters do not work in quoted objects. Quoting follows SQL quoting rules. Arbitrary parts can be quoted, and two quotes following each other in a quoted string represent the quote itself.

 

\A

 

Enable auto commit mode.

 
 

\a

 

Disable auto commit mode.

 

EXAMPLES

Efficiently import data from a CSV (comma-separated values) file into a table. The file must be readable by the server. $file is the absolute path name of the file, $table is the name of the table, $db is the name of the database.

mclient −d $db −s "COPY INTO $table FROM ’$file’ USING DELIMITERS ’,’,E’\\n’,’\"’"

Efficiently import data from a CSV file into a table when the file is to be read by mclient (e.g. the server has no access to the file). $file is the (absolute or relative) path name of the file, $table is the name of the table, $db is the name of the database.

mclient −d $db −s "COPY INTO $table FROM STDIN USING DELIMITERS ’,’,E’\\n’,’\"’" − < $file

Note that in this latter case, if a count of records is supplied, it should be at least as large as the number of records actually present in the CSV file. This, because otherwise the remainder of the file will be interpreted as SQL queries.

Another, easier method to have the client read the file content is as follows:

mclient −d $db −s "COPY INTO $table FROM ’$file’ ON CLIENT USING DELIMITERS ’,’,E’\\n’,\"’"

In this case the value of $file can be a path name relative to the directory in which mclient was started. If, in addition, the option −−allow−remote is passed to mclient, the $file in the above query can also be a URL. It then has to have the form schema://string, e.g., https://www.example.org/dumpdata.csv.

See https://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto for more information about the COPY INTO query.

SEE ALSO

msqldump(1), mserver5(1)

msqldump

msqldump sjoerd Tue, 09/20/2011 - 14:45

NAME

msqldump − dump a MonetDB/SQL database

SYNOPSIS

msqldump [ options ] [ dbname ]

DESCRIPTION

MonetDB is a database management system that is developed from a main-memory perspective with use of a fully decomposed storage model, automatic index management, extensibility of data types and search accelerators, and an SQL front end.

Msqldump is the program to dump an MonetDB/SQL database. The dump can be used to populate a new MonetDB/SQL database.

Before msqldump starts parsing command line options, it reads a .monetdb file. If the environment variable DOTMONETDBFILE is set, it reads the file pointed to by that variable instead. When unset, msqldump searches for a .monetdb file in the current working directory, and if that doesn’t exist, in the current user’s home directory. This file can contain defaults for the flags user and password. To disable reading the .monetdb file, set the variable DOTMONETDBFILE to the empty string in the environment.

OPTIONS

−−help (−?)

Print usage information and exit.

−−database=database (−d database)

Specify the name of the database to connect to. The −d can be omitted if it is the last option.

−−host=hostname (−h hostname)

Specify the name of the host on which the server runs (default: localhost).

−−port=portnr (−p portnr)

Specify the portnumber of the server (default: 50000).

−−user=user (−u user)

Specify the user to connect as. If this flag is absent, the client will ask for a user name.

−−describe (−D)

Only dump the database schema.

−−inserts (−N)

When dumping the table data, use INSERT INTO statements, rather than COPY INTO + CSV values. INSERT INTO statements are more portable, and necessary when the load of the dump is processed by e.g. a JDBC application.

−−functions (−f)

Only dump functions definitions.

−−table=table (−t table)

Only dump the specified table.

−−quiet (−q)

Don’t print the welcome message.

−−Xdebug (−X)

Trace network interaction between mclient and the server.

SEE ALSO

mclient(1), mserver5(1)

MonetDB Server Applications

MonetDB Server Applications giulia Mon, 02/24/2020 - 15:17

Under construction

monetdb

monetdb sjoerd Tue, 04/26/2011 - 12:33

NAME

monetdb − control a MonetDB Database Server instance

SYNOPSIS

monetdb [ monetdb_options ] command [ command_options ] [ command_args ]

DESCRIPTION

Monetdb allows an administrator of the MonetDB Database Server to perform various operations on the databases in the server. It relies on monetdbd(1) running in the background for all operations.

OPTIONS

Monetdb_options affect all commands and control the general behavior of monetdb.

 

−q

 

Suppresses all standard progress messages, only writing output to stderr if an error occurred.

−h hostname

Connect to hostname instead of attempting a connection over the local UNIX socket. This allows monetdb to connect to a remote monetdbd(1). The use of this option requires −P (see below).

−p port

Connects to the given portnumber instead of the default (50000). Requires −h to be given as option too.

−P passphrase

Specifies the passphrase necessary to login to a remote monetdbd(1). This option requires −h to be given as well. A bad passphrase causes monetdb to fail to login, and hence fail to perform any remote action.

 

−v

 

Show version, equal to monetdb version.

 

COMMANDS

The commands for the monetdb utility are create, destroy, lock, release, status, start, stop, kill, profilerstart, profilerstop, set, get, inherit, discover, help, and version. The commands facilitate adding, removing, maintaining, starting and stopping a database inside the MonetDB Database Server.

For all commands, database arguments can be glob-like expressions. This allows to do wildcard matches. For details on the syntax, see EXPRESSIONS.
create [−m pattern] [−p password] database [database ...]

Initializes a new database in the MonetDB Database Server. A database created with this command makes it available under its database name, but not yet for use by clients, as the database is put into maintenance mode. This allows the database administrator to perform initialization steps before releasing it to users, unless the −p argument is supplied. See also monetdb lock. The name of the database must match the expression [A−Za−z0−9−_]+.
−m pattern

With the −m flag, instead of creating a database, a multiplex-funnel is created. See section MULTIPLEX-FUNNEL in monetdbd(1). The pattern argument is not fully the same as a pattern for connecting or discovery. Each parallel target for the multiplex-funnel is given as username+password@pattern sequence, separated by commas. Here the pattern is an ordinary pattern as would be used for connecting to a database, and can hence also be just the name of a database.

−p password

The −p flag allows to create a database with the given password for the monetdb user. Since this protects the database from being accessed via well-known credentials, the created database is not locked after creation. This way, a new database can be created and used right away using the password supplied.

destroy [−f] database [database ...]

Removes the given database, including all its data and logfiles. Once destroy has completed, all data is lost. Be careful when using this command.

 

−f

 

By default, a confirmation question is asked, however the −f option, when provided, suppresses this question and removal is executed right away. Note that without this option you cannot destroy a running database, bring it down first using the stop command.

lock database [database ...]

Puts the given database in maintenance mode. A database under maintenance can only be connected to by an administrator account (by default the monetdb account). A database which is under maintenance is not started automatically by monetdbd(1), the MonetDB Database Server, when clients request for it. Use the release command to bring the database back for normal usage. To start a database which is under maintenance for administrator access, the start command can be used.

release database [database ...]

Brings back a database from maintenance mode. A released database is available again for normal use by any client, and is started on demand. Use the lock command to take a database under maintenance.

status [−lc] [−s states] [database ...]

Shows the state of the given database, or, when none given, all known databases. Three modes control the level of detail in the displayed output. By default a condensed one-line output per database format is used. This output resembles pretty much the output of various xxxstat programs, and is ideal for quickly gaining an overview of the system state. The output is divided into four columns, name, state, health, and remarks. The state column contains two characters that identify the state of the database, based on Booting (starting up), Running, Stopped, Crashed and Locked (under maintenance). This is followed by the uptime when running. The health column contains the percentage of successful starts and stops, followed by the average uptime. The remarks column can contain arbitrary information about the database state, but usually contains the URI the database can be connected to.

 

−c

 

The −c flag shows the most used properties of a database. This includes the state of the database (running, crashed, stopped), whether it is under maintenance or not, the crash averages and uptime statistics. The crash average is the number of times the database has crashed over the last 1, 15 or 30 starts. The lower the average, the healthier the database is.

 

−l

 

Triggered by the −l flag, a long listing is used. This listing spans many rows with on each row one property and its value separated by a colon (:). The long listing includes all information that is available.

 

−s

 

The −s flag controls which databases are being shown, matching their state. The required argument to this flag can be a combination of any of the following characters. Note that the order in which they are put also controls the order in which the databases are printed. b, r, s, c, and l are used to print a starting up (booting), started (running), stopped, crashed and locked database respectively. The default order which is used when the −s flag is absent, is rbscl.

start [−a] database [database ...]
stop [−a] database [database ...]
kill [−a] database [database ...]

Starts, stops or kills the given database, or, when −a is supplied, all known databases. The kill command immediately sends a SIGKILL and should only be used as last resort for a database that doesn’t respond any more. Killing a database may result in (partial) data loss. It is more common to use the stop command to stop a database. It will first attempt to stop the database, waiting for mero_exittimeout seconds and if that fails, kill the database. When using the start command, monetdb(1) will output diagnostic messages if the requested action failed. When encountering an error, one should always consult the logfile of monetdbd(1) for more details. For the kill command a diagnostic message indicating the database has crashed is always emitted, due to the nature of that command. Note that in combination with −a the return code of monetdb(1) indicates failure if one of the databases had a failure, even though the operation on other databases was successful.

profilerstart database [database ...]
profilerstop database [database ...]

Starts or stops the collection of profiling logs for the given database. The property profilerlogpath must be set for the given database, and it should point to a directory where the logs will be gathered. The filenames of the logs have the format: proflog_<database>_YYYY-MM-DD_HH-MM-SS.json where the last part is the date and time when the collection started. Please note that a file recording the pid of the profiler is written in the log directory, therefore each database needs to have a different profilerlogpath value.

get <all | property[,property[,..]]> [database ...]

Prints the requested properties, or all known properties, for the given database. For each property its source and value are printed. Source indicates where the current value comes from, e.g. the configuration file, or a local override.

set property=value database [database ...]

Sets property to value for the given database. For a list of properties, run monetdb get all. Most properties require the database to be stopped when set.
shared=<yes|no|tag>

Defines if and how the database is being announced to other monetdbds or not. If not set to yes or no the database is simply announced or not. Using a string, called tag the database is shared using that tag, allowing for more sophisticated usage. For information about the tag format and use, see section REMOTE DATABASES in the monetdbd(1) manpage. Note that this property can be set for a running database, and that a change takes immediate effect in the network.

nthreads=number

Defines how many worker threads the server should use to perform main processing. Normally, this number equals the number of available CPU cores in the system. Reducing this number forces the server to use less parallelism when executing queries, or none at all if set to 1.

optpipe=string

Each server operates with a given optimizer pipeline. While the default usually is the best setting, for some experimental uses the pipeline can be changed. See the mserver5(1) manpage for available pipelines. Changing this setting is discouraged at all times.

readonly=<yes|no>

Defines if the database has to be started in readonly mode. Updates are rejected in this mode, and the server employs some read-only optimizations that can lead to improved performance.

nclients=number

Sets the maximum amount of clients that can connect to this database at the same time. Setting this to a high value is discouraged. A multiplex-funnel may be more performant, see MULTIPLEX-FUNNEL below.

inherit property database [database ...]

Like set, but unsets the database-local value, and reverts to inherit from the default again.

discover [expression]

Returns a list of remote monetdbds and database URIs that were discovered by monetdbd(1). All databases listed can be connected to via the local MonetDB Database Server as if it were local databases using their database name. The connection is redirected or proxied based on configuration settings. If expression is given, only those discovered databases are returned for which their URI matches the expression. The expression syntax is described in the section EXPRESSIONS. Next to database URIs the hostnames and ports for monetdbds that allow to be controlled remotely can be found in the discover list masked with an asterisk. These entries can easily be filtered out using an expression (e.g. "mapi:monetdb:*") if desired. The control entries come in handy when one wants to get an overview of available monetdbds in e.g. a local cluster. Note that for monetdbd to announce its control port, the mero_controlport setting for that monetdbd must be enabled in the configuration file.

 

−h

 

help [command]

Shows general help, or short help for a given command.

 

−v

 

version

Shows the version of the monetdb utility.

EXPRESSIONS

For various options, typically database names, expressions can be used. These expressions are limited shell-globbing like, where the * in any position is expanded to an arbitrary string. The * can occur multiple times in the expression, allowing for more advanced matches. Note that the empty string also matches the *, hence "de*mo" can return "demo" as match. To match the literal ’*’ character, one has to escape it using a backslash, e.g. "\*".

RETURN VALUE

The monetdb utility returns exit code 0 if it successfully performed the requested command. An error caused by user input or database state is indicated by exit code 1. If an internal error in the utility occurs, exit code 2 is returned.

SEE ALSO

monetdbd(1), mserver5(1)

monetdbd

monetdbd sjoerd Tue, 04/26/2011 - 12:31

NAME

monetdbd − the MonetDB Database Server daemon

SYNOPSIS

monetdbd command [ command_args ] dbfarm

DESCRIPTION

Monetdbd is the MonetDB Database Server daemon. The program is mainly meant to be used as daemon, but it also allows to setup and change the configuration of a dbfarm. The use of monetdbd is either as user-oriented way to configure, start and stop a database farm, or to be started from a startup script, such as from /etc/init.d/ on Linux systems or smf(5) on Solaris systems, as part of a system startup.

Monetdbd is the system formerly known as merovingian. It was renamed to monetdbd since the name merovingian proved to be confusing to most regular end-users. Internally, monetdbd uses the name merovingian at many places for historical reasons.

A monetdbd instance manages one local cluster based, which is a directory in the system, referred to as the dbfarm. Nowadays, the dbfarm location always has to be given as argument to monetdbd.

Within its local cluster monetdbd takes care of starting up databases when necessary, and stopping them either upon request via monetdb(1) or when being shut down. Client database connections are made against monetdbd initially which redirects or proxies the client to the appropriate database process, started on the fly when necessary.

When started, monetdbd runs by default in the background, sending log messages to merovingian.log, until being sent a stop, terminate or interrupt signal, possibly using the stop command of monetdbd.

monetdbd uses a neighbor discovery scheme to detect other monetdbd processes running in the local network. Databases from those remote instances are made available to a locally connecting client. Remote databases never override local databases, and their availability is controlled by the remote monetdbd process. See also the sharing capabilities of monetdb(1) and the REMOTE DATABASES section below.

COMMANDS

The commands for monetdbd are create, start, stop, get, set, version, and help. The commands facilitate initializing a dbfarm, starting and stopping the MonetDB Database Server, and retrieving or setting options.
create dbfarm

Initializes a new database farm, such that a MonetDB Database Server can be started on that location. All necessary directories are attempted to be created, and an initial properties file is created in the directory itself. dbfarm must be a location addressable in the local filesystem hierarchy.

start [−n] dbfarm

Starts monetdbd, the MonetDB Database Server, on the given dbfarm. When the −n flag is given, monetdbd will not fork into the background, but instead remain attached to the calling environment, until given a stop signal.

stop dbfarm

Sends a stop signal to the monetdbd process responsible for the given dbfarm.

get <all | property[,property[,..]]> dbfarm

Prints the requested properties, or all known properties, for the given dbfarm. For each property, its value is printed. Some properties are virtual, and given for information purposes only, they cannot be modified using the set command.

set property=value dbfarm

Sets property to value for the given database. For a list of properties, run monetdbd get all. Some properties require a restart of the MonetDB Database Server in order to take effect. The set command, will however always write the property, and tell the running monetdbd to reload the properties file (if running). For an explanation of the properties, see the CONFIGURATION section below.

CONFIGURATION

Monetdbd reads its properties from the .merovingian_properties file inside the dbfarm. This file is created by the create command. This file is not meant to be edited manually, instead it should be updated using the set command. The following properties can be set:
logfile

This property points to the file where all log messages are written to. It is relative to the dbfarm directory, but can be absolute to point to e.g. another medium. Changing this property takes effect immediately at runtime.

pidfile

Monetdbd stores the process ID of the background server in the file pointed to by this property. The same rules apply as for the logfile property.

sockdir

For faster access, monetdbd uses UNIX domain sockets for its control mechanism and regular database connections. The sockets are placed as files in the filesystem hierarchy. The sockdir property controls in which directory they are placed. In general this setting should not be changed.

 

port

 

This property specifies which TCP port monetdbd should listen to for connection requests. Defaults to 50000.

 

ipv6

 

This property forces monetdbd to bind connections on IPv6 addresses only. Defaults to false.

listenaddr

This property specifies an address that is allowed to connect to the server. The user can specify one IP(v6) address, or use the 0.0.0.0 notation to allow connections from everywhere. Defaults to localhost (127.0.0.1).

control

For remote management of monetdbd, the control property specifies whether or not to enable remote management. Note that for remote management, a passphrase is required, see below. It defaults to false for security reasons. Changing this property takes effect immediately at runtime.

passphrase

To control monetdbd from a remote machine, a passphrase is necessary, to be given to monetdb(1). The passphrase can be either given as hashed value prefixed by the hash type in curly braces (e.g. {SHA512}xxx...) or as plain text value which will be hashed automatically. Note that the only hash accepted is the one specified at configure time, which is SHA512. Changing this property takes effect immediately at runtime.

discovery

Specifies whether neighbor discovery is to be enabled using UDP broadcasts or not. The broadcasts are done on the same portnumber as the port setting.

discoveryttl

Monetdbd publishes locally available databases to others periodically. The interval used here, defined in seconds, depends on the time-to-live of the databases before they need to get refreshed. The default is 600 seconds (10 minutes), which should keep traffic in your network fairly low. Additions and removals are processed immediately regardless of this timeout. If you are in a network environment where physical network links disappear often, you may want to decrease this value to more quickly remove no longer reachable databases.

exittimeout

mservers that were started by the MonetDB Database Server are shut down when monetdbd is shut down. Setting the exittimeout property to a positive non-zero value will shut down each running mserver with the given time-out in seconds. If the time-out expires, the mserver process is killed using the SIGKILL signal. A time-out value of 0 means no mservers will be shut down, and hence they will continue to run after monetdbd has shut down. Note that this particular configuration is extremely inconvenient. The default time-out is 60 seconds. If your databases are rather large and find your databases consistently being killed by monetdbd upon shutdown, you may want to increase this time-out. Changing this property takes effect immediately at runtime.

forward

monetdbd has two ways in which it can "attach" a connecting client to the target database. The first method, redirect, uses a redirect sent to the client with the responsible mserver process. The second method, proxy, proxies the client to the mserver over monetdbd. While redirect is more efficient, it requires the connecting client to be able to connect to the mserver. In many settings this may be undesirable or even impossible, since a wide range of open ports and routing are necessary for this. In such case the proxy technique of monetdbd is a good solution, which also allows a monetdbd instance on the border of a network to serve requests to nodes in the local (unreachable) network. Note that for local databases, the proxy method uses a UNIX domain socket feature to pass file-descriptors to the local mserver. This effectively is as efficient as the redirect approach, but still hides away the mservers properly behind monetdbd. Hence, in practice it is only relevant for connections to remote databases to use redirects instead of proxies. Changing this property takes effect immediately at runtime.

REMOTE DATABASES

The neighbor discovery capabilities of monetdbd allow a user to contact a remote database transparently, as if it were a local database. By default, all local databases are announced in the network, such that neighbors can pick them up to make them available for their local users. This feature can be disabled globally, or on database level. For the latter, the monetdb(1) utility can be used to change the share property of a database.

While neighbor discovery in itself is sufficient to locate a database in a cluster, it is limited in expressiveness. For instance, database names are assumed to be unique throughout the entire system. This means local databases overshadow remote ones, and duplicate remote entries cannot be distinguished. To compensate for this, monetdbd allows to adds a tag to each database that is being shared. This tag is sent in addition to the database name, and only understood by other monetdbds.

Tags are arbitrary ASCII-strings matching the pattern [A−Za−z0−9./]+. There are no assumed semantics in the tag, which allows for multiple approaches when using the tag. The tag is always used in combination with the database name. For this, the ‘/’ character is used as separator, which hence suggests the user to use that character as separator for multilevel tags. Monetdbd allows common path globbing using ‘*’ on tags, which allows for many use-cases. Consider for instance the following three databases with their tag:

dbX/master/tableQ
dbY/slave/tableQ
dbZ/slave/tableQ

A default match has implicit ‘/*’ added to the search, making more generic search strings match more specific ones. Hence, a connect with database dbX is the same as dbX/* and hence matches dbX/master/tableQ. Similar, a database connect for */master matches the same database as before. Note that the implicit ‘/*’ is not added if that would cause no matches, such as for */master/tableQ which would return all masters for tableQ, which in our hypothetical example is only dbX. In contrast, a database connect for */slave/tableQ matches with either dbY or dbZ. Monetdbd returns the two options to the client in a round-robin fashion, such that subsequent connects for the same pattern result in a load-balanced connect to either of both databases.

With tags in use, one can possibly make distinction between databases, if setup like that. The previous example could hence also be setup like this:

tableQ/master
tableQ/slave
tableQ/slave

Connecting to tableQ/slave would now return either of both databases even though they are not unique (apart from the host they are located on, which is not shown in the example). While being confusing for humans, for monetdbd it is the same situation as in the previous example. However, because globbing allows to make things easier to understand, tags for both slaves could be changed to slaveX or slave/X and use the necessary pattern to match them. It is up to the user to decide how to use the tags.

MULTIPLEX-FUNNELS

Monetdbd implements multiplex-funnel capabilities. As the name suggests two techniques are combined, the multiplexer and the funnel.

The funnel capability limits the access to the database to one client at a time. That is, if multiple clients connect to the funnel, their queries will be serialized such that they are executed one after the other. An effect of this approach is that clients no longer have an exclusive channel to the database, which means that individual queries from one client may have been interleaved with queries from others. This most notably makes SQL transaction blocks unreliable with a funnel. The funnel, hence, is meant to scale down a large amount of clients that perform short-running (read-only) queries, as typically seen in web-based query loads.

When a funnel is defined to use multiple databases, the funnel adds a multiplexer to its query channel. A multiplex-funnel sends each query to all of the defined databases. This behavior can be quite confusing at first, but proves to be useful in typical sharding configurations, where in particular simple selection queries have to be performed on each of the shards. The multiplexer combines the answers from all defined databases in one single answer that it sends back to the client. However, this combining is without any smart logic, that is, the multiplexer does not evaluate the query it is running, but just combines all answers it receives from the databases. This results in e.g. as many return tuples for a SELECT COUNT(*) query, as there are databases defined.

Due to the two above mentioned characteristics, a multiplex-funnel has some limitations. As mentioned before, transactions over multiple queries are likely not to result in the desired behavior. This is due to each query to the funnel is required to be self-contained. Further, since for each query, the results from multiple servers have to be combined into one, that query must only return a single response, i.e. multi-statement queries are most likely causing the funnel to respond with an error, or return garbled results. Last, the size of each query is limited to currently about 80K. While this size should be sufficient for most queries, it is likely not enough for e.g. COPY INTO statements. Apart from the data transfer implications, such statements should not be used with the funnel, as the results will be undefined due to the limited query buffer. Applications using the funnel should aim for short and single-statement queries that require no transactions.

See the create command in the monetdb(1) man-page for details on how to setup a multiplex-funnel.

SIGNALS

Monetdbd acts upon a number of signals as is common for a daemon.
SIGINT, SIGTERM, SIGQUIT

Any of these signals make monetdbd enter the shutdown sequence. This sequence involves cleanly shutting down listener sockets, shutting down all started databases and finally terminating itself.

 

SIGHUP

 

When this signal is received by monetdbd it will reopen the logfile as pointed to by the logfile setting. Before it reopens the logfile, it will re-read the properties file from the dbfarm, which might result in opening a different file to continue logging.

RETURN VALUE

Monetdbd returns exit code 0 if it was able to successfully perform the requested action, e.g. start, stop, etc. When an error occurs during the action, that prevents monetdbd from successfully performing the action, the exit code 1 is returned.

SEE ALSO

monetdb(1), mserver5(1)

mserver5

mserver5 sjoerd Tue, 04/26/2011 - 12:35

NAME

mserver5 − the MonetDB server version 5

SYNOPSIS

mserver5 [ options ]

DESCRIPTION

Mserver5 is the current MonetDB server that performs all processing on request of clients for a certain database.

Note that while mserver5 is the process that does the actual work, it is usually more common to start, monitor and connect to the mserver5 process through monetdbd(1).

This man-page describes the options that mserver5 understands. It is intended for people who really need to work with mserver5 itself. In regular cases, the programs monetdbd(1) and monetdb(1) control the many options, and allow to adjust them to appropriate values where sensible. For normal usage, it is preferred to apply any configuration through these programs.

OPERATION

When the build-time configuration did not disable this, a mserver5 process presents the user with a console prompt. On this prompt, MAL commands can be executed. The architecture is setup to handle multiple streams of requests. The first thread started represents the server, which is the console prompt, reading from standard input and writing to standard output.

The server thread started remains in existence until all other threads die. The server is stopped by sending it a termination signal (SIGINT, SIGTERM, SIGQUIT).

MSERVER5 OPTIONS

Mserver5 can be started with options as arguments.
−−dbpath=path

Path where mserver5 should find a database. Shorthand for option gdk_dbpath. Default value: ${prefix}/var/monetdb5/dbfarm/demo.

−−dbextra=path

Path where mserver5 should store transient data. Default value is the value of the −−dbpath option.

−−config=file

Config file to read options from. This file can contain all options as can be set with the --set flag. See CONFIG FILE FORMAT.

−−set option=value

Set individual configuration option. For possible options, see PARAMETERS sections.

 

−−help

 

Print list of options.

 

−−version

Print version and compile configuration.

−−in−memory

Run mserver5 in-memory. No data will be persisted.

−−trace

Print information about MAL instructions as they are executed.

−−verbose[=value]
−v[value]

Set or increase verbosity level. If no value is specified, the verbosity level is increased by 1. If a value is specified, the verbosity level is set to the given value.

−−debug[=value]
−d[value]

Set debug level. This is mostly for debugging purposes. The value is an integer, which can be (a bit-wise OR of):
1 = THRDMASK = thread-specific debug output
2 = CHECKMASK = property enforcing on new BATs
4 = MEMMASK = memory allocation
8 = PROPMASK = property checking on all values:
tells about wrongly set properties

16 = IOMASK = major IO activity
32 = BATMASK = BAT handling
128 = PARMASK = Thread management
512 = TMMASK = Transaction management
1024 = TEMMASK = Locks and Triggers
4096 = PERFMASK = BBP Performance (?)
8192 = DELTAMASK = Delta debugging (?)
16384 = LOADMASK = Module loading
2097152 = ALGOMASK = show join/select algorithm chosen
4194304 = ESTIMASK = show result size estimations
(for join, select)

16777216 = NOSYNCMASK = disable forcefully synchronizing files
to disk. If this flag is set, there is
no guarantee that the database remains
consistent after a crash. DO NOT USE
(unless you really don’t care about your
data).

33554432 = DEADBEEFMASK = disable "cleaning" of freed memory
in GDKfree() (e.g., for performance
measurements)

67108864 = ALLOCMASK = exhaustive GDK malloc & free tracing
for debugging (GDK developers, only)

134217728 = OPTMASK = trace the actions, decisions and
effects of MAL optimizers

268435456 = HEAPMASK = trace/debug HEAPextend;
used only for development & debugging

536870912 = FORCEMITOMASK = forcefully activate mitosis even on
small tables, i.e., split small tables
in as many (tiny) pieces as there are
cores (threads) available;
this allows us to test mitosis
functionality without requiring large
data sets (— at the expense of a
potentially significant interpretation
overhead for unnecessarily large plans);
used only for development & testing;
set automatically by Mtest.py

The value together with the = sign is optional. If not specified, it defaults to 1. In the short form −d, the value, if present, must immediately (i.e. without space) follow the option. The values of multiple instances of this flag are OR-ed together.

−−threads

Equivalent to −−debug=(THRDMASK | PARMASK).

−−memory

Equivalent to −−debug=(MEMMASK | ALLOCMASK).

−−properties

Equivalent to −−debug=(CHECKMASK | PROPMASK | BATMASK).

 

−−io

 

Equivalent to −−debug=(IOMASK | PERFMASK).

 

−−heaps

Equivalent to −−debug=(HEAPMASK).

−−transactions

Equivalent to −−debug=(TMMASK | DELTAMASK | TEMMASK).

−−modules

Equivalent to −−debug=(LOADMASK).

−−algorithms

Equivalent to −−debug=(ALGOMASK | ESTIMASK).

−−performance

Equivalent to −−debug=(DEADBEEFMASK).

−−optimizers

Equivalent to −−debug=(OPTMASK).

−−forcemito

Equivalent to −−debug=(FORCEMITOMASK | NOSYNCMASK).

MSERVER5 PARAMETERS

Mserver5 instructs the GDK kernel through the MAL (MonetDB Assembler Language) language. MonetDB 5 contains an extensive optimiser framework to transform MAL plans into more optimal or functional (e.g. distributed) plans. These parameters control behaviour on the MAL level.
monet_vault_key

The authorisation tables inside mserver5 can be encrypted with a key, such that reading the BATs does not directly disclose any credentials. The monet_vault_key setting points to a file that stores a secret key to unlock the password vault. It can contain anything. The file is read up to the first null-byte (’ ’), hence it can be padded to any length with trailing null-bytes to obfuscate the key length. Generating a key can be done for example by using a tool such as pwgen and adding a few of the passwords generated. Make sure not to choose a too small key. Note that on absence of a vault key file, some default key is used to encrypt the authorisation tables. Changing this setting (effectively changing the key) for an existing database makes that database unusable as noone is any longer able to login. If you use monetdbd(1), a per-database vault key is set.

max_clients

Controls how many client slots are allocated for clients to connect. This settings limits the maximum number of connected clients at the same time. Note that MonetDB is not designed to handle massive amounts of connected clients. The funnel capability from monetdbd(1) might be a more suitable solution for such workloads.

Default 64.

SQL PARAMETERS

The SQL component of MonetDB 5 runs on top of the MAL environment. It has its own SQL-level specific settings.
sql_debug

Enable debugging using a mask. This option should normally be disabled (0). Default: 0.

sql_optimizer

The default SQL optimizer pipeline can be set per server. See the optpipe setting in monetdb(1) when using monetdbd. During SQL initialization, the optimizer pipeline is checked against the dependency information maintained in the optimizer library to ensure there are no conflicts and at least the pre-requisite optimizers are used. The setting of sql_optimizer can be either the list of optimizers to run. Default: default_pipe.
The following are possible pipes to use:
minimal_pipe

The minimal pipeline necessary by the server to operate correctly. minimal_pipe=inline,remap,deadcode,multiplex,generator,profiler,candidates,garbageCollector

default_pipe

The default pipeline contains the mitosis-mergetable-reorder optimizers, aimed at large tables and improved access locality. default_pipe=inline,remap,costModel,coercions,aliases,evaluate,emptybind,pushselect,aliases,mitosis,mergetable,deadcode,aliases,constants,commonTerms,projectionpath,deadcode,reorder,matpack,dataflow,querylog,multiplex,generator,profiler,candidates,postfix,deadcode,wlc,garbageCollector

no_mitosis_pipe

The no_mitosis pipeline is identical to the default pipeline, except that optimizer mitosis is omitted. It is used mainly to make some tests work deterministically, and to check/debug whether "unexpected" problems are related to mitosis (and/or mergetable). no_mitosis_pipe=inline,remap,costModel,coercions,aliases,evaluate,emptybind,pushselect,aliases,mergetable,deadcode,aliases,constants,commonTerms,projectionpath,deadcode,reorder,matpack,dataflow,querylog,multiplex,generator,profiler,candidates,postfix,deadcode,wlc,garbageCollector

sequential_pipe

The sequential pipeline is identical to the default pipeline, except that optimizers mitosis & dataflow are omitted. It is use mainly to make some tests work deterministically, i.e., avoid ambigious output, by avoiding parallelism. sequential_pipe=inline,remap,costModel,coercions,aliases,evaluate,emptybind,pushselect,aliases,mergetable,deadcode,aliases,constants,commonTerms,projectionpath,deadcode,reorder,matpack,querylog,multiplex,generator,profiler,candidates,postfix,deadcode,wlc,garbageCollector

embedded_py

Enable embedded Python. This means Python code can be called from SQL. The value is true or 2 for embedded Python 2, or 3 for embedded Python 3. Note that by enabling embedded Python, users of the server are allowed to execute arbitrary Python code, and are therefore able to read and modify all data that the server process has access to. Also note that it is not possible to enable both Python 2 and 3 at the same time.

embedded_r=true

Enable embedded R. This means R code can be called from SQL. Note that by enabling embedded R, users of the server are allowed to execute arbitrary R code, and are therefore able to read and modify all data that the server process has access to.

embedded_c=true

Enable embedded C. This means C code can be called from SQL. The C code will first be compiled and then executed. This means a C compiler must be available. Note also that by enabling embedded C, users of the server are allowed to execute arbitrary C code, and are therefore able to read and modify all data that the server process has access to. In addition, if the C code causes a crash, all bets are off.

CONFIG FILE FORMAT

The configuration file readable by mserver5 consists of parameters of the form "name=value". The file is line-based, each newline-terminated line represents either a comment or a parameter. Only the first equals sign in a parameter is significant. Whitespace before or after the first equals sign is not stripped. Trailing whitespace in a parameter value is retained verbatim. Any line beginning with a hash (#) is ignored, as are lines containing only whitespace. The values following the equals sign in parameters are all a string where quotes are not needed, and if written be part of the string.

SEE ALSO

monetdbd(1), monetdb(1), mclient(1)

Readonly Database

Readonly Database mk Mon, 03/02/2020 - 11:15

When working in a regular mode, the query processing spans the basic column storage and the data changed by transactions (inserts, updates, and deletes). For instance, the inserted data, kept in a separate delta structure, are added to the column before other operations in the plan. In a 'read-only mode' modifying transactions are not allowed and the query processing spans only over the basic column storage. One of the consequences is that query execution may become faster.

The readonly mode is administered with a boolean variable, which  can be set by the system administrator using the monetdb tool:

shell> monetdb set readonly=yes <mydatabasename>

It ensures that all tables are accessed in read only mode.  Moreover, the database is protected against any catalog change, the user can not even create a temporary table for keeping an intermediate result around.

The alternative route is to deploy SQL schemas in combination with user authentication and access control grants to selectively allow users access to the database. See the corresponding description in the SQL manual.

SQL Syntax Overview

SQL Syntax Overview giulia Mon, 02/24/2020 - 11:46

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 TYPE EXTERNAL DROP TYPE
DECLARE

Data Manipulation Language (DML)

SELECT SELECT INTO WITH SELECT
INSERT VALUES INSERT SELECT
UPDATE
MERGE
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 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

EXPLAIN
PLAN
TRACE
DEBUG
ANALYZE
SET OPTIMIZER

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 ] schema_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 (11.27.1) onwards.

For details see: Documentation/SQLreference/Schema
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/Schema
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/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 . ] 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/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 . ] 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/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 . ] 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) using the ordinary DROP TABLE statement. There is no DROP MERGE TABLE statement.

For details see: Documentation/Cookbooks/SQLrecipes/DataPartitioning and updatable-merge-tables
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 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 remote_table URL
    [ WITH [USER 'user_login_name_nm']   [ [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.
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 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 . ] 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/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 . ] 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/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 . ] 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/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 [, 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/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. 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/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. 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/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 [, 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/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 . ] 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 done externally

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 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 . ] aggregate_name ( [ arg1_nm_type [ , arg2_nm_type ] [, ... ] ] )
    RETURNS datatype
    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 {
    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 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  or --set embedded_py=2  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 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 . ] 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 done externally

Example:

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


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.

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 . ] function_name ( [ arg1_nm_type [ , arg2_nm_type ] [, ... ] ] )
    RETURNS datatype
    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. 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 or --set embedded_py=2 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 done externally

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 [ 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=2  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/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 . ] procedure_name ( [ arg1_nm_type [ , arg2_nm_type ] [, ... ] ] )
    EXTERNAL NAME  MAL_procedure_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. 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/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 type_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. For examples see the C implementation of types: inet, json, url and uuid.

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

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 [ schema_name . ] 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 schema name of a full qualified trigger name must be the same as the schema name of the table.
The "OR REPLACE" option and the TRUNCATE trigger event are supported from release Mar2018 (11.29.3) 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 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 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 . ] 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 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 (11.29.3) 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 . ] 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
 

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 . ] 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/Manuals/SQLreference/SerialTypes
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/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 [ 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/Alter
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/Alter
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");


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/Alter
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/Alter
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/Alter
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/Alter
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/Alter
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/Cookbooks/SQLrecipes/DataPartitioning and updatable-merge-tables
and: Documentation/SQLreference/Alter
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/Alter
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/Cookbooks/SQLrecipes/DataPartitioning
and: Documentation/SQLreference/Alter
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/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 [ 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/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 [ 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/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 (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/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 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/Cookbooks/SQLrecipes/DataPartitioning
and: Documentation/Manuals/SQLreference/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/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 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/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 [ , 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/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.
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 [ , 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/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.
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 [ , 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/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.
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 . ] 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 [ 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/Manuals/SQLreference/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/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.
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 (11.29.3) 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 [ [ 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/TableExpressions , Documentation/Manuals/SQLreference/WindowFunctions ,
Documentation/Cookbooks/SQLrecipes/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/TableExpressions
and: Documentation/Cookbooks/SQLrecipes/monitor

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/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/Updates
and: Documentation/Cookbooks/SQLrecipes/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/Updates

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/Updates

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/Updates
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);


Note: The current 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. This is useful for statements which need to be executed many times but with different values, such as an INSERT command.

Example:

PREPARE INSERT INTO towns (name, country, citizens) VALUES (?, ?, ?);
EXECUTE 5('Amsterdam', 'NL', 856124);
EXECUTE 5('Berlin', 'DE', 3715930);

PREPARE SELECT id, name, schema_id FROM tables WHERE name like ?;
EXECUTE 7('%type%');
EXECUTE 7('%id%');


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 command.
Instead of keyword 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 [ , parm2_val ] [, ... ] ] )

Execute the prepared statement with parameter values

Example:

EXECUTE 3('c-data');
EXECUTE 5('Amsterdam', 'NL', 856124);


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

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/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 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/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 user_login_name
    RENAME TO new_user_login_name

Change the user id name of a database user 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 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/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/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/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 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/Users
and: Documentation/Manuals/SQLreference/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 | AGGREGATE ] 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/Permissions
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 ] 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/Permissions
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/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_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/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_name

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 = 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_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/Users
and: Documentation/Manuals/SQLreference/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/Users
and: Documentation/Manuals/SQLreference/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/Roles
and: Documentation/Manuals/SQLreference/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. 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/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 [ ',' 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/Cookbooks/SQLrecipes/CSV_bulk_loads
and: Documentation/Cookbooks/SQLrecipes/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/Cookbooks/SQLrecipes/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. Extension .zip is not recognised, use extension .gz instead.

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

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/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
    [ READ ONLY | READ WRITE ] [ ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } ]

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

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

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 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 TO SAVEPOINT savepoint_id_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 savepoint_id_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 = optimizer_pipeline_name

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    Documentation/Manuals/MonetDB/MALreference
and: Documentation/Cookbooks/SQLrecipes/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/Manuals/SQLreference/PlanSQL
and: Documentation/Cookbooks/SQLrecipes/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/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 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/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 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);
INSERT INTO test(v) VALUES ('ab');
SELECT * FROM test;


For details see: Documentation/Manuals/SQLreference/SerialTypes

Tracer

Tracer giulia Mon, 02/24/2020 - 12:04

Under construction