SQL Grammar
Index
Literals
Datetime fields
Other Grammar
Details
Click on the header of the grammar element to switch between railroad diagram and BNF.
Non-standard syntax is marked in green. Compatibility-only non-standard syntax is marked in red, don't use it unless you need it for compatibility with other databases or old versions of H2.
Literals
Value
A literal value of any data type, or null.
Example:
10
Approximate numeric
An approximate numeric value. Approximate numeric values have DECFLOAT data type. To define a DOUBLE PRECISION value, use CAST(X AS DOUBLE PRECISION). To define a REAL value, use CAST(X AS REAL). There are some special REAL, DOUBLE PRECISION, and DECFLOAT values: to represent positive infinity, use CAST('Infinity' AS dataType); for negative infinity, use CAST('-Infinity' AS dataType); for NaN (not a number), use CAST('NaN' AS dataType).
Example:
-1.4e-10
1.111_111E3
CAST(1e2 AS REAL)
CAST('NaN' AS DOUBLE PRECISION)
Array
ARRAY [ |
| ] |
An array of values.
Example:
ARRAY[1, 2]
ARRAY[1]
ARRAY[]
Boolean
TRUE | ||
FALSE | ||
UNKNOWN |
A boolean value. UNKNOWN is a NULL value with the boolean data type.
Example:
TRUE
Bytes
A binary string value. The hex value is not case sensitive and may contain space characters as separators. If there are more than one group of quoted hex values, groups must be separated with whitespace.
Example:
X''
X'01FF'
X'01 bc 2a'
X'01' '02'
Date
DATE ' |
| 2000-01-01 ' |
A date literal.
Example:
DATE '2004-12-31'
Date and time
A literal value of any date-time data type.
Example:
TIMESTAMP '1999-01-31 10:00:00'
Dollar Quoted String
$ $ anything $ $ |
A string starts and ends with two dollar signs. Two dollar signs are not allowed within the text. A whitespace is required before the first set of dollar signs. No escaping is required within the text.
Example:
$$John's car$$
Exact numeric
An exact numeric value. Exact numeric values with dot have NUMERIC data type, values without dot small enough to fit into INTEGER data type have this type, larger values small enough to fit into BIGINT data type have this type, others also have NUMERIC data type.
Example:
-1600.05
134_518.235_114
Hex Number
|
|
|
|
|
|
A number written in hexadecimal notation.
Example:
0xff
0x_ABCD_1234
Octal Number
|
|
| 0 - 7 |
|
|
A number written in octal notation.
Example:
0o664
0o_123_777
Binary Number
|
|
| 0 - 1 |
|
|
A number written in binary notation.
Example:
0b101
0b_01010101_10101010
Int
| number |
The maximum integer number is 2147483647, the minimum is -2147483648.
Example:
10
65_536
GEOMETRY
A binary string or character string with GEOMETRY object.
A binary string should contain Well-known Binary Representation (WKB) from OGC 06-103r4. Dimension system marks may be specified either in both OGC WKB or in PostGIS EWKB formats. Optional SRID from EWKB may be specified. POINT EMPTY stored with NaN values as specified in OGC 12-128r15 is supported.
A character string should contain Well-known Text Representation (WKT) from OGC 06-103r4 with optional SRID from PostGIS EWKT extension.
Example:
GEOMETRY 'GEOMETRYCOLLECTION (POINT (1 2))'
GEOMETRY X'00000000013ff00000000000003ff0000000000000'
JSON
A binary or character string with a RFC 8259-compliant JSON text and data format. JSON text is parsed into internal representation. Order of object members is preserved as is. Duplicate object member names are allowed.
Example:
JSON '{"id":10,"name":"What''s this?"}'
JSON '[1, ' '2]';
JSON X'7472' '7565'
Long
| number |
Long numbers are between -9223372036854775808 and 9223372036854775807.
Example:
100000
1_000_000_000
Null
NULL
NULL is a value without data type and means 'unknown value'.
Example:
NULL
Number
The maximum length of the number depends on the data type used.
Example:
100
10_000
Numeric
The data type of a numeric literal is the one of numeric data types, such as NUMERIC, DECFLOAT, BIGINT, or INTEGER depending on format and value.
An explicit CAST can be used to change the data type.
Example:
-1600.05
CAST(0 AS DOUBLE PRECISION)
-1.4e-10
999_999_999.999_999
String
| ||||||||||||||||||
|
A character string literal starts and ends with a single quote. Two single quotes can be used to create a single quote inside a string. Prefix N means a national character string literal; H2 does not distinguish regular and national character string literals in any way, this prefix has no effect in H2.
String literals staring with U& are Unicode character string literals. All character string literals in H2 may have Unicode characters, but Unicode character string literals may contain Unicode escape sequences \0000 or \+000000, where \ is an escape character, 0000 and 000000 are Unicode character codes in hexadecimal notation. Optional UESCAPE clause may be used to specify another escape character, with exception for single quote, double quote, plus sign, and hexadecimal digits (0-9, a-f, and A-F). By default the backslash is used. Two escape characters can be used to include a single character inside a string. Two single quotes can be used to create a single quote inside a string.
Example:
'John''s car'
'A' 'B' 'C'
U&'W\00f6rter ' '\\ \+01f600 /'
U&'|00a1' UESCAPE '|'
UUID
UUID ' |
|
| ' |
A UUID literal. Must contain 32 hexadecimal digits. Digits may be separated with - signs.
Example:
UUID '12345678-1234-1234-1234-123456789ABC'
Time
TIME |
| ' 12:00:00 |
| ' |
A time literal. A value is between 0:00:00 and 23:59:59.999999999 and has nanosecond resolution.
Example:
TIME '23:59:59'
Time with time zone
TIME WITH TIME ZONE ' 12:00:00 |
|
| ' |
A time with time zone literal. A value is between 0:00:00 and 23:59:59.999999999 and has nanosecond resolution.
Example:
TIME WITH TIME ZONE '23:59:59+01'
TIME WITH TIME ZONE '10:15:30.334-03:30'
TIME WITH TIME ZONE '0:00:00Z'
Timestamp
TIMESTAMP |
| ' |
| 2000-01-01 12:00:00 |
| ' |
A timestamp literal.
Example:
TIMESTAMP '2005-12-31 23:59:59'
Timestamp with time zone
A timestamp with time zone literal. If name of time zone is specified it will be converted to time zone offset.
Example:
TIMESTAMP WITH TIME ZONE '2005-12-31 23:59:59Z'
TIMESTAMP WITH TIME ZONE '2005-12-31 23:59:59-10:00'
TIMESTAMP WITH TIME ZONE '2005-12-31 23:59:59.123+05'
TIMESTAMP WITH TIME ZONE '2005-12-31 23:59:59.123456789 Europe/London'
Interval
An interval literal.
Example:
INTERVAL '1-2' YEAR TO MONTH
INTERVAL YEAR
INTERVAL |
| ' |
| yearInt ' YEAR |
|
An INTERVAL YEAR literal. If precision is specified it should be from 1 to 18.
Example:
INTERVAL '10' YEAR
INTERVAL MONTH
INTERVAL |
| ' |
| monthInt ' MONTH |
|
An INTERVAL MONTH literal. If precision is specified it should be from 1 to 18.
Example:
INTERVAL '10' MONTH
INTERVAL DAY
INTERVAL |
| ' |
| dayInt ' DAY |
|
An INTERVAL DAY literal. If precision is specified it should be from 1 to 18.
Example:
INTERVAL '10' DAY
INTERVAL HOUR
INTERVAL |
| ' |
| hourInt ' HOUR |
|
An INTERVAL HOUR literal. If precision is specified it should be from 1 to 18.
Example:
INTERVAL '10' HOUR
INTERVAL MINUTE
INTERVAL |
| ' |
| minuteInt ' MINUTE |
|
An INTERVAL MINUTE literal. If precision is specified it should be from 1 to 18.
Example:
INTERVAL '10' MINUTE
INTERVAL SECOND
An INTERVAL SECOND literal. If precision is specified it should be from 1 to 18. If fractional seconds precision is specified it should be from 0 to 9.
Example:
INTERVAL '10.123' SECOND
INTERVAL YEAR TO MONTH
INTERVAL |
| ' |
| yearInt - monthInt ' YEAR |
| TO MONTH |
An INTERVAL YEAR TO MONTH literal. If leading field precision is specified it should be from 1 to 18.
Example:
INTERVAL '1-6' YEAR TO MONTH
INTERVAL DAY TO HOUR
INTERVAL |
| ' |
| dayInt hoursInt ' DAY |
| TO HOUR |
An INTERVAL DAY TO HOUR literal. If leading field precision is specified it should be from 1 to 18.
Example:
INTERVAL '10 11' DAY TO HOUR
INTERVAL DAY TO MINUTE
INTERVAL |
| ' |
| dayInt 12:00:00 ' DAY |
| TO MINUTE |
An INTERVAL DAY TO MINUTE literal. If leading field precision is specified it should be from 1 to 18.
Example:
INTERVAL '10 11:12' DAY TO MINUTE
INTERVAL DAY TO SECOND
INTERVAL |
| ' |
| dayInt 12:00:00 |
| ' DAY |
|
TO SECOND |
|
An INTERVAL DAY TO SECOND literal. If leading field precision is specified it should be from 1 to 18. If fractional seconds precision is specified it should be from 0 to 9.
Example:
INTERVAL '10 11:12:13.123' DAY TO SECOND
INTERVAL HOUR TO MINUTE
INTERVAL |
| ' |
| 12:00:00 ' HOUR |
| TO MINUTE |
An INTERVAL HOUR TO MINUTE literal. If leading field precision is specified it should be from 1 to 18.
Example:
INTERVAL '10:11' HOUR TO MINUTE
INTERVAL HOUR TO SECOND
An INTERVAL HOUR TO SECOND literal. If leading field precision is specified it should be from 1 to 18. If fractional seconds precision is specified it should be from 0 to 9.
Example:
INTERVAL '10:11:12.123' HOUR TO SECOND
INTERVAL MINUTE TO SECOND
An INTERVAL MINUTE TO SECOND literal. If leading field precision is specified it should be from 1 to 18. If fractional seconds precision is specified it should be from 0 to 9.
Example:
INTERVAL '11:12.123' MINUTE TO SECOND
Datetime fields
Datetime field
yearField | ||
monthField | ||
dayOfMonthField | ||
hourField | ||
minuteField | ||
secondField | ||
timezoneHourField | ||
timezoneMinuteField | ||
Fields for EXTRACT, DATEADD, DATEDIFF, and DATE_TRUNC functions.
Example:
YEAR
Year field
YEAR | |||||||||||
|
Year.
Example:
YEAR
Month field
MONTH | |||||||||||
|
Month (1-12).
Example:
MONTH
Day of month field
DAY | |||||||||||
|
Day of month (1-31).
Example:
DAY
Hour field
HOUR | ||||||||
|
Hour (0-23).
Example:
HOUR
Minute field
MINUTE | |||||||||||
|
Minute (0-59).
Example:
MINUTE
Second field
SECOND | |||||||||||
|
Second (0-59).
Example:
SECOND
Timezone hour field
TIMEZONE_HOUR
Timezone hour (from -18 to +18).
Example:
TIMEZONE_HOUR
Timezone minute field
TIMEZONE_MINUTE
Timezone minute (from -59 to +59).
Example:
TIMEZONE_MINUTE
Timezone second field
TIMEZONE_SECONDTimezone second (from -59 to +59). Local mean time (LMT) used in the past may have offsets with seconds. Standard time doesn't use such offsets.
Example:
TIMEZONE_SECOND
Millennium field
MILLENNIUMCentury, or one thousand years (2001-01-01 to 3000-12-31).
Example:
MILLENNIUM
Century field
CENTURYCentury, or one hundred years (2001-01-01 to 2100-12-31).
Example:
CENTURY
Decade field
DECADEDecade, or ten years (2020-01-01 to 2029-12-31).
Example:
DECADE
Quarter field
QUARTERQuarter (1-4).
Example:
QUARTER
Millisecond field
MILLISECOND | ||
MS |
Millisecond (0-999).
Example:
MILLISECOND
Microsecond field
MICROSECOND | ||
MCS |
Microsecond (0-999999).
Example:
MICROSECOND
Nanosecond field
NANOSECOND | ||
NS |
Nanosecond (0-999999999).
Example:
NANOSECOND
Day of year field
| ||||||||
|
Day of year (1-366).
Example:
DAYOFYEAR
ISO day of week field
ISO_DAY_OF_WEEK | ||
ISODOW |
ISO day of week (1-7). Monday is 1.
Example:
ISO_DAY_OF_WEEK
ISO week field
ISO_WEEKISO week of year (1-53). ISO definition is used when first week of year should have at least four days and week is started with Monday.
Example:
ISO_WEEK
ISO week year field
ISO_WEEK_YEAR | ||||||||
|
Returns the ISO week-based year from a date/time value.
Example:
ISO_WEEK_YEAR
Day of week field
| ||||||||
DOW |
Day of week (1-7), locale-specific.
Example:
DAY_OF_WEEK
Week field
WEEK | |||||||||||
|
Week of year (1-53) using local rules.
Example:
WEEK
Week year field
WEEK_YEARReturns the week-based year (locale-specific) from a date/time value.
Example:
WEEK_YEAR
Epoch field
EPOCHFor TIMESTAMP values number of seconds since 1970-01-01 00:00:00 in local time zone. For TIMESTAMP WITH TIME ZONE values number of seconds since 1970-01-01 00:00:00 in UTC time zone. For DATE values number of seconds since 1970-01-01. For TIME values number of seconds since midnight.
Example:
EPOCH
Other Grammar
Alias
An alias is a name that is only valid in the context of the statement.
Example:
A
And Condition
Value or condition.
Example:
ID=1 AND NAME='Hi'
Array element reference
Returns array element at specified 1-based index. Returns NULL if array or json is null, index is null, or element with specified index isn't found in JSON.
Example:
A[2]
M[5][8]
Field reference
( expression ) . fieldName |
Returns field value from the row value or JSON value. Returns NULL if value is null or field with specified name isn't found in JSON. Expression on the left must be enclosed in parentheses if it is an identifier (column name), in other cases they aren't required.
Example:
(R).FIELD1
(TABLE1.COLUMN2).FIELD.SUBFIELD
JSON '{"a": 1, "b": 2}'."b"
Array value constructor by query
ARRAY ( query ) |
Collects values from the subquery into array.
The subquery should have exactly one column. Number of elements in the returned array is the number of rows in the subquery. NULL values are included into array.
Example:
ARRAY(SELECT * FROM SYSTEM_RANGE(1, 10));
Case expression
Performs conditional evaluation of expressions.
Example:
CASE A WHEN 'a' THEN 1 ELSE 2 END
CASE WHEN V > 10 THEN 1 WHEN V < 0 THEN 2 END
CASE WHEN A IS NULL THEN 'Null' ELSE 'Not null' END
Simple case
Returns then expression from the first when clause where one of its operands was was evaluated to TRUE for the case expression. If there are no such clauses, returns else expression or NULL if it is absent.
Plain expressions are tested for equality with the case expression, NULL is not equal to NULL. Right sides of conditions are evaluated with the case expression on the left side.
Example:
CASE CNT WHEN IS NULL THEN 'Null' WHEN 0 THEN 'No' WHEN 1 THEN 'One' WHEN 2, 3 THEN 'Few' ELSE 'Some' END
Searched case
Returns the first expression where the condition is true. If no else part is specified, return NULL.
Example:
CASE WHEN CNT<10 THEN 'Low' ELSE 'High' END
CASE WHEN A IS NULL THEN 'Null' ELSE 'Not null' END
Cast specification
CAST ( value AS dataTypeOrDomain |
| ) |
Converts a value to another data type. The following conversion rules are used: When converting a number to a boolean, 0 is false and every other value is true. When converting a boolean to a number, false is 0 and true is 1. When converting a number to a number of another type, the value is checked for overflow. When converting a string to binary, UTF-8 encoding is used. Note that some data types may need explicitly specified precision to avoid overflow or rounding.
Template may only be specified for casts from datetime data types to character string data types and for casts from character string data types to datetime data types.
'-', '.', '/', ',', '''', ';', ':' and ' ' (space) characters can be used as delimiters.
Y, YY, YYY, YYYY represent last 1, 2, 3, or 4 digits of year. YYYY, if delimited, can also be used to parse any year, including negative years. When a year is parsed with Y, YY, or YYY pattern missing leading digits are filled using digits from the current year.
RR and RRRR have the same meaning as YY and YYYY for formatting. When a year is parsed with RR, the resulting year is within current year - 49 years and current year + 50 years in H2, other database systems may use different range of years.
MM represent a month.
DD represent a day of month.
DDD represent a day of year, if this pattern in specified, MM and DD may not be specified.
HH24 represent an hour (from 0 to 23).
HH and HH12 represent an hour (from 1 to 12), this pattern may only be used together with A.M. or P.M. pattern. These patterns may not be used together with HH24.
MI represent minutes.
SS represent seconds of minute.
SSSSS represent seconds of day, this pattern may not be used together with HH24, HH (HH12), A.M. (P.M.), MI or SS pattern.
FF1, FF2, ..., FF9 represent fractional seconds.
TZH, TZM and TZH represent hours, minutes and seconds of time zone offset.
Multiple patterns for the same datetime field may not be specified.
If year is not specified, current year is used. If month is not specified, current month is used. If day is not specified, 1 is used.
If some fields of time or time zone are not specified, 0 is used.
Example:
CAST(NAME AS INT);
CAST(TIMESTAMP '2010-01-01 10:40:00.123456' AS TIME(6));
CAST('12:00:00 P.M.' AS TIME FORMAT 'HH:MI:SS A.M.');
Cipher
AESOnly the algorithm AES (AES-128) is supported currently.
Example:
AES
Column Definition
dataTypeOrDomain |
|
| ||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
|
|
|
|
|
|
|
The default expression is used if no explicit value was used when adding a row and when DEFAULT value was specified in an update command.
A column is either a generated column or a base column. The generated column has a generated column expression. The generated column expression is evaluated and assigned whenever the row changes. This expression may reference base columns of the table, but may not reference other data. The value of the generated column cannot be set explicitly. Generated columns may not have DEFAULT or ON UPDATE expressions.
On update column expression is used if row is updated, at least one column has a new value that is different from its previous value and value for this column is not set explicitly in update statement.
Identity column is a column generated with a sequence. The column declared as the identity column with IDENTITY data type or with IDENTITY () clause is implicitly the primary key column of this table. GENERATED ALWAYS AS IDENTITY, GENERATED BY DEFAULT AS IDENTITY, and AUTO_INCREMENT clauses do not create the primary key constraint automatically. GENERATED ALWAYS AS IDENTITY clause indicates that column can only be generated by the sequence, its value cannot be set explicitly. Identity column has implicit NOT NULL constraint. Identity column may not have DEFAULT or ON UPDATE expressions.
DEFAULT ON NULL makes NULL value work as DEFAULT value is assignments to this column.
The invisible column will not be displayed as a result of SELECT * query. Otherwise, it works as normal column.
Column constraint definitions are not supported for ALTER statements.
Example:
CREATE TABLE TEST(ID INT PRIMARY KEY,
NAME VARCHAR(255) DEFAULT '' NOT NULL);
CREATE TABLE TEST(ID BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
QUANTITY INT, PRICE NUMERIC(10, 2),
AMOUNT NUMERIC(20, 2) GENERATED ALWAYS AS (QUANTITY * PRICE));
Column Constraint Definition
constraintNameDefinition |
| ||||||||||
| ||||||||||
| ||||||||||
referencesSpecification | ||||||||||
|
NOT NULL disallows NULL value for a column.
PRIMARY KEY and UNIQUE require unique values. PRIMARY KEY also disallows NULL values and marks the column as a primary key. UNIQUE constraint allows NULL values, if nulls distinct clause is not specified, the default is NULLS DISTINCT, excluding some compatibility modes.
Referential constraint requires values that exist in other column (usually in another table).
Check constraint require a specified condition to return TRUE or UNKNOWN (NULL). It can reference columns of the table, and can reference objects that exist while the statement is executed. Conditions are only checked when a row is added or modified in the table where the constraint exists.
Example:
NOT NULL
PRIMARY KEY
UNIQUE
REFERENCES T2(ID)
CHECK (VALUE > 0)
Comment
bracketedComment | |||
| |||
|
Comments can be used anywhere in a command and are ignored by the database. Line comments -- and // end with a newline.
Example:
-- comment
/* comment */
Bracketed comment
/ * |
| * / |
Comments can be used anywhere in a command and are ignored by the database. Bracketed comments /* */ can be nested and can be multiple lines long.
Example:
/* comment */
/* comment /* nested comment */ comment */
Compare
| |||
| |||
| |||
= | |||
< | |||
> | |||
| |||
&& |
Comparison operator. The operator != is the same as <>. The operator && means overlapping; it can only be used with geometry types.
Example:
<>
Condition
| |||||||||||
| |||||||||||
| |||||||||||
| |||||||||||
Boolean value or condition.
NOT condition negates the result of subcondition and returns TRUE, FALSE, or UNKNOWN (NULL).
EXISTS predicate tests whether the result of the specified subquery is not empty and returns TRUE or FALSE.
UNIQUE predicate tests absence of duplicate rows in the specified subquery and returns TRUE or FALSE. If nulls distinct clause is not specified, NULLS DISTINCT is implicit.
INTERSECTS checks whether 2D bounding boxes of specified geometries intersect with each other and returns TRUE or FALSE.
Example:
ID <> 2
NOT(A OR B)
EXISTS (SELECT NULL FROM TEST T WHERE T.GROUP_ID = P.ID)
UNIQUE (SELECT A, B FROM TEST T WHERE T.CATEGORY = CAT)
INTERSECTS(GEOM1, GEOM2)
Condition Right Hand Side
The right hand side of a condition.
Example:
> 10
IS NULL
IS NOT NULL
IS NOT DISTINCT FROM B
IS OF (DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE)
IS JSON OBJECT WITH UNIQUE KEYS
LIKE 'Jo%'
Comparison Right Hand Side
Right side of comparison predicates.
Example:
> 10
Quantified Comparison Right Hand Side
Right side of quantified comparison predicates.
Quantified comparison predicate ALL returns TRUE if specified comparison operation between left size of condition and each row from a subquery or each element of array returns TRUE, including case when there are no rows (elements). ALL predicate returns FALSE if at least one such comparison returns FALSE. Otherwise it returns UNKNOWN.
Quantified comparison predicates ANY and SOME return TRUE if specified comparison operation between left size of condition and at least one row from a subquery or at least one element of array returns TRUE. ANY and SOME predicates return FALSE if all such comparisons return FALSE. Otherwise they return UNKNOWN.
Note that these predicates have priority over ANY and SOME aggregate functions with subquery on the right side. Use parentheses around aggregate function.
If version with array is required and this array is returned from a subquery, wrap this subquery with a cast to distinguish this operation from standard quantified comparison predicate with a query.
Example:
< ALL(SELECT V FROM TEST)
= ANY(ARRAY_COLUMN)
= ANY(CAST((SELECT ARRAY_COLUMN FROM OTHER_TABLE WHERE ID = 5) AS INTEGER ARRAY)
Null Predicate Right Hand Side
IS |
| NULL |
Right side of null predicate.
Check whether the specified value(s) are NULL values. To test multiple values a row value must be specified. IS NULL returns TRUE if and only if all values are NULL values; otherwise it returns FALSE. IS NOT NULL returns TRUE if and only if all values are not NULL values; otherwise it returns FALSE.
Example:
IS NULL
Distinct Predicate Right Hand Side
IS |
|
| operand |
Right side of distinct predicate.
Distinct predicate is null-safe, meaning NULL is considered the same as NULL, and the condition never evaluates to UNKNOWN.
Example:
IS NOT DISTINCT FROM OTHER
Quantified Distinct Predicate Right Hand Side
Right side of quantified distinct predicate.
Quantified distinct predicate is null-safe, meaning NULL is considered the same as NULL, and the condition never evaluates to UNKNOWN.
Quantified distinct predicate ALL returns TRUE if specified distinct predicate between left size of condition and each row from a subquery or each element of array returns TRUE, including case when there are no rows. Otherwise it returns FALSE.
Quantified distinct predicates ANY and SOME return TRUE if specified distinct predicate between left size of condition and at least one row from a subquery or at least one element of array returns TRUE. Otherwise they return FALSE.
Note that these predicates have priority over ANY and SOME aggregate functions with subquery on the right side. Use parentheses around aggregate function.
If version with array is required and this array is returned from a subquery, wrap this subquery with a cast to distinguish this operation from quantified comparison predicate with a query.
Example:
IS DISTINCT FROM ALL(SELECT V FROM TEST)
IS NOT DISTINCT FROM ANY(ARRAY_COLUMN)
IS NOT DISTINCT FROM ANY(CAST((SELECT ARRAY_COLUMN FROM OTHER_TABLE WHERE ID = 5) AS INTEGER ARRAY)
Boolean Test Right Hand Side
IS |
|
|
Right side of boolean test.
Checks whether the specified value is (not) TRUE, FALSE, or UNKNOWN (NULL) and return TRUE or FALSE. This test is null-safe.
Example:
IS TRUE
Type Predicate Right Hand Side
IS |
| OF ( dataType |
| ) |
Right side of type predicate.
Checks whether the data type of the specified operand is one of the specified data types. Some data types have multiple names, these names are considered as equal here. Domains and their base data types are currently not distinguished from each other. Precision and scale are also ignored. If operand is NULL, the result is UNKNOWN.
Example:
IS OF (INTEGER, BIGINT)
JSON Predicate Right Hand Side
IS |
| JSON |
|
|
Right side of JSON predicate.
Checks whether value of the specified string, binary data, or a JSON is a valid JSON. If ARRAY, OBJECT, or SCALAR is specified, only JSON items of the specified type are considered as valid. If WITH UNIQUE [ KEYS ] is specified only JSON with unique keys is considered as valid. This predicate isn't null-safe, it returns UNKNOWN if operand is NULL.
Example:
IS JSON OBJECT WITH UNIQUE KEYS
Between Predicate Right Hand Side
Right side of between predicate.
Checks whether the value is within the range inclusive. V BETWEEN [ ASYMMETRIC ] A AND B is equivalent to A <= V AND V <= B. V BETWEEN SYMMETRIC A AND B is equivalent to A <= V AND V <= B OR A >= V AND V >= B.
Example:
BETWEEN LOW AND HIGH
In Predicate Right Hand Side
| IN ( |
| ) |
Right side of in predicate.
Checks presence of value in the specified list of values or in result of the specified query.
Returns TRUE if row value on the left side is equal to one of values on the right side, FALSE if all comparison operations were evaluated to FALSE or right side has no values, and UNKNOWN otherwise.
This operation is logically equivalent to OR between comparison operations comparing left side and each value from the right side.
Example:
IN (A, B, C)
IN (SELECT V FROM TEST)
Like Predicate Right Hand Side
Right side of like predicate.
The wildcards characters are _ (any one character) and % (any characters). The database uses an index when comparing with LIKE except if the operand starts with a wildcard. To search for the characters % and _, the characters need to be escaped. The default escape character is \ (backslash). To select no escape character, use ESCAPE '' (empty string). At most one escape character is allowed. Each character that follows the escape character in the pattern needs to match exactly. Patterns that end with an escape character are invalid and the expression returns NULL.
ILIKE does a case-insensitive compare.
Example:
LIKE 'a%'
Regexp Predicate Right Hand Side
| REGEXP operand |
Right side of Regexp predicate.
Regular expression matching is used. See Java Matcher.find for details.
Example:
REGEXP '[a-z]'
Nulls Distinct
NULLS |
|
Are nulls distinct for unique constraint, index, or predicate.
If NULLS DISTINCT is specified, rows with null value in any column are distinct. If NULLS ALL DISTINCT is specified, rows with null value in all columns are distinct. If NULLS NOT DISTINCT is specified, null values are identical.
Treatment of null values inside composite data types is not affected.
Example:
NULLS DISTINCT
NULLS NOT DISTINCT
Table Constraint Definition
constraintNameDefinition |
| |||||||||||||||||||||||||||
| |||||||||||||||||||||||||||
referentialConstraint | |||||||||||||||||||||||||||
|
Defines a constraint.
PRIMARY KEY and UNIQUE require unique values. PRIMARY KEY also disallows NULL values and marks the column as a primary key, a table can have only one primary key. UNIQUE constraint supports NULL values and rows with NULL value in any column are considered as unique. UNIQUE constraint allows NULL values, if nulls distinct clause is not specified, the default is NULLS DISTINCT, excluding some compatibility modes. UNIQUE (VALUE) creates a unique constraint on entire row, excluding invisible columns; but if new columns will be added to the table, they will not be included into this constraint.
Referential constraint requires values that exist in other column(s) (usually in another table).
Check constraint requires a specified condition to return TRUE or UNKNOWN (NULL). It can reference columns of the table, and can reference objects that exist while the statement is executed. Conditions are only checked when a row is added or modified in the table where the constraint exists.
Example:
PRIMARY KEY(ID, NAME)
Constraint Name Definition
CONSTRAINT |
| newConstraintName |
Defines a constraint name.
Example:
CONSTRAINT CONST_ID
Csv Options
Optional parameters for CSVREAD and CSVWRITE. Instead of setting the options one by one, all options can be combined into a space separated key-value pairs, as follows: STRINGDECODE('charset=UTF-8 escape=\" fieldDelimiter=\" fieldSeparator=, ' || 'lineComment=# lineSeparator=\n null= rowSeparator='). The following options are supported:
caseSensitiveColumnNames (true or false; disabled by default),
charset (for example 'UTF-8'),
escape (the character that escapes the field delimiter),
fieldDelimiter (a double quote by default),
fieldSeparator (a comma by default),
lineComment (disabled by default),
lineSeparator (the line separator used for writing; ignored for reading),
null Support reading existing CSV files that contain explicit null delimiters. Note that an empty, unquoted values are also treated as null.
quotedNulls (quotes the nullString. true of false; disabled by default),
preserveWhitespace (true or false; disabled by default),
writeColumnHeader (true or false; enabled by default).
For a newline or other special character, use STRINGDECODE as in the example above. A space needs to be escaped with a backslash ('\ '), and a backslash needs to be escaped with another backslash ('\\'). All other characters are not to be escaped, that means newline and tab characters are written as such.
Example:
CALL CSVWRITE('test2.csv', 'SELECT * FROM TEST', 'charset=UTF-8 fieldSeparator=|');
Data Change Delta Table
Executes the inner data change command and returns old, new, or final rows.
OLD is not allowed for INSERT command. It returns old rows.
NEW and FINAL are not allowed for DELETE command.
NEW returns new rows after evaluation of default expressions, but before execution of triggers.
FINAL returns new rows after execution of triggers.
Example:
SELECT ID FROM FINAL TABLE (INSERT INTO TEST (A, B) VALUES (1, 2))
Data Type or Domain
A data type or domain name.
Example:
INTEGER
MY_DOMAIN
Data Type
A data type.
Example:
INTEGER
Predefined Type
A predefined data type.
Example:
INTEGER
Digit
0-9
A digit.
Example:
0
Expression
andCondition |
|
Value or condition.
Example:
ID=1 OR NAME='Hi'
Factor
A value or a numeric factor.
Example:
ID * 10
Grouping element
expression | ||||||||||||
| ||||||||||||
|
A grouping element of GROUP BY clause.
Example:
A
(B, C)
()
Hex
The hexadecimal representation of a number or of bytes with optional space characters. Two hexadecimal digit characters are one byte.
Example:
cafe
11 22 33
a b c d
Index Column
columnName |
|
|
Indexes this column in ascending or descending order. Usually it is not required to specify the order; however doing so will speed up large queries that order the column in the same way.
Example:
NAME
Insert values
VALUES |
| , |
|
Values for INSERT statement.
Example:
VALUES (1, 'Test')
Interval qualifier
| ||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||
|
An interval qualifier.
Example:
DAY TO SECOND
Join specification
| |||||||||||
|
Specifies a join condition or column names.
Example:
ON B.ID = A.PARENT_ID
USING (ID)
Merge when clause
WHEN MATCHED or WHEN NOT MATCHED clause for MERGE USING command.
Example:
WHEN MATCHED THEN DELETE
Merge when matched clause
WHEN MATCHED clause for MERGE USING command.
Updates or deletes rows in a target table.
Example:
WHEN MATCHED THEN UPDATE SET NAME = S.NAME
WHEN MATCHED THEN DELETE
Merge when not matched clause
WHEN NOT MATCHED |
| THEN INSERT |
|
overrideClause |
VALUES ( |
|
| ) |
WHEN NOT MATCHED clause for MERGE USING command.
Inserts rows into a target table.
If column names aren't specified a list of all visible columns in the target table is assumed.
Example:
WHEN NOT MATCHED THEN INSERT (ID, NAME) VALUES (S.ID, S.NAME)
Name
| ||||||||||||||||||||||||
quotedName |
With default settings unquoted names are converted to upper case. The maximum name length is 256 characters.
Identifiers in H2 are case sensitive by default. Because unquoted names are converted to upper case, they can be written in any case anyway. When both quoted and unquoted names are used for the same identifier the quoted names must be written in upper case. Identifiers with lowercase characters can be written only as a quoted name, they aren't accessible with unquoted names.
If DATABASE_TO_UPPER setting is set to FALSE the unquoted names aren't converted to upper case.
If DATABASE_TO_LOWER setting is set to TRUE the unquoted names are converted to lower case instead.
If CASE_INSENSITIVE_IDENTIFIERS setting is set to TRUE all identifiers are case insensitive.
Example:
TEST
Operand
Performs the concatenation of character string, binary string, or array values. In the default mode, the result is NULL if either parameter is NULL. In compatibility modes result of string concatenation with NULL parameter can be different.
Example:
'Hi' || ' Eva'
X'AB' || X'CD'
ARRAY[1, 2] || 3
1 || ARRAY[2, 3]
ARRAY[1, 2] || ARRAY[3, 4]
Override clause
OVERRIDING |
| VALUE |
If OVERRIDING USER VALUE is specified, INSERT statement ignores the provided value for identity column and generates a new one instead.
If OVERRIDING SYSTEM VALUE is specified, INSERT statement assigns the provided value to identity column.
If neither clauses are specified, INSERT statement assigns the provided value to GENERATED BY DEFAULT AS IDENTITY column, but throws an exception if value is specified for GENERATED ALWAYS AS IDENTITY column.
Example:
OVERRIDING SYSTEM VALUE
OVERRIDING USER VALUE
Query
A query, such as SELECT, explicit table, or table value.
Example:
SELECT ID FROM TEST;
TABLE TEST;
VALUES (1, 2), (3, 4);
Quoted Name
| |||||||||||
|
Case of characters in quoted names is preserved as is. Such names can contain spaces. The maximum name length is 256 characters. Two double quotes can be used to create a single double quote inside an identifier. With default settings identifiers in H2 are case sensitive.
Identifiers staring with U& are Unicode identifiers. All identifiers in H2 may have Unicode characters, but Unicode identifiers may contain Unicode escape sequences \0000 or \+000000, where \ is an escape character, 0000 and 000000 are Unicode character codes in hexadecimal notation. Optional UESCAPE clause may be used to specify another escape character, with exception for single quote, double quote, plus sign, and hexadecimal digits (0-9, a-f, and A-F). By default the backslash is used. Two escape characters can be used to include a single character inside an Unicode identifier. Two double quotes can be used to create a single double quote inside an Unicode identifier.
Example:
"FirstName"
U&"\00d6ffnungszeit"
U&"/00d6ffnungszeit" UESCAPE '/'
Referential Constraint
FOREIGN KEY ( columnName |
| ) referencesSpecification |
Defines a referential constraint.
Example:
FOREIGN KEY(ID) REFERENCES TEST(ID)
References Specification
REFERENCES |
|
|
|
|
Defines a referential specification of a referential constraint. If the table name is not specified, then the same table is referenced. RESTRICT is the default action. If the referenced columns are not specified, then the primary key columns are used. Referential constraint requires an existing unique or primary key constraint on referenced columns, this constraint must include all referenced columns in any order and must not include any other columns. Some tables may not be referenced, such as metadata tables.
Example:
REFERENCES TEST(ID)
Referential Action
CASCADE | ||||||||||
RESTRICT | ||||||||||
| ||||||||||
|
The action CASCADE will cause conflicting rows in the referencing (child) table to be deleted or updated. RESTRICT is the default action. As this database does not support deferred checking, RESTRICT and NO ACTION will both throw an exception if the constraint is violated. The action SET DEFAULT will set the column in the referencing (child) table to the default value, while SET NULL will set it to NULL.
Example:
CASCADE
SET NULL
Script Compression Encryption
The compression and encryption algorithm to use for script files. When using encryption, only DEFLATE and LZF are supported. LZF is faster but uses more space.
Example:
COMPRESSION LZF
Select order
|
|
|
Sorts the result by the given column number, or by an expression. If the expression is a single parameter, then the value is interpreted as a column number. Negative column numbers reverse the sort order.
Example:
NAME DESC NULLS LAST
Row value expression
| |||||||||||||||||||
| |||||||||||||||||||
expression |
A row value expression.
Example:
ROW (1)
(1, 2)
1
Select Expression
An expression in a SELECT statement.
Example:
ID AS DOCUMENT_ID
Sequence value expression
| VALUE FOR |
| sequenceName |
The next or current value of a sequence.
When the next value is requested the sequence is incremented and the current value of the sequence and the last identity in the current session are updated with the generated value. The next value of the sequence is generated only once for each processed row. If this expression is used multiple times with the same sequence it returns the same value within a processed row. Used values are never re-used, even when the transaction is rolled back.
Current value may only be requested after generation of the sequence value in the current session. It returns the latest generated value for the current session.
If a single command contains next and current value expressions for the same sequence there is no guarantee that the next value expression will be evaluated before the evaluation of current value expression.
Example:
NEXT VALUE FOR SEQ1
CURRENT VALUE FOR SCHEMA2.SEQ2
Sequence option
| |||
| |||
basicSequenceOption |
Option of a sequence.
START WITH is used to set the initial value of the sequence. If initial value is not defined, MINVALUE for incrementing sequences and MAXVALUE for decrementing sequences is used.
RESTART is used to immediately restart the sequence with the specified value.
Example:
START WITH 10000
NO CACHE
Alter sequence option
| |||||||||||
| |||||||||||
basicSequenceOption |
Option of a sequence.
START WITH is used to change the initial value of the sequence. It does not affect the current value of the sequence, it only changes the preserved initial value that is used for simple RESTART without a value.
RESTART is used to restart the sequence from its initial value or with the specified value.
Example:
START WITH 10000
NO CACHE
Alter identity column option
| |||||||||||
| |||||||||||
|
Option of an identity column.
START WITH is used to set or change the initial value of the sequence. START WITH does not affect the current value of the sequence, it only changes the preserved initial value that is used for simple RESTART without a value.
RESTART is used to restart the sequence from its initial value or with the specified value.
Example:
START WITH 10000
SET NO CACHE
Basic sequence option
| |||
| |||
| |||
NOMINVALUE | |||
| |||
| |||
NOMAXVALUE | |||
CYCLE | |||
| |||
EXHAUSTED | |||
NOCYCLE | |||
| |||
| |||
NOCACHE |
Basic option of a sequence.
INCREMENT BY specifies the step of the sequence, may be positive or negative, but may not be zero. The default is 1.
MINVALUE and MAXVALUE specify the bounds of the sequence.
Sequences with CYCLE option start the generation again from MINVALUE (incrementing sequences) or MAXVALUE (decrementing sequences) instead of exhausting with an error. Sequences with EXHAUSTED option can't return values until they will be restarted.
The CACHE option sets the number of pre-allocated numbers. If the system crashes without closing the database, at most this many numbers are lost. The default cache size is 32 if sequence has enough range of values. NO CACHE option or the cache size 1 or lower disable the cache. If CACHE option is specified, it cannot be larger than the total number of values that sequence can produce within a cycle.
Example:
MAXVALUE 100000
CYCLE
NO CACHE
Set clause list
|
|
List of SET clauses.
Each column may be specified only once in update targets.
Example:
NAME = 'Test', PRICE = 2
(A, B) = (1, 2)
(A, B) = (1, 2), C = 3
(A, B) = (SELECT X, Y FROM OTHER T2 WHERE T1.ID = T2.ID)
Sort specification
expression |
|
|
Sorts the result by an expression.
Example:
X ASC NULLS FIRST
Sort specification list
sortSpecification |
|
Sorts the result by expressions.
Example:
V
A, B DESC NULLS FIRST
Summand
A value or a numeric sum.
Please note the text concatenation operator is ||.
Example:
ID + 20
Table Expression
| |||||||||||
| |||||||||||
unnest | |||||||||||
table | |||||||||||
dataChangeDeltaTable |
|
|
|
Joins a table. The join specification is not supported for cross and natural joins. A natural join is an inner join, where the condition is automatically on the columns with the same name.
Example:
TEST1 AS T1 LEFT JOIN TEST2 AS T2 ON T1.ID = T2.PARENT_ID
Update target
columnName |
|
Column or element of a column of ARRAY data type.
If array indexes are specified, column must have a compatible ARRAY data type and updated rows may not have NULL values in this column. It means for C[2][3] both C and C[2] may not be NULL. Too short arrays are expanded, missing elements are set to NULL.
Example:
A
B[1]
C[2][3]
Within group specification
WITHIN GROUP ( ORDER BY sortSpecificationList ) |
Group specification for ordered set functions.
Example:
WITHIN GROUP (ORDER BY ID DESC)
Wildcard expression
A wildcard expression in a SELECT statement. A wildcard expression represents all visible columns. Some columns can be excluded with optional EXCEPT clause.
Example:
*
* EXCEPT (DATA)
Window name or specification
A window name or inline specification for a window function or aggregate.
Window functions in H2 may require a lot of memory for large queries.
Example:
W1
(ORDER BY ID)
Window specification
A window specification for a window, window function or aggregate.
If name of an existing window is specified its clauses are used by default.
Optional window partition clause separates rows into independent partitions. Each partition is processed separately. If this clause is not present there is one implicit partition with all rows.
Optional window order clause specifies order of rows in the partition. If some rows have the same order position they are considered as a group of rows in optional window frame clause.
Optional window frame clause specifies which rows are processed by a window function, see its documentation for a more details.
Example:
()
(W1 ORDER BY ID)
(PARTITION BY CATEGORY)
(PARTITION BY CATEGORY ORDER BY NAME, ID)
(ORDER BY Y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES)
Window frame
ROWS | ||
RANGE | ||
GROUP |
windowFramePreceding | |||
|
|
A window frame clause. May be specified only for aggregates and FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() window functions.
If this clause is not specified for an aggregate or window function that supports this clause the default window frame depends on window order clause. If window order clause is also not specified the default window frame contains all the rows in the partition. If window order clause is specified the default window frame contains all preceding rows and all rows from the current group.
Window frame unit determines how rows or groups of rows are selected and counted. If ROWS is specified rows are not grouped in any way and relative numbers of rows are used in bounds. If RANGE is specified rows are grouped according window order clause, preceding and following values mean the difference between value in the current row and in the target rows, and CURRENT ROW in bound specification means current group of rows. If GROUPS is specified rows are grouped according window order clause, preceding and following values means relative number of groups of rows, and CURRENT ROW in bound specification means current group of rows.
If only window frame preceding clause is specified it is treated as BETWEEN windowFramePreceding AND CURRENT ROW.
Optional window frame exclusion clause specifies rows that should be excluded from the frame. EXCLUDE CURRENT ROW excludes only the current row regardless the window frame unit. EXCLUDE GROUP excludes the whole current group of rows, including the current row. EXCLUDE TIES excludes the current group of rows, but not the current row. EXCLUDE NO OTHERS is default and it does not exclude anything.
Example:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES
Window frame preceding
| |||
| |||
|
A window frame preceding clause. If value is specified it should not be negative.
Example:
UNBOUNDED PRECEDING
1 PRECEDING
CURRENT ROW
Window frame bound
A window frame bound clause. If value is specified it should not be negative.
Example:
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
1 FOLLOWING
CURRENT ROW
Term
value | ||||||||||
column | ||||||||||
| ||||||||||
sequenceValueExpression | ||||||||||
Function | ||||||||||
| ||||||||||
| ||||||||||
arrayElementReference | ||||||||||
fieldReference | ||||||||||
| ||||||||||
caseExpression | ||||||||||
castSpecification | ||||||||||
userDefinedFunctionName |
timeZone | ||
intervalQualifier |
A value. Parameters can be indexed, for example ?1 meaning the first parameter.
Interval qualifier may only be specified for a compatible value or for a subtraction operation between two datetime values. The subtraction operation ignores the leading field precision of the qualifier.
Example:
'Hello'
Time zone
AT |
|
A time zone. Converts the timestamp with or without time zone into timestamp with time zone at specified time zone. If a day-time interval is specified as a time zone, it may not have fractional seconds and must be between -18 to 18 hours inclusive.
Example:
AT LOCAL
AT TIME ZONE '2'
AT TIME ZONE '-6:00'
AT TIME ZONE INTERVAL '10:00' HOUR TO MINUTE
AT TIME ZONE INTERVAL '10:00:00' HOUR TO SECOND
AT TIME ZONE 'UTC'
AT TIME ZONE 'Europe/London'
Column
|
|
A column name with optional table alias and schema. _ROWID_ can be used to access unique row identifier.
Example:
ID