Data manipulation

Table updates

Data insertions

A table can be populated using an insert statement. It takes a table name and a value expression list. The expression result types should align with the attributes in the table definition. Otherwise the attribute-value association should be explicitly defined using a column name list. Multiple rows can be inserted in a single statement.

The result of a query can be inserted into a table, provided both source and destination are type compatible. Insertion of a table into itself effectively doubles its content, provided non of the table constraints is violated. MonetDB/SQL doest not support inserts through view definitions.

INSERT INTO table_name [ '(' column_name ',' ... ')' ] [ VALUES '('value_expression','...')' ',' ... | select_query ]

Data updates

The update statement syntax follows the SQL standard, but its semantics for bulk updates on keys may be slightly different than expected from other systems. In particular, the update implementation ensures that you can freely update any column without the danger of run-away values. MonetDB/SQL doest not support updates through views.

UPDATE table_name SET assignment ',' ... [WHERE search_condition ]

assignment : column_name '=' ( scalar_exp | search_condition | NULL )

Data deletions

DELETE FROM table_name [WHERE search_condition ]

MonetDB/SQL doest not support deletions through views.

Table expressions

When a query is created a table can be referenced in different ways, sometimes by its name or by a select query or a join result. Here is the syntax to refer a table.

table_reference: simple_table
    | '(' select_query ')' [AS] table_name [ '(' column_name ',' ... ')' ]
    | '(' select_query ')'
    | joined_table | '(' joined_table ')' [AS] table_name [ '(' column_name ',' ... ')' ]

joined_table: '(' joined_table ')'
    | table_reference { CROSS | NATURAL } JOIN table_reference
    | table_reference { JOIN | UNIONJOIN} table_reference join_spec
    | table_reference join_type JOIN table_reference join_spec
    | table_reference NATURAL join_type JOIN table_reference

join_spec : ON search_condition | USING '(colunm_name ',' ...' )'

join_type: INNER | { LEFT | RIGHT | FULL } [OUTER]

The SQL framework for table expressions is based on the select-from-where construct.

select_query:
    SELECT [ ALL | DISTINCT ] ( '*' | column_name ',' ... ) [ INTO column_name ',' ... ]
    [ FROM table_name ',' ... ]
    [ WHERE search_condition ]
    [ GROUP BY column_name ',' ... ]
    [ HAVING search_condition]
    [ ORDER BY ordering_spec ',' ... ]
    [ LIMIT posint ] [ OFFSET posint ]

The with clause provides the mechanism to introduce in-line view definitions:

with_clause : WITH with_element ',' ... select_query

with_element: table_name '(' column_name ',' ... ')' AS '(' select_query ')'

The pattern matching operations are used over predicates. It is possible to compare them, see the differences between them, if a predicate is a sub-predicate of another, etc. The following syntax description cover all the operations supported by MonetDB.

comparison_predicate:
    pred_expr COMPARISON pred_expr
    | pred_expr = pred_exp between_predicate:
    pred_expr [NOT] BETWEEN [SYMMETRIC | ASYMMETRIC] pred_expr AND pred_expr like_predicate:
    pred_expr [NOT] LIKE atom_expr

test_for_null:
column_name IS [NOT] NULL

in_predicate:
pred_expr [NOT] IN '(' element_name ',' ... ')' all_or_any_predicate :
pred_expr COMPARISON { ANY | ALL | SOME } subquery

existence_test:
    [NOT] EXISTS subquery pred_expr:
    scalar_expr
    | predicate

atom_expr:
    ['('] atom [')']
    | ['('] atom [')'] ESCAPE string
    | '?'

string_funcs:
      SUBSTRING ( scalar_expr FROM scalar_expr FOR scalar_expr ')'
    | SUBSTRING '(' scalar_expr ',' scalar_expr ',' scalar_expr ')'
    | SUBSTRING '(' scalar_exprFROM scalar_expr ')'
    | SUBSTRING '(' scalar_expr ',' scalar_expr ')'
    | SUBSTRING '(' scalar_expr int_value scalar_expr ')'
    | scalar_expr '||' scalar_expr

Bulk input/output

The COPY INTO command enables fast insertion of multiple tuples from an text file or standard input. Each tuple in the input file is terminated by a record-separator (default '\n') and fields are separated by the field separator (default '|'). The field values should adhere to the syntax for value literals. Alternative separators should adhere to the SQL lexical syntax for string values. A different NULL value representation can be passed using the NULL as null_string option. Furthermore, the fields are optionally enclosed with a user defined quote character. The text file should use UTF-8 encoding if specified as file_name, and the same encoding as mclient is using if read using FROM STDIN.

It is strongly advised to announce the maximum number of records to be inserted. It avoids guessing by the server and subsequent re-allocation of table space which may involve potentially expensive copying. A portion of the input file can be skipped using the offset feature. The offset value specifies the record at which loading should commence, the first record having offset 1.

The file read from should be accessible by the server and must use an absolute path name. The STDIN file designator reads data from the client application.

COPY [ int_val [ OFFSET int_val ] RECORDS ] INTO table_name
FROM ( file_name ',' ... | STDIN )
[ [USING] DELIMITERS field_separator [',' record_separator [ ',' string_quote ]]] [ NULL AS null_string ] [LOCKED]

The input syntax should comply to the following grammar: [ [ [quote] [[escape]char] * [quote]] feldspar] * record separator. Quote characters in quoted fields may be escaped with a backslash. Field and record separators can be embedded in quoted fields.

In many bulk loading situations, the original file can be saved as a backup or recreated for disaster handling. This reliefs the database system from having to prepare for recovery as well and to safe significant storage space. The LOCKED qualifier can be used in this situation (and in single user mode!) to skip the logging operation normally performed.

WARNING It is advised to add integrity constraints to the table after the file has been loaded. The ALTER statements perform bulk integrity checking and perform these checks often more efficiently.

Copy into File

The COPY INTO command with a file name argument allows for fast dumping of a result set into an ASCII file. The file should be accessible by the server and a full path name may be required. The file STDOUT can be used to direct the result to the primary output channel.

The delimiters and NULL AS arguments provide control over the layout required.

COPY subquery INTO file_name [ [USING] DELIMITERS field_separator [',' record_separator [ ',' string_quote ]]] [ NULL AS null_string ]

For both the input and output version of the COPY INTO command one can specify a file name ending with 'gz' or 'bz2' to use the appropriate compression library (if available).

Copying binary files

Migration of tables between MonetDB/SQL instances can be sped up using the binary COPY INTO/FROM format. See the recipe [1] for this functionality.