This is the preferred way of running a database server and the fastest one. A proprietary communications protocol is used for this mode. A command similar to those used for running tools and described above is used for running the server. The following example of the command for starting the server starts the server with one (default) database with files named "mydb.*" and the public name of "xdb". The public name hides the file names from users.

 java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:mydb --dbname.0 xdb

The command line argument --help can be used to get a list of available arguments. Connections are made using an hsql: URL.

 Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb", "SA", "");

This method of access is used when the computer hosting the database server is restricted to the HTTP protocol. The only reason for using this method of access is restrictions imposed by firewalls on the client or server machines and it should not be used where there are no such restrictions. The HyperSQL HTTP Server is a special web server that allows JDBC clients to connect via HTTP. The server can also act as a small general-purpose web server for static pages.

To run an HTTP server, replace the main class for the server in the example command line above with WebServer:

 java -cp ../lib/hsqldb.jar org.hsqldb.server.WebServer --database.0 file:mydb --dbname.0 xdb

The command line argument --help can be used to get a list of available arguments. Connections are made using an http: URL.

 Connection c = DriverManager.getConnection("jdbc:hsqldb:http://localhost/xdb", "SA", "");

This method of access also uses the HTTP protocol. It is used when a servlet engine (or application server) such as Tomcat or Resin provides access to the database. The Servlet Mode cannot be started independently from the servlet engine. The Servlet class, in the HSQLDB jar, should be installed on the application server to provide the connection. The database file path is specified using an application server property. Refer to the source file src/org/hsqldb/server/Servlet.java to see the details.

Both HTTP Server and Servlet modes can be accessed using the JDBC driver at the client end. They do not provide a web front end to the database. The Servlet mode can serve multiple databases.

Please note that you do not normally use this mode if you are using the database engine in an application server. In this situation, connections to a catalog are usually made in-process, or using the hsql: protocol to an HSQL Server

When a HyperSQL server is running, client programs can connect to it using the HSQLDB JDBC Driver contained in hsqldb.jar. Full information on how to connect to a server is provided in the Java Documentation for JDBCConnection (located in the /doc/apidocs directory of HSQLDB distribution). A common example is connection to the default port (9001) used for the hsql: protocol on the same machine:

 try {
     Class.forName("org.hsqldb.jdbc.JDBCDriver" );
 } catch (Exception e) {
     System.err.println("ERROR: failed to load HSQLDB JDBC driver.");
     e.printStackTrace();
     return;
 }

 Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb", "SA", "");

If the HyperSQL HTTP server is used, the protocol is http: and the URL will be different:

 Connection c = DriverManager.getConnection("jdbc:hsqldb:http://localhost/xdb", "SA", "");

Note in the above connection URL, there is no mention of the database file, as this was specified when running the server. Instead, the public name defined for dbname.0 is used. Also, see the HyperSQL Network Listeners (Servers) chapter for the connection URL when there is more than one database per server instance.

When a HyperSQL server is run, network access should be adequately protected. Source IP addresses may be restricted by use of our Access Control List feature, network filtering software, firewall software, or standalone firewalls. Only secure passwords should be used-- most importantly, the password for the default system user should be changed from the default empty string. If you are purposefully providing data to the public, then the wide-open public network connection should be used exclusively to access the public data via read-only accounts. (i.e., neither secure data nor privileged accounts should use this connection). These considerations also apply to HyperSQL servers run with the HTTP protocol.

HyperSQL provides two optional security mechanisms. The encrypted SSL protocol, and Access Control Lists. Both mechanisms can be specified when running the Server or WebServer. On the client, the URL to connect to an SSL server is slightly different:

 Connection c = DriverManager.getConnection("jdbc:hsqldb:hsqls://localhost/xdb", "SA", "");
 Connection c = DriverManager.getConnection("jdbc:hsqldb:https://localhost/xdb", "SA", "");

The security features are discussed in detail in the HyperSQL Network Listeners (Servers) chapter.

A server can provide connections to more than one database. In the examples above, more than one set of database names can be specified on the command line. It is also possible to specify all the databases in a .properties file, instead of the command line. These capabilities are covered in the HyperSQL Network Listeners (Servers) chapter

Definition statements create, modify, or remove database objects. Tables and views are objects that contain data. There are other types of objects that do not contain data. These statements are covered in the Schemas and Database Objects chapter.

CREATE

Followed by { SCHEMA | TABLE | VIEW | SEQUENCE | PROCEDURE | FUNCTION | USER | ROLE | ... }, the keyword is used to create the database objects.

ALTER

Followed by the same keywords as CREATE, the keyword is used to modify the object.

DROP

Followed by the same keywords as above, the keyword is used to remove the object. If the object contains data, the data is removed too.

GRANT

Followed by the name of a role or privilege, the keyword assigns a role or gives permissions to a USER or role.

REVOKE

Followed by the name of a role or privilege, REVOKE is the opposite of GRANT.

COMMENT ON

Followed by the same keywords as CREATE, the keyword is used to add a text comment to TABLE, VIEW, COLUMN, ROUTINE, and TRIGGER objects.

EXPLAIN REFERENCES

These keywords are followed by TO or FROM to list the other database objects that reference the given object, or vice versa.

DECLARE

This is used for declaring temporary session tables and variables.

Data manipulation statements add, update, or delete data in tables and views. These statements are covered in the Data Access and Change chapter.

INSERT

Inserts one or more rows into a table or view.

UPDATE

Updates one or more rows in a table or view.

DELETE

Deletes one or more rows from a table or view.

TRUNCATE

Deletes all the rows in a table.

MERGE

Performs a conditional INSERT, UPDATE or DELETE on a table or view using the data given in the statement.

Data query statements retrieve and combine data from tables and views and return result sets. These statements are covered in the Data Access and Change chapter.

SELECT

Returns a result set formed from a subset of rows and columns in one or more tables or views.

VALUES

Returns a result set formed from constant values.

WITH ...

This keyword starts a series of SELECT statements that form a query. The first SELECTs act as subqueries for the final SELECT statement in the same query.

EXPLAIN PLAN

These keywords are followed by the full text of any DQL or DML statement. The result set shows the anatomy of the given DQL or DML statement, including the indexes used to access the tables.

CALL

Calls a procedure or function. Calling a function can return a result set or a value, while calling a procedure can return one or more result sets and values at the same time. This statement is covered in the SQL-Invoked Routines chapter.

SET

The SET statement has many variations and is used for setting the values of the general properties of the database or the current session. Usage of the SET statement for the database is covered in the System Management chapter. Usage for the session is covered in the Sessions and Transactions chapter.

General operations on the database include backup, checkpoint, and other operations. These statements are covered in detail in the System Management chapter.

BACKUP

Creates a backup of the database in a target directory.

PERFORM

Includes commands to export and import SQL scripts from / to the database. Also includes a command to check the consistency of the indexes.

SCRIPT

Creates a script of SQL statements that creates the database objects and settings.

CHECKPOINT

Saves all the changes to the database up to this point to disk files.

SHUTDOWN

Shuts down the database after saving all the changes.

These statements are used in a session to start, end or control transactions. They are covered in the Sessions and Transactions chapter.

START TRANSACTION

This statement initiates a new transaction with the given transaction characteristics

SET TRANSACTION

Introduces one of more characteristics for the next transaction.

COMMIT

Commits the changes to data made in the current transaction.

ROLLBACK

Rolls back the changes to data made in the current transaction. It is also possible to roll back to a savepoint.

SAVEPOINT

Records a point in the current transaction so that future changes can be rolled back to this point.

RELEASE SAVEPOINT

Releases an existing savepoint.

LOCK

Locks a set of tables for transaction control.

CONNECT

Starts a new session and continues operations in this session.

DISCONNECT

Ends the current session.

Any SQL statement can include comments. The comments are stripped before the statement is executed.

SQL style line comments start with two dashes -- and extend to the end of the line.

C style comments can cover part of the line or multiple lines. They start with /* and end with */.

The body of user-defined SQL procedures and functions (collectively called routines) may contain several other types of statements and keywords in addition to DML and DQL statements. These include: BEGIN and END for blocks; FOR, WHILE and REPEAT loops; IF, ELSE and ELSEIF blocks; SIGNAL and RESIGNAL statements for handling exceptions.

These statements are covered in detail in the SQL-Invoked Routines chapter.

Standard SQL is not case sensitive, except when names of objects are enclosed in double-quotes. SQL keywords can be written in any case; for example, sElect, SELECT and select are all allowed and converted to uppercase. Identifiers, such as names of tables, columns and other objects defined by the user, are also converted to uppercase. For example, myTable, MyTable and MYTABLE all refer to the same table and are stored in the database in the case-normal form, which is all uppercase for unquoted identifiers. When the name of an object is enclosed in double quotes when it is created, the exact name is used as the case-normal form and it must be referenced with the exact same double-quoted string. For example, "myTable" and "MYTABLE" are different tables. When the double-quoted name is all-uppercase, it can be referenced in any case; "MYTABLE" is the same as myTable and MyTable because they are all converted to MYTABLE.

HyperSQL supports the Standard definition of persistent base table, but defines three types according to the way the data is stored. These are MEMORY tables, CACHED tables, and TEXT tables.

Memory tables are the default type when the CREATE TABLE command is used. Their data is held entirely in memory. In file-based databases, MEMORY tables are persistent and any change to their structure or contents is written to the *.log and *.script files. The *.script file and the *.log file are read the next time the database is opened, and the MEMORY tables are recreated with all the data. This process may take a long time if the database is larger than tens of megabytes. When the database is shutdown, all the data is saved.

CACHED tables are created with the CREATE CACHED TABLE command. Only part of their data or indexes is held in memory, allowing large tables that would otherwise take up to several hundred megabytes of memory. Another advantage of cached tables is that the database engine takes less time to start up when a cached table is used for large amounts of data. The disadvantage of cached tables is a reduction in speed. Do not use cached tables if your data set is relatively small. In an application with some small tables and some large ones, it is better to use the default, MEMORY mode for the small tables.

TEXT tables use a CSV (Comma Separated Value) or other delimited text file as the source of their data. You can specify an existing CSV file, such as a dump from another database or program, as the source of a TEXT table. Alternatively, you can specify an empty file to be filled with data by the database engine. TEXT tables are efficient in memory usage as they cache only part of the text data and all of the indexes. The Text table data source can always be reassigned to a different file if necessary. The commands are needed to set up a TEXT table as detailed in the Text Tables chapter.

With all-in-memory mem: databases, both MEMORY table and CACHED table declarations are treated as declarations for MEMORY tables which last only for the duration of the Java process. In the latest versions of HyperSQL, TEXT table declarations are allowed in all-in-memory databases.

The default type of tables resulting from future CREATE TABLE statements can be specified with the SQL command:

 SET DATABASE DEFAULT TABLE TYPE { CACHED | MEMORY };

The type of an existing table can be changed with the SQL command:

 SET TABLE <table name> TYPE { CACHED | MEMORY };

SQL statements such as INSERT or SELECT access different types of tables uniformly. No change to statements is needed to access different types of table.

Data in TEMPORARY tables is not saved and lasts only for the lifetime of the session. The contents of each TEMP table are visible only from the session that is used to populate it.

HyperSQL supports two types of temporary tables.

The GLOBAL TEMPORARY type is a schema object. It is created with the CREATE GLOBAL TEMPORARY TABLE statement. The definition of the table persists, and each session has access to the table. But each session sees its own copy of the table, which is empty at the beginning of the session.

The LOCAL TEMPORARY type is not a schema object. It is created with the DECLARE LOCAL TEMPORARY TABLE statement. The table definition lasts only for the duration of the session and is not persisted in the database. The table can be declared in the middle of a transaction without committing the transaction. If a schema name is needed to reference these tables in a given SQL statement, the pseudo schema name SESSION can be used.

When the session commits, the contents of all temporary tables are cleared by default. If the table definition statement includes ON COMMIT PRESERVE ROWS, then the contents are kept when a commit takes place.

The rows in temporary tables are stored in memory by default. If the hsqldb.result_max_memory_rows property has been set or the SET SESSION RESULT MEMORY ROWS <row count> has been specified, tables with row count above the setting are stored on disk.

TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC and DECIMAL (without a decimal point) are the supported integral types. They correspond respectively to byte, short, int, long, BigDecimal and BigDecimal Java types in the range of values that they can represent (NUMERIC and DECIMAL are equivalent). The type TINYINT is an HSQLDB extension to the SQL Standard, while the others conform to the Standard definition. The SQL type dictates the maximum and minimum values that can be held in a field of each type. For example the value range for TINYINT is -128 to +127. The bit precision of TINYINT, SMALLINT, INTEGER and BIGINT is respectively 8, 16, 32 and 64. For NUMERIC and DECIMAL, decimal precision is used.

DECIMAL and NUMERIC with decimal fractions are mapped to java.math.BigDecimal and can have very large numbers of digits. In HyperSQL the two types are equivalent. These types, together with integral types, are called exact numeric types.

In HyperSQL, REAL, FLOAT and DOUBLE are equivalent: they are all mapped to double in Java. These types are defined by the SQL Standard as approximate numeric types. The bit-precision of all these types is 64 bits.

The decimal precision and scale of NUMERIC and DECIMAL types can be optionally defined. For example, DECIMAL(10,2) means maximum total number of digits is 10 and there are always 2 digits after the decimal point, while DECIMAL(10) means 10 digits without a decimal point. The bit-precision of FLOAT can be defined but it is ignored and the default bit-precision of 64 is used. The default precision of NUMERIC and DECIMAL (when not defined) is 128.

Note: If a database has been set to ignore type precision limits with the SET DATABASE SQL SIZE FALSE command, then a type definition of DECIMAL with no precision and scale is treated as DECIMAL(128,32). In normal operation, it is treated as DECIMAL(128).

Integral Types

In expressions, values of TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC and DECIMAL (without a decimal point) types can be freely combined and no data narrowing takes place. The resulting value is of a type that can support all possible values.

If the SELECT statement refers to a simple column or function, then the return type is the type corresponding to the column or the return type of the function. For example:

 CREATE TABLE t(a INTEGER, b BIGINT);
 SELECT MAX(a), MAX(b) FROM t;

will return a ResultSet where the type of the first column is java.lang.Integer and the second column is java.lang.Long. However,

 SELECT MAX(a) + 1, MAX(b) + 1 FROM t;

will return java.lang.Long and BigDecimal values, generated as a result of uniform type promotion for all possible return values. Note that type promotion to BigDecimal ensures the correct value is returned if MAX(b) evaluates to Long.MAX_VALUE.

There is no built-in limit on the size of intermediate integral values in expressions. As a result, you should check for the type of the ResultSet column and choose an appropriate getXXXX() method to retrieve it. Alternatively, you can use the getObject() method, then cast the result to java.lang.Number and use the intValue() or longValue() if the value is not an instance of java.math.BigDecimal.

When the result of an expression is stored in a column of a database table, it has to fit in the target column, otherwise an error is returned. For example, when 1234567890123456789012 / 12345687901234567890 is evaluated, the result can be stored in any integral type column, even a TINYINT column, as it is a small value.

In SQL Statements, an integer literal is treated as INTEGER, unless its value does not fit. In this case it is treated as BIGINT or DECIMAL, depending on the value.

Depending on the types of the operands, the result of the operation is returned in a JDBC ResultSet in any of the related Java types: Integer, Long or BigDecimal. The ResultSet.getXXXX() methods can be used to retrieve the values so long as the returned value can be represented by the resulting type. This type is deterministically based on the query, not on the actual rows returned.

Other Numeric Types

In SQL statements, number literals with a decimal point are treated as DECIMAL unless they are written with an exponent. Thus 0.2 is considered a DECIMAL value but 0.2E0 is considered a DOUBLE value.

When an approximate numeric type, REAL, FLOAT or DOUBLE (all synonymous) is part of an expression involving different numeric types, the type of the result is DOUBLE. DECIMAL values can be converted to DOUBLE unless they are beyond the Double.MIN_VALUE - Double.MAX_VALUE range. For example, A * B, A / B, A + B, etc., will return a DOUBLE value if either A or B is a DOUBLE.

Otherwise, when no DOUBLE value exists, if a DECIMAL or NUMERIC value is part an expression, the type of the result is DECIMAL or NUMERIC. Similar to integral values, when the result of an expression is assigned to a table column, the value has to fit in the target column, otherwise an error is returned. This means a small, 4 digit value of DECIMAL type can be assigned to a column of SMALLINT or INTEGER, but a value with 15 digits cannot.

When a DECIMAL value is multiplied by a DECIMAL or integral type, the resulting scale is the sum of the scales of the two terms. When they are divided, the result is a value with a scale (number of digits to the right of the decimal point) equal to the larger of the scales of the two terms. The precision for both operations is calculated (usually increased) to allow all possible results.

The distinction between DOUBLE and DECIMAL is important when a division takes place. For example, 10.0/8.0 (DECIMAL) equals 1.2 but 10.0E0/8.0E0 (DOUBLE) equals 1.25. Without division operations, DECIMAL values represent exact arithmetic.

REAL, FLOAT and DOUBLE values are all stored in the database as java.lang.Double objects. Special values such as NaN and +-Infinity are also stored and supported. These values can be submitted to the database via JDBC PreparedStatement methods and are returned in ResultSet objects. In order to allow division by zero of DOUBLE values in SQL statements (which returns NaN or +-Infinity) you should set the property hsqldb.double_nan as false (SET DATABASE SQL DOUBLE NAN FALSE). The double values can be retrieved from a ResultSet in the required type so long as they can be represented. For setting the values, when PreparedStatement.setDouble() or setFloat() is used, the value is treated as a DOUBLE automatically.

In short,

<numeric type> ::= <exact numeric type> | <approximate numeric type>

<exact numeric type> ::= NUMERIC [ <left paren> <precision> [ <comma> <scale> ] <right paren> ] | { DECIMAL | DEC } [ <left paren> <precision> [ <comma> <scale> ] <right paren> ] | TINYINT | SMALLINT | INTEGER | INT | BIGINT

<approximate numeric type> ::= FLOAT [ <left paren> <precision> <right paren> ] | REAL | DOUBLE PRECISION

<precision> ::= <unsigned integer>

<scale> ::= <unsigned integer>

The BOOLEAN type conforms to the SQL Standard and represents the values TRUE, FALSE and UNKNOWN. This type of column can be initialised with Java boolean values, or with NULL for the UNKNOWN value.

The three-value logic is sometimes misunderstood. For example, x IN (1, 2, NULL) does not return true if x is NULL.

In previous versions of HyperSQL, BIT was simply an alias for BOOLEAN. In version 2, BIT is a single-bit bit map.

<boolean type> ::= BOOLEAN

The SQL Standard does not support type conversion to BOOLEAN apart from character strings that consists of boolean literals. Because the BOOLEAN type is relatively new to the Standard, several database products used other types to represent boolean values. For improved compatibility, HyperSQL allows some type conversions to boolean.

Values of BIT and BIT VARYING types with length 1 can be converted to BOOLEAN. If the bit is set, the result of conversion is the TRUE value, otherwise it is FALSE.

Values of TINYINT, SMALLINT, INTEGER and BIGINT types can be converted to BOOLEAN. If the value is zero, the result is the FALSE value, otherwise it is TRUE.

The CHARACTER, CHARACTER VARYING and CLOB types are the SQL Standard character string types. CHAR, VARCHAR and CHARACTER LARGE OBJECT are synonyms for these types. HyperSQL also supports LONGVARCHAR as a synonym for VARCHAR. If LONGVARCHAR is used without a length, then a length of 16M is assigned. You can set LONGVARCHAR to map to CLOB, with the sql.longvar_is_lob connection property or the SET DATABASE SQL LONGVAR IS LOB TRUE statement..

HyperSQL's default character set is Unicode, therefore all possible character strings can be represented by these types.

The SQL Standard behaviour of the CHARACTER type is a remnant of legacy systems in which character strings are padded with spaces to fill a fixed width. These spaces are sometimes significant while in other cases they are silently discarded. It would be best to avoid the CHARACTER type altogether. With the rest of the types, the strings are not padded when assigned to columns or variables of the given type. The trailing spaces are still considered discardable for all character types. Therefore, if a string with trailing spaces is too long to assign to a column or variable of a given length, the spaces beyond the type length are discarded and the assignment succeeds (provided all the characters beyond the type length are spaces).

The VARCHAR and CLOB types have length limits, but the strings are not padded by the system. Note that if you use a large length for a VARCHAR or CLOB type, no extra space is used in the database. The space used for each stored item is proportional to its actual length.

If CHARACTER is used without specifying the length, the length defaults to 1. For the CLOB type, the length limit can be defined in units of kilobyte (K, 1024), megabyte (M, 1024 * 1024) or gigabyte (G, 1024 * 1024 * 1024), using the <multiplier>. If CLOB is used without specifying the length, the length defaults to 1GB.

<character string type> ::= { CHARACTER | CHAR } [ <left paren> <character length> <right paren> ] | { CHARACTER VARYING | CHAR VARYING | VARCHAR } <left paren> <character length> <right paren> | LONGVARCHAR [ <left paren> <character length> <right paren> ] | <character large object type>

<character large object type> ::= { CHARACTER LARGE OBJECT | CHAR LARGE OBJECT | CLOB } [ <left paren> <character large object length> <right paren> ]

<character length> ::= <unsigned integer> [ <char length units> ]

<large object length> ::= <length> [ <multiplier> ] | <large object length token>

<character large object length> ::= <large object length> [ <char length units> ]

<large object length token> ::= <digit>... <multiplier>

<multiplier> ::= K | M | G

<char length units> ::= CHARACTERS | OCTETS

Each character type has a collation. This is either a default collation or stated explicitly with the COLLATE clause. Collations are discussed in the Schemas and Database Objects chapter.

 CHAR(10)
 CHARACTER(10)
 VARCHAR(2)
 CHAR VARYING(2)
 CLOB(1000)
 CLOB(30K)
 CHARACTER LARGE OBJECT(1M)
 LONGVARCHAR

The BINARY, BINARY VARYING and BLOB types are the SQL Standard binary string types. VARBINARY and BINARY LARGE OBJECT are synonyms for BINARY VARYING and BLOB types. HyperSQL also supports LONGVARBINARY as a synonym for VARBINARY. You can set LONGVARBINARY to map to BLOB, with the sql.longvar_is_lob connection property or the SET DATABASE SQL LONGVAR IS LOB TRUE statement.

Binary string types are used in a similar way to character string types. There are several built-in functions that are overloaded to support character, binary and bit strings.

The BINARY type represents a fixed width-string. Each shorter string is padded with zeros to fill the fixed width. Similar to the CHARACTER type, the trailing zeros in the BINARY string are simply discarded in some operations. For the same reason, it is best to avoid this particular type and use VARBINARY instead.

When two binary values are compared, if one is of BINARY type, then zero padding is performed to extend the length of the shorter string to the longer one before comparison. No padding is performed with other binary types. If the bytes compare equal to the end of the shorter value, then the longer string is considered larger than the shorter string.

If BINARY is used without specifying the length, the length defaults to 1. For the BLOB type, the length limit can be defined in units of kilobyte (K, 1024), megabyte (M, 1024 * 1024) or gigabyte (G, 1024 * 1024 * 1024), using the <multiplier>. If BLOB is used without specifying the length, the length defaults to 1GB.

The UUID type represents a UUID string. The type is similar to BINARY(16) but with the extra enforcement that disallows assigning, casting, or comparing with shorter or longer strings. Strings such as '24ff1824-01e8-4dac-8eb3-3fee32ad2b9c' or '24ff182401e84dac8eb33fee32ad2b9c' are allowed. When a value of the UUID type is converted to a CHARACTER type, the hyphens are inserted in the required positions. Java UUID objects can be used with java.sql.PreparedStatement to insert values of this type. The getObject() method of ResultSet returns the Java object for UUID column data.

<binary string type> ::= BINARY [ <left paren> <length> <right paren> ] | { BINARY VARYING | VARBINARY } <left paren> <length> <right paren> | LONGVARBINARY [ <left paren> <length> <right paren> ] | UUID | <binary large object string type>

<binary large object string type> ::= { BINARY LARGE OBJECT | BLOB } [ <left paren> <large object length> <right paren> ]

<length> ::= <unsigned integer>

 BINARY(10)
 VARBINARY(2)
 BINARY VARYING(2)
 BLOB(1000)
 BLOB(30G)
 BINARY LARGE OBJECT(1M)
 LONGVARBINARY

The BIT and BIT VARYING types are the supported bit string types. These types were defined by SQL:1999 but were later removed from the Standard. Bit types represent bit maps of given lengths. Each bit is 0 or 1. The BIT type represents a fixed width-string. Each shorter string is padded with zeros to fill the fixed with. If BIT is used without specifying the length, the length defaults to 1. The BIT VARYING type has a maximum width and shorter strings are not padded.

Before the introduction of the BOOLEAN type to the SQL Standard, a single-bit string of the type BIT(1) was commonly used. For compatibility with other products that do not conform to, or extend, the SQL Standard, HyperSQL allows values of BIT and BIT VARYING types with length 1 to be converted to and from the BOOLEAN type. BOOLEAN TRUE is considered equal to B'1', BOOLEAN FALSE is considered equal to B'0'.

For the same reason, numeric values can be assigned to columns and variables of the type BIT(1). For assignment, the numeric value zero is converted to B'0', while all other values are converted to B'1'. For comparison, numeric values 1 is considered equal to B'1' and numeric value zero is considered equal to B'0'.

It is not allowed to perform other arithmetic or boolean operations involving BIT(1) and BIT VARYING(1). The kid of operations allowed on bit strings are analogous to those allowed on BINARY and CHARACTER strings. Several built-in functions support all three types of string.

<bit string type> ::= BIT [ <left paren> <length> <right paren> ] | BIT VARYING <left paren> <length> <right paren>

 BIT
 BIT(10)
 BIT VARYING(2)

BLOB and CLOB are lob types. These types are used for very long strings that do not necessarily fit in memory. Small lobs that fit in memory can be accessed just like BINARY or VARCHAR column data. But lobs are usually much larger and therefore accessed with special JDBC methods.

To insert a lob into a table, or to update a column of lob type with a new lob, you can use the setBinaryStream() and setCharacterStream() methods of JDBC java.sql.PreparedStatement. These are very efficient methods for long lobs. Other methods are also supported. If the data for the BLOB or CLOB is already a memory object, you can use the setBytes() or setString() methods, which are efficient for memory data. Another method is to obtain a lob with the getBlob() and getClob() methods of java.sql.Connection, populate its data, then use the setBlob() or setClob() methods of PreparedStatement. Yet another method allows to create instances of org.hsqldb.jdbc.JDBCBlobFile and org.hsqldb.jdbc.JDBCClobFile and construct a large lob for use with setBlob() and setClob() methods.

A lob is retrieved from a ResultSet with the getBlob() or getClob() method. The steaming methods of the lob objects are then used to access the data. HyperSQL also allows efficient access to chunks of lobs with getBytes() or getString() methods. Furthermore, parts of a BLOB or CLOB already stored in a table can be modified. An updatable ResultSet is used to select the row from the table. The getBlob() or getClob() methods of ResultSet are used to access the lob as a java.sql.Blob or java.sql.Clob object. The setBytes() and setString() methods of these objects can be used to modify the lob. Finally the updateRow() method of the ResultSet is used to update the lob in the row. Note these modifications are not allowed with compressed or encrypted lobs.

Lobs are logically stored in columns of tables. Their physical storage is a separate *.lobs file. This file is created as soon as a BLOB or CLOB is inserted into the database. The file will grow as new lobs are inserted into the database. In version 2, the *.lobs file is never deleted even if all lobs are deleted from the database. In this case you can delete the *.lobs file after a SHUTDOWN. When a CHECKPOINT happens, the space used for deleted lobs is freed and is reused for future lobs. By default, clobs are stored without compression. You can use a database setting to enable compression of clobs. This can significantly reduce the storage size of clobs.

From version 2.3.4 there are two options for storing Java Objects.

The default option allows storing Serializable object. The objects remain serialized inside the database until they are retrieved. The application program that retrieves the object must include in its classpath the Java Class for the object, otherwise it cannot retrieve the object.

Any serializable Java Object can be inserted directly into a column of type OTHER using any variation of PreparedStatement.setObject() methods.

The alternative Live Object option is for mem: databases only and is enabled when the database property sql.live_object=true is appended to the connection property that creates the mem database. For example 'jdbc:hsqldb:mem:mydb;sql.live_object=true'. With this option, any Java object can be stored as it is not serialized. The SQL statement SET DATABASE SQL LIVE OBJECT TRUE can be also used. Note the SQL statement must be executed on the first connection to the database before any data is inserted. No data access should be made from this connection. Instead, new connections should be used for data access.

For comparison purposes and in indexes, any two Java Objects are considered equal unless one of them is NULL. You cannot search for a specific object or perform a join on a column of type OTHER.

Java Objects can simply be stored internally and no operations can be performed on them other than assignment between columns of type OTHER or checking for NULL. Tests such as WHERE object1 = object2 do not mean what you might expect, as any non-null object would satisfy such a tests. But WHERE object1 IS NOT NULL is perfectly acceptable.

The engine does not allow normal column values to be assigned to Java Object columns (for example, assigning an INTEGER or STRING to such a column with an SQL statement such as UPDATE mytable SET objectcol = intcol WHERE ...).

<java object type> ::= OTHER

The default method of storage is used when the objects and their state needs to be saved and retrieved in the future. This method is also used when memory resources are limited and collections of objects are stored and retrieved only when needed.

The Live Object option uses the database table as a collection of objects. This allows storing some attributes of the objects in the same table alongside the object itself and fast search and retrieval of objects on their attributes. For example, when many thousands of live objects contain details of films, the film title and the director can be stored in the table and searches can be performed for films on these attributes:

CREATE TABLE movies (director VARCHAR(30), title VARCHAR(40), obj OTHER)
SELECT obj FROM movies WHERE director LIKE 'Luc%'

In any case, at least one attribute of the object should be stored to allow efficient retrieval of the objects from both Live Object and Serialized storage. An ID number is often used as the stored column attribute.

In HyperSQL, column length, precision and scale qualifiers are required and are always enforced. The VARCHAR and VARBINARY types require a size parameter and do not have a default. For compatibility with CREATE TABLE statements from other databases that do not have size parameters for VARCHAR column, the URL property hsqldb.enforce_size=false or the SQL statement SET DATABASE SQL SIZE FALSE can be used to allow the table creation and automatically apply a large value for the maximum size of the VARCHAR column. You should test your application to ensure the length, precision and scale that is used for column definitions is appropriate for the application data.

All other types have defaults for size or precision parameters. However, the defaults may not be what your application requires and you may have to specify the parameters.

String types, including all BIT, BINARY and CHAR string types plus CLOB and BLOB, are generally defined with a length. If no length is specified for BIT, BINARY and CHAR, the default length is 1. For CLOB and BLOB an implementation defined length of 1G is used.

TIME and TIMESTAMP types can be defined with a fractional second precision between 0 and 9. INTERVAL type definition may have precision and, in some cases, fraction second precision. DECIMAL and NUMERIC types may be defined with precision and scale. For all of these types a default precision or scale value is used if one is not specified. The default scale is 0. The default fractional precision for TIME is 0, while it is 6 for TIMESTAMP.

Values can be converted from one type to another in two different ways: by using explicit CAST expression or by implicit conversion used in assignment, comparison, and aggregation.

String values cannot be assigned to VARCHAR columns if they are longer than the defined type length. For CHARACTER columns, a long string can be assigned (with truncation) only if all the characters after the length are spaces. Shorter strings are padded with the space character when inserted into a CHARACTER column. Similar rules are applied to VARBINARY and BINARY columns. For BINARY columns, the padding and truncation rules are applied with zero bytes, instead of spaces.

Explicit CAST of a value to a CHARACTER or VARCHAR type will result in forced truncation or padding. So a test such as CAST (mycol AS VARCHAR(2)) = 'xy' will find the values beginning with 'xy'. This is the equivalent of SUBSTRING(mycol FROM 1 FOR 2)= 'xy'.

For all numeric types, the rules of explicit cast and implicit conversion are the same. If cast or conversion causes any digits to be lost from the fractional part, it can take place. If the non-fractional part of the value cannot be represented in the new type, cast or conversion cannot take place and will result in a data exception.

There are special rules for DATE, TIME, TIMESTAMP and INTERVAL casts and conversions.

The type of a table column, a routine parameter, a variable, or the return value of a function can be defined as an array.

<array type> ::= <data type> ARRAY [ <left bracket or trigraph> <maximum cardinality> <right bracket or trigraph> ]

The word ARRAY is added to any valid type definition except BLOB and CLOB type definitions. If the optional <maximum cardinality> is not used, the default value is 1024. The size of the array cannot be extended beyond maximum cardinality.

In the example below, the table contains a column of INTEGER arrays and a column of VARCHAR arrays. The VARCHAR array has an explicit maximum size of 10, which means each array can have between 0 and 10 elements. The INTEGER array has the default maximum size of 1024. The scores column has a default clause with an empty array. The default clause can be defined only as DEFAULT NULL or DEFAULT ARRAY[] and does not allow arrays containing elements.

 CREATE TABLE t (id INT PRIMARY KEY, scores INT ARRAY DEFAULT ARRAY[], names VARCHAR(20) ARRAY[10])

An array can be constructed from value expressions or a query expression.

<array value constructor by enumeration> ::= ARRAY <left bracket or trigraph> <array element list> <right bracket or trigraph>

<array element list> ::= <value expression> [ { <comma> <value expression> }... ]

<array value constructor by query> ::= ARRAY <left paren> <query expression> [ <order by clause> ] <right paren>

In the examples below, arrays are constructed from values, column references or variables, function calls, or query expressions.

 ARRAY [ 1, 2, 3 ]
 ARRAY [ 'HOT', 'COLD' ]
 ARRAY [ var1, var2, CURRENT_DATE ]
 ARRAY (SELECT lastname FROM namestable ORDER BY id)

Inserting and updating a table with an ARRAY column can use array constructors, not only for updated column values, but also in equality search conditions:

 INSERT INTO t VALUES 10, ARRAY[1,2,3], ARRAY['HOT', 'COLD']
 UPDATE t SET names = ARRAY['LARGE', 'SMALL'] WHERE id = 12
 UPDATE t SET names = ARRAY['LARGE', 'SMALL'] WHERE id < 12 AND scores = ARRAY[3,4]

When using a PreparedStatement with an ARRAY parameter, a Java array or a java.sql.Array object may be used to set the parameter.

In the example below prepared statements for INSERT and UPDATE with array parameters are used.

  // create
 String create = "CREATE TABLE t (id INT PRIMARY KEY, scores INT ARRAY DEFAULT ARRAY[], names VARCHAR(20) ARRAY[10])";
 Statement st = connection.createStatement();
 st.execute(create);

 // insert
 String insert = "INSERT INTO t VALUES ?, ?, ?";
 PreparedStatement ps = connection.prepareStatement(insert);
 Object[] numbers = new Object[]{17, 19};
 Object[] data = new Object[]{"one", "two"};
 ps.setInt(1, 10);
 ps.setObject(2, numbers);
 ps.setObject(3, data);
 ps.execute();

 // update
 String update_a = "UPDATE t SET names = ? WHERE id = ?";
 ps = connection.prepareStatement(update_a);
 data = new Object[]{"I", "II", "III", "IV"};
 ps.setObject(1, data);
 ps.setInt(2, 10);
 ps.executeUpdate();        

In the example below, a java.sql.Array is used for the update, using the same data as above:

data = new Object[]{"I", "II", "III", "IV"};
Array array = connection.createArrayOf("VARCHAR", data);
ps.setArray(1, array);
ps.setInt(2, 10);
ps.executeUpdate();        

 INSERT INTO t VALUES 10, ARRAY??(1,2,3??), ARRAY['HOT', 'COLD']
 UPDATE t SET names = ARRAY ??('LARGE', 'SMALL'??) WHERE id = 12
 UPDATE t SET names = ARRAY['LARGE', 'SMALL'] WHERE id < 12 AND scores = ARRAY[3,4]

The most common operations on an array are element reference and assignment, which are used when reading or writing an element of the array. Unlike Java and many other languages, arrays are extended if an element is assigned to an index beyond the current length. This can result in gaps containing NULL elements. Array length cannot exceed the maximum cardinality.

Elements of all arrays, including those that are the result of function calls or other operations can be referenced for reading.

<array element reference> ::= <array value expression> <left bracket> <numeric value expression> <right bracket>

Elements of arrays that are table columns or routine variables can be referenced for writing. This is done in a SET statement, either inside an UPDATE statement, or as a separate statement in the case of routine variables, OUT and INOUT parameters.

<target array element specification> ::= <target array reference> <left bracket or trigraph> <simple value specification> <right bracket or trigraph>

<target array reference> ::= <SQL parameter reference> | <column reference>

Note that only simple values or variables are allowed for the array index when an assignment is performed. The examples below demonstrate how elements of the array are referenced in SELECT and UPDATE statements.

 SELECT scores[ranking], names[ranking] FROM t JOIN t1 on (t.id = t1.tid)
 UPDATE t SET scores[2] = 123, names[2] = 'Reds' WHERE id = 10

Several SQL operations and functions can be used with arrays.

CONCATENATION

Array concatenation is performed similar to string concatenation. All elements of the array on the right are appended to the array on left.

<array concatenation> ::= <array value expression 1> <concatenation operator> <array value expression 2>

<concatenation operator> ::= ||

FUNCTIONS

Functions listed below operate on arrays. Details are described in the Built In Functions chapter.

ARRAY_AGG is an aggregate function and produces an array containing values from different rows of a SELECT statement. Details are described in the Data Access and Change chapter.

SEQUENCE_ARRAY creates an array with sequential elements.

CARDINALITY <left paren> <array value expression> <right paren>

MAX_CARDINALITY <left paren> <array value expression> <right paren>

Array cardinality and max cardinality are functions that return an integer. CARDINALITY returns the element count, while MAX_CARDINALITY returns the maximum declared cardinality of an array.

POSITION_ARRAY <left paren> <value expression> IN <array value expression> [FROM <numeric value expression>] <right paren>

The POSITION_ARRAY function returns the position of the first match for the <value expression> from the start or from the given start position when <numeric value expression> is used.

TRIM_ARRAY <left paren> <array value expression> <comma> <numeric value expression> <right paren>

The TRIM_ARRAY function returns a copy of an array with the specified number of elements removed from the end of the array. The <array value expression> can be any expression that evaluates to an array.

SORT_ARRAY <left paren> <array value expression> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] <right paren>

The SORT_ARRAY function returns a sorted copy of an array. NULL elements appear at the beginning of the new array. You can change the sort direction or the position of NULL elements with the option keywords.

CAST

An array can be cast into an array of a different type. Each element of the array is cast into the element type of the target array type. For example:

 SELECT CAST(scores[ranking] AS VARCHAR(6) ARRAY), names[ranking] FROM t JOIN t1 on (t.id = t1.tid)

UNNEST

Arrays can be converted into table references with the UNNEST keyword.

UNNEST(<array value expression>) [ WITH ORDINALITY ]

The <array value expression> can be any expression that evaluates to an array. A table is returned that contains one column when WITH ORDINALITY is not used, or two columns when WITH ORDINALITY is used. The first column contains the elements of the array (including all the nulls). When the table has two columns, the second column contains the ordinal position of the element in the array. When UNNEST is used in the FROM clause of a query, it implies the LATERAL keyword, which means the array that is converted to table can belong to any table that precedes the UNNEST in the FROM clause. This is explained in the Data Access and Change chapter.

INLINE CONSTRUCTOR

Array constructors can be used in SELECT and other statements. For example, an array constructor with a subquery can return the values from several rows as one array.

The example below shows an ARRAY constructor with a correlated subquery to return the list of order values for each customer. The CUSTOMER table that is included for tests in the DatabaseManager GUI app is the source of the data.

 SELECT FIRSTNAME, LASTNAME, ARRAY(SELECT INVOICE.TOTAL FROM INVOICE WHERE CUSTOMERID = CUSTOMER.ID) AS ORDERS FROM CUSTOMER

 FIRSTNAME LASTNAME  ORDERS                                    
 --------- --------- -------------------------------------- 
 Laura     Steel     ARRAY[2700.90,4235.70]                 
 Robert    King      ARRAY[4761.60]                         
 Robert    Sommer    ARRAY[]                                
 Michael   Smith     ARRAY[3420.30]

COMPARISON

Arrays can be compared for equality. It is possible to define a UNIQUE constraint on a column of ARRAY type. Two arrays are equal if they have the same length and the values at each index position are either equal or both NULL. Array expressions cannot be used in a comparison expression such as GREATER THAN but they can be used in an ORDER BY clause. For example, it is possible to add ORDER BY ORDERS to the above SELECT statement,

USER DEFINED FUNCTIONS and PROCEDURES

Array parameters, variables and return values can be specified in user defined functions and procedures, including aggregate functions. An aggregate function can return an array that contains all the scalar values that have been aggregated. These capabilities allow a wider range of applications to be covered by user defined functions and easier data exchange between the engine and the user's application.

The name of a schema object is an <identifier>. The name belongs to the name-space for the particular kind of schema object. The name is unique within its name-space. For example, each schema has a separate name-space for TRIGGER objects.

In addition to the name-spaces in the schema. Each table has a name-space for the names of its columns.

Because a schema object is always in a schema and a schema always in a catalog, it is possible, and sometimes necessary, to qualify the name of the schema object that is being referenced in an SQL statement. This is done by forming an <identifier chain>. In some contexts, only a simple <identifier> can be used and the <identifier chain> is prohibited. While in some other contexts, the use of <identifier chain> is optional. An identifier chain is formed by qualifying each object with the name of the object that owns its name-space. Therefore, a column name is prefixed with a table name, a table name is prefixed with a schema name, and a schema name is prefixed with a catalog name. A fully qualified column name is in the form <catalog name>.<schema name>.<table name>.<column name>, likewise, a fully qualified sequence name is in the form <catalog name>.<schema name>.<sequence name>.

HyperSQL extends the SQL standard to allow renaming all database objects. The ALTER ... RENAME TO command has slightly different forms depending on the type of object. If an object is referenced in a VIEW or ROUTINE definition, it is not always possible to rename it.

A CHARACTER SET is the whole or a subset of the UNICODE character set.

A character set name can only be a <regular identifier>. There is a separate name-space for character sets.

There are several predefined character sets. These character sets belong to INFORMATION_SCHEMA. However, when they are referenced in a statement, no schema prefix is necessary.

The following character sets, together with some others, have been specified by the SQL Standard:

SQL_CHARACTER, SQL_TEXT, SQL_IDENTIFIER

The SQL_CHARACTER consists of ASCII letters, digits and the symbols used in the SQL language itself. SQL_TEXT and SQL_IDENTIFIER are implementation defined. HyperSQL defines SQL_TEXT as the Unicode character set and SQL_IDENTIFIER as the Unicode character set minus the SQL language special characters.

SQL_TEXT consists of the full set of Unicode characters. These characters can be used in strings and clobs stored in the database. The character repertoire of HyperSQL is the UTF16 character set, which covers all possible character sets.

If a predefined character set is specified for a table column, then any string stored in the column must contain only characters from the specified character set. HyperSQL does not enforce the CHARACTER SET that is specified for a column and may accept any character string supported by SQL_TEXT.

A COLLATION is the method used for ordering character strings in ordered sets and to determine equivalence of two character strings.

The system collation is called SQL_TEXT. This collation sorts according to the Unicode code of the characters, UNICODE_SIMPLE. The system collation is always used for INFORMATION_SCHEMA tables.

The default database collation is the same as the system collation. You can change this default, either with a language collation, or with the SQL_TEXT_UCC. This collation is a case-insensitive form of the UNICODE_SIMPLE collation.

Collations for a large number of languages are supported by HyperSQL. These collations belong to INFORMATION_SCHEMA. However, when they are referenced in a statement, there is no need for a schema prefix.

A different collation than the default collation can be specified for each table column that is defined as CHAR or VARCHAR.

A collation can also be used in an ORDER BY clause.

A collation can be used in the GROUP BY clause.

 CREATE TABLE t (id INTEGER PRIMARY KEY, name VARCHAR(20) COLLATE "English")
 SELECT * FROM t ORDER BY name COLLATE "French"
 SELECT COUNT(*), name FROM t GROUP BY name COLLATE "English 0"

In the examples above, the collation for the column is already specified when it is defined. In the first SELECT statement, the column is sorted using the French collation. In the second SELECT, the "English 0" collation is used in the GROUP BY clause. This collation is case insensitive, so the same name with different uses of upper and lower-case letters is considered the same and counted together.

The supported collations are named according to the language. You can see the list in the INFORMATION_SCHEMA.COLLATIONS view. You can use just the name in double quotes for the default form of the collation. If you add a strength between 0, 1, 2, 3, the case sensitivity and accent sensitivity changes. The value 0 indicates least sensitivity to differences. At this strength the collation is case-insensitive and ignores differences between accented letters. At strength 1, differences between accented letters are taken into account. At strength 2, both case and accent are significant. Finally, 3 indicates additional sensitivity to different punctuation. A second parameter can also be used with values 0 or 1, to indicate how decomposition of accented characters for comparison is handled for languages that support such characters. See the Java and ICU (International Components for Unicode) collation documentation for more details on these values. For example, possible forms of the French collation are "French", "French 0", "French 1", etc., and "French 2 1", etc. When the collation is specified without strength, it seems the system defaults to strength 2, which is case and accent sensitive.

When a collation is not explicitly used in the CREATE TABLE statement for a column, then the database default collation is used for this column. If you change the database default collation afterwards, the new collation will be used.

With the older versions of HyperSQL the special type VARCHAR_IGNORECASE was used as the column type for case-insensitive comparison. Any column already defined as VARCHAR_IGNORECASE will be compared exactly as before. In version 2.3.0 and later, this form is represented by the addition of UCC after the collation name, for example "French UCC". You can still use the SET IGNORECASE TRUE statement in your session to force the UCC to be applied to the collation for the VARCHAR columns of new tables. UCC stands for Upper Case Comparison. Before comparing two strings, both are converted to uppercase using the current collation. This is exactly how VARCHAR_IGNORECASE worked.

It is recommended to use the default SQL_TEXT collation for your general CHAR or VARCHAR columns. For columns where a language collation is desirable, the choice should be made very carefully, because names that are very similar but only differ in the accents may be considered equal in searches.

When comparing two strings, HyperSQL 2.x pads the shorter string with spaces in order to compare two strings of equal length. You can change the default database collation with one that does not pad the string with spaces before comparison. This method of comparison was used in versions older than 2.0.

User defined collations can be created based on existing collations to control the space padding. These collations are part of the current schema.

See the COLLATE keyword and SET DATABASE COLLATION statement in the System Management chapter. The PAD SPACE or NO PAD clause is used to control padding.

	Important
	If you change the default collation of a database when there are tables containing data with CHAR or VARCHAR columns that are part of an index, a primary key or a unique constraint, you must execute SHUTDOWN COMPACT or SHUTDOWN SCRIPT after the change. If you do not do this, your queries and other statements will show erratic behaviour and may result in unrecoverable errors.

A distinct, user-defined TYPE is simply based on a built-in type. A distinct TYPE is used in table definitions and in CAST statements.

Distinct types share a name-space with domains.

A DOMAIN is a user-defined type, simply based on a built-in type. A DOMAIN can have constraints that limit the values that the DOMAIN can represent. A DOMAIN can be used in table definitions and in CAST statements.

Distinct types share a name-space with domains.

A SEQUENCE object produces INTEGER values in sequence. The SEQUENCE can be referenced in special contexts only within certain SQL statements. For each row where the object is referenced, its value is incremented.

There is a separate name-space for SEQUENCE objects.

IDENTITY columns are columns of tables which have an internal, unnamed SEQUENCE object. HyperSQL also supports IDENTITY columns that use a named, external, SEQUENCE object.

SEQUENCE objects and IDENTITY columns are supported fully according to the latest SQL Standard syntax.

Sequences

The SQL Standard syntax and usage is different from what is supported by many existing database engines. Sequences are created with the CREATE SEQUENCE command and their current value can be modified at any time with ALTER SEQUENCE. The next value for a sequence is retrieved with the NEXT VALUE FOR <name> expression. This expression can be used for inserting and updating table rows.

 INSERT INTO mytable VALUES 2, 'John', NEXT VALUE FOR mysequence

You can also use it in select statements. For example, if you want to number the returned rows of a SELECT in sequential order, you can use:

 SELECT NEXT VALUE FOR mysequence, col1, col2 FROM mytable WHERE ...

The semantics of sequences are exactly as defined by SQL:2023. If you use the same sequence twice in the same row in an INSERT statement, you will get the same value, as required by the Standard.

The correct way to use a sequence value is the NEXT VALUE FOR expression.

HyperSQL adds an extension to Standard SQL to return the last value returned by the NEXT VALUE FOR expression in the current session. After a statement containing NEXT VALUE FOR is executed, the value that was returned for NEXT VALUE FOR is available using the CURRENT VALUE FOR expression. In the example below, the NEXT VALUE FOR expression is used to insert a new row. The value that was returned by NEXT VALUE FOR is retrieved with the CURRENT VALUE FOR in the next insert statements to populate two new rows in a different table that has a parent child relationship with the first table. For example, if the value 15 was returned by the sequence, the same value 15 is inserted in the three rows.

 INSERT INTO mytable VALUES 2, 'John', NEXT VALUE FOR mysequence
 INSERT INTO childtable VALUES 4, CURRENT VALUE FOR mysequence
 INSERT INTO childtable VALUES 5, CURRENT VALUE FOR mysequence

The INFORMATION_SCHEMA.SEQUENCES table contains the next value that will be returned from any of the defined sequences. The SEQUENCE_NAME column contains the name and the NEXT_VALUE column contains the next value to be returned. Note that this is only for getting information and you should not use it for accessing the next sequence value. When multiple sessions access the same sequence, the value returned from this table by one session could be used by a different session, causing a sequence value to be used twice unintentionally.

Identity Auto-Increment Columns

Each table can contain a single auto-increment column, known as the IDENTITY column. An IDENTITY column is a SMALLINT, INTEGER, BIGINT, DECIMAL or NUMERIC column with its value generated by a sequence generator.

In HyperSQL 2.x, an IDENTITY column is not by default treated as the primary key for the table (as a result, multi-column primary keys are possible with an IDENTITY column present). Use the SQL standard syntax for declaration of the IDENTITY column.

The SQL standard syntax is used, which allows the initial value and other options to be specified.

<colname> [ INTEGER | BIGINT | DECIMAL | NUMERIC ] GENERATED { BY DEFAULT | ALWAYS} AS IDENTITY [( <options> )]

 /* this table has no primary key */
 CREATE TABLE vals (id INTEGER GENERATED BY DEFAULT AS IDENTITY, data VARBINARY(2000))
 
 /* in this table id becomes primary key because the old syntax is used - avoid this syntax */
 CREATE TABLE vals (id INTEGER IDENTITY, data VARBINARY(2000))

 /* use the standard syntax and explicity declare a primary key identity column */
 CREATE TABLE vals (id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, data VARBINARY(2000))

When you add a new row to such a table using an INSERT INTO <tablename> ... statement, you can use the DEFAULT keyword for the IDENTITY column, which results in an auto-generated value for the column.

The IDENTITY() function returns the last value inserted into any IDENTITY column by this session. Each session manages this function call separately and is not affected by inserts in other sessions. Use CALL IDENTITY() as an SQL statement to retrieve this value. If you want to use the value for a field in a child table, you can use INSERT INTO <childtable> VALUES (...,IDENTITY(),...);. Both types of call to IDENTITY() must be made before any additional update or insert statements are issued by the session.

In triggers and routines, the value returned by the IDENTITY() function is correct for the given context. For example, if a call to a stored procedure inserts a row into a table, causing a new identity value to be generated, a call to IDENTITY() inside the procedure will return the new identity, but a call outside the procedure will return the last identity value that was generated before a call was made to the procedure.

The last inserted IDENTITY value can also be retrieved via JDBC, by specifying the Statement or PreparedStatement object to return the generated value.

The next IDENTITY value to be used can be changed with the following statement. Note that this statement is not used in normal operation and is only for special purposes, for example resetting the identity generator:

 ALTER TABLE <table name> ALTER COLUMN <column name> RESTART WITH <new value>;

For backward compatibility, support has been retained for CREATE TABLE <tablename>(<colname> IDENTITY, ...) as a shortcut which defines the column both as an IDENTITY column and a PRIMARY KEY column. Also, for backward compatibility, it is possible to use NULL as the value of an IDENTITY column in an INSERT statement and the value will be generated automatically. You should avoid these compatibility features as they may be removed from future versions of HyperSQL.

In the following example, the identity value for the first INSERT statement is generated automatically using the DEFAULT keyword. The second INSERT statement uses a call to the IDENTITY() function to populate a row in the child table with the generated identity value.

 CREATE TABLE star (id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 
   firstname VARCHAR(20),
   lastname VARCHAR(20))
 CREATE TABLE movies (starid INTEGER, movieid INTEGER PRIMARY KEY, title VARCHAR(40)) 
 INSERT INTO star (id, firstname, lastname) VALUES (DEFAULT, 'Felix', 'the Cat')
 INSERT INTO movies (starid, movieid, title) VALUES (IDENTITY(), 10, 'Felix in Hollywood')

HyperSQL also supports IDENTITY columns that use an external, named SEQUENCE object. This feature is not part of the SQL Standard. The example below uses this type of IDENTITY. Note the use of CURRENT VALUE FOR seq here is multi-session safe. The returned value is the last value used by this session when the row was inserted into the star table. This value is available until the transaction is committed. After commit, NULL is returned by the CURRENT VALUE FOR expression until the SEQUENCE is used again.

 CREATE SEQUENCE seq
 CREATE TABLE star (id INTEGER GENERATED BY DEFAULT AS SEQUENCE seq PRIMARY KEY, 
   firstname VARCHAR(20),
   lastname VARCHAR(20))
 CREATE TABLE movies (starid INTEGER, movieid INTEGER PRIMARY KEY, title VARCHAR(40)) 
 -- the first insert uses the next value from the sequence seq
 INSERT INTO star (id, firstname, lastname) VALUES (DEFAULT, 'Felix', 'the Cat')
 -- the second insert uses CURRENT VALUE to insert the same auto-generated value into the other table
 INSERT INTO movies (starid, movieid, title) VALUES (CURRENT VALUE FOR seq, 10, 'Felix in Hollywood')

In the SQL environment, tables are the most essential components, as they hold all persistent data.

If TABLE is considered as metadata (without its actual data) it is called a relation in relational theory. It has one or more columns, with each column having a distinct name and a data type. A table usually has one or more constraints which limit the values that can potentially be stored in the TABLE. These constraints are discussed in the next section.

A single column of the table can be defined as IDENTITY. The values stored in this column are auto-generated and are based on an (unnamed) identity sequence, or optionally, a named SEQUENCE object. One or more other columns of the table can be defined as GENERATED by an expression that returns a value based on other columns of the same row.

A VIEW is similar to a TABLE but it does not permanently contain rows of data. A view is defined as a QUERY EXPRESSION, which is often a SELECT statement that references views and tables, but it can also consist of a TABLE CONSTRUCTOR that does not reference any tables or views.

A view has many uses:

A VIEW that returns the columns of a single ordinary TABLE is updatable if the query expression of the view is an updatable query expression as discussed in the Data Access and Change chapter. Some updatable views are insertable-into because the query expression is insertable-into. In these views, each column of the query expressions must be a column of the underlying table and those columns of the underlying table that are not in the view must have a default clause, or be an IDENTITY or GENERATED column. When rows of an updatable view are updated, or new rows are inserted, or rows are deleted, these changes are reflected in the base table. A VIEW definition may specify that the inserted or updated rows conform to the search condition of the view. This is done with the CHECK OPTION clause.

A view that is not updatable according to the above paragraph can be made updatable or insertable-into by adding INSTEAD OF triggers to the view. These triggers contain statements to use the submitted data to modify the contents of the underlying tables of the view separately. For example, a view that represents a SELECT statement that joins two tables can have an INSTEAD OF DELETE trigger with two DELETE statements, one for each table. Views that have an INSTEAD OF trigger are called TRIGGER INSERTABLE, TRIGGER UPDATABLE, etc., according to the triggers that have been defined.

Views share a name-space with tables.

A CONSTRAINT is a child schema object and can belong to a DOMAIN or a TABLE. CONSTRAINT objects can be defined without specifying a name. In this case the system generates a name for the new object beginning with "SYS_". This default naming can be changed with the SET DATABASE SQL SYS INDEX NAMES TRUE statement, to use the constraint name as the name of the index.

In a DOMAIN, CHECK constraints can be defined that limit the value represented by the DOMAIN. These constraints work exactly like a CHECK constraint on a single column of a table as described below.

In a TABLE, a constraint takes three basic forms.

CHECK

A CHECK constraint consists of a <search condition> that must not be false (can be unknown) for each row of the table. The <search condition> can reference all the columns of the current row. HyperSQL does not support the optional feature of the SQL Standard that allows a <subquery> referencing tables and views in the database in a <search condition>.

NOT NULL

A simple form of check constraint is the NOT NULL constraint, which applies to a single column.

UNIQUE

A UNIQUE constraint is based on an equality comparison of values of specific columns (taken together) of one row with the same values from each of the other rows. The result of the comparison must never be true (can be false or unknown). If a row of the table has NULL in any of the columns of the constraint, it conforms to the constraint. A unique constraint on multiple columns (c1, c2, c3, ..) means that in no two rows, the sets of values for the columns can be equal unless at least one of them is NULL. Each single column taken by itself can have repeat values in different rows. The following example satisfies a UNIQUE constraint on the two columns

If the SET DATABASE SQL UNIQUE NULLS FALSE has been set, then if not all the values set of columns are null, the not null values are compared and it is disallowed to insert identical rows that contain at least one not-null value.

PRIMARY KEY

A PRIMARY KEY constraint is equivalent to a UNIQUE constraint on one or more NOT NULL columns. Only one PRIMARY KEY can be defined in each table.

FOREIGN KEY

A FOREIGN key constraint is based on an equality comparison between values of specific columns (taken together) of each row with the values of the columns of a UNIQUE constraint on another table or the same table. The result of the comparison must never be false (can be unknown). A special form of FOREIGN KEY constraint, based on its CHECK clause, allows the result to be unknown only if the values for all columns are NULL. A FOREIGN key can be declared only if a UNIQUE constraint exists on the referenced columns.

Constraints share a name space with assertions.

An ASSERTION is a top-level schema object. It consists of a <search condition> that must not be false (can be unknown). HyperSQL does not yet support assertions.

Assertions share a name-space with constraints

A TRIGGER is a child schema object that always belongs to a TABLE or a VIEW.

Each time a DELETE, UPDATE or INSERT is performed on the table or view, additional actions are taken by the triggers that have been declared on the table or view.

Triggers are discussed in detail in Triggers chapter.

There is a separate name space for triggers.

Routines are user-defined functions or procedures. The names and usage of functions and procedures are different. FUNCTION is a routine that can be referenced in many types of statements. PROCEDURE is a routine that can be referenced only in a CALL statement.

There is a separate name-space for routines.

Because of the possibility of overloading, each routine can have more than one name. The name of the routine is the same for all overloaded variants, but each variant has a specific name, different from all other routine names and specific names in the schema. The specific name can be specified in the routine definition statement. Otherwise it is assigned by the engine. The specific name is used only for schema manipulation statements, which need to reference a specific variant of the routine. For example, if a routine has two signatures, each signature has its own specific name. This allows the user to drop one of the signatures while keeping the other.

Routines are discussed in detail in the SQL-Invoked Routines chapter.

Indexes are an implementation-defined extension to the SQL Standard. HyperSQL has a dedicated name-space for indexes in each schema.

Synonyms are user-defined names that refer to other schema objects. Synonyms can be defined for TABLE, VIEW, SEQUENCE, PROCEDURE and FUNCTION names and used in SELECT, UPDATE, CALL, etc. statements. They cannot be used in DDL statements. Synonym are in schemas, but they are used without a schema qualifier. When used, a synonym is immediately translated to the target name and the target name is used in the actual statement. The access privileges to the target object are checked.

 CREATE SYNONYM REG FOR OTHER_SCHEMA.REGISTRATION_DETAIL_TABLE

 SELECT R_ID, R_DATE FROM REG WHERE R_DATA > CURRENT_DATE - 3 DAY

A synonym cannot be the same as the name of any existing object in the schema.

These elements and statements are used for different types of object. They are described here, before the statements that can use them.

identifier

definition of identifier

<identifier> ::= <regular identifier> | <delimited identifier> | <SQL language identifier>

<delimited identifier> ::= <double quote> <character sequence> <double quote>

<regular identifier> ::= <special character sequence>

<SQL language identifier> ::= <special character sequence>

A <delimited identifier> is a sequence of characters enclosed with double-quote symbols. All characters are allowed in the character sequence.

A <regular identifier> is a special sequence of characters. It consists of letters, digits, and the underscore characters. It must begin with a letter. All the letters are translated to their upper-case version.

The database setting, SET DATABASE SQL REGULAR NAMES FALSE can be used to relax the rules for regular identifier. With this setting, an underscore character can appear at the start of the regular identifier, and the dollar sign character can be used in the identifier.

A <SQL language identifier> is similar to <regular identifier> but the letters can range only from A-Z in the ASCII character set. This type of identifier is used for names of CHARACTER SET objects.

If the character sequence of a delimited identifier is the same as an undelimited identifier, it represents the same identifier. For example, "JOHN" is the same identifier as JOHN. In a <regular identifier> the case-normal form is considered for comparison. This form consists of the upper-case equivalent of all the letters. When a database object is created with one of the CREATE statements or renamed with the ALTER statement, if the name is enclosed in double quotes, the exact name is used as the case-normal form. But if it is not enclosed in double quotes, the name is converted to uppercase and this uppercase version is stored in the database as the case-normal form.

The character sequence length of all identifiers must be between 1 and 128 characters.

A reserved word is one that is used by the SQL Standard for special purposes. It is similar to a <regular identifier> but it cannot be used as an identifier for user objects. If a reserved word is enclosed in double quote characters, it becomes a quoted identifier and can be used for database objects.

Case sensitivity rules for identifiers can be described simply as follows:

CASCADE or RESTRICT

drop behavior

<drop behavior> ::= CASCADE | RESTRICT

The <drop behavior> is a required element of statements that drop a SCHEMA or a schema object. If <drop behavior> is not specified then RESTRICT is implicit. It determines the effect of the statement if there are other objects in the catalog that reference the SCHEMA or the schema object. If RESTRICT is specified, the statement fails if there are referencing objects. If CASCADE is specified, all the referencing objects are modified or dropped with cascading effect. Whether a referencing object is modified or dropped, depends on the kind of schema object that is dropped.

IF EXISTS

drop condition (HyperSQL)

<if exists clause> ::= IF EXISTS

This clause is not part of the SQL standard and is a HyperSQL extension to some commands that drop objects (schemas, tables, views, sequences, and indexes). If it is specified, then the statement does not return an error if the drop statement is issued on a non-existent object.

IF NOT EXISTS

create condition (HyperSQL)

<if not exists clause> ::= IF NOT EXISTS

This clause is not part of the SQL standard and is a HyperSQL extension to CREATE statements that create schemas, tables, views, sequences and indexes, as well as ALTER TABLE ... ADD CONSTRAINT and ADD COLUMN statements. If it is specified, then the statement does not return an error if the CREATE or ALTER statement is for an object name that already exists.

SPECIFIC

specific routine designator

<specific routine designator> ::= SPECIFIC <routine type> <specific name>

<routine type> ::= ROUTINE | FUNCTION | PROCEDURE

This clause is used in statements that need to specify one of the multiple versions of an overloaded routine. The <specific name> is the one specified in the <routine definition> statement. The keyword ROUTINE can be used instead of either FUNCTION or PROCEDURE.

RENAME

rename statement (HyperSQL)

<rename statement> ::= ALTER <object type> <name> RENAME TO <new name>

<object type> ::= CATALOG | SCHEMA | DOMAIN | TYPE | TABLE | CONSTRAINT | INDEX | ROUTINE | SPECIFIC ROUTINE

<column rename statement> ::= ALTER TABLE <table name> ALTER COLUMN <name> RENAME TO <new name>

This statement is used to rename an existing object. It is not part of the SQL Standard. The specified <name> is the existing name, which can be qualified with a schema name, while the <new name> is the new name for the object.

COMMENT

comment statement (HyperSQL)

<comment statement> ::= COMMENT ON { TABLE | COLUMN | ROUTINE | SEQUENCE | TRIGGER} <name> IS <character string literal>

Adds a comment to the object metadata, which can later be read from an INFORMATION_SCHEMA view. This command is not part of the SQL Standard. The strange syntax is due to compatibility with other database engines that support the statement. The <name> is the name of a table, view, column or routine. The name of the column consists of dot-separated <table name> <period> <column name>. The name of the table, view or routine can be a simple name. All names can be qualified with a schema name. If there is already a comment on the object, the new comment will replace it. Comments can be added to views and their columns using the TABLE keyword.

The comments appear in the results returned by JDBC DatabaseMetaData methods, getTables() and getColumns(). The INFORMATION_SCHEMA.SYSTEM_COMMENTS view contains the comments. You can query this view using the schema name, object name, and column name to retrieve the comments.

CREATE SCHEMA

schema definition

The CREATE_SCHEMA or DBA role is required in order to create a schema. A schema can be created with or without schema objects. Schema objects can always be added after creating the schema, or existing ones can be dropped. Within the <schema definition> statement, all schema object creation takes place inside the newly created schema. Therefore, if a schema name is specified for the schema objects, the name must match that of the new schema. In addition to statements for creating schema objects, the statement can include instances of <grant statement> and <role definition>. This is a curious aspect of the SQL standard, as these elements do not really belong to schema creation.

<schema definition> ::= CREATE SCHEMA <schema name clause> [ <schema character set specification> ] [ <schema element>... ]

<schema name clause> ::= <schema name> | AUTHORIZATION <authorization identifier> | <schema name> AUTHORIZATION <authorization identifier>

If the name of the schema is specified simply as <schema name>, then the AUTHORIZATION is the current user. Otherwise, the specified <authorization identifier> is used as the AUTHORIZATION for the schema. If <schema name> is omitted, then the name of the schema is the same as the specified <authorization identifier>.

<schema element> ::= <table definition> | <view definition> | <domain definition> | <character set definition> | <collation definition> | <transliteration definition> | <assertion definition> | <trigger definition> | <user-defined type definition> | <user-defined cast definition> | <user-defined ordering definition> | <transform definition> | <schema routine> | <sequence generator definition> | <grant statement> | <role definition>

An example of the statement is given below. Note that a single semicolon appears at the end. There should be no semicolon between the statements:

 CREATE SCHEMA ACCOUNTS AUTHORIZATION DBA
   CREATE TABLE AB(A INTEGER, ...)
   CREATE TABLE CD(C CHAR(10), ...)
   CREATE VIEW VI AS SELECT ...
   GRANT SELECT ON AB TO PUBLIC
   GRANT SELECT ON CD TO JOE;

It is not really necessary to create a schema and all its objects as one command. The schema can be created first, and its objects can be created one by one.

DROP SCHEMA

drop schema statement

<drop schema statement> ::= DROP SCHEMA [ IF EXISTS ] <schema name> [ IF EXISTS ] <drop behavior>

This command destroys an existing schema. If <drop behavior> is RESTRICT, the schema must be empty, otherwise an error is raised. If CASCADE is specified as <drop behavior>, then all the objects contained in the schema are destroyed with a CASCADE option.

CREATE TABLE

table definition

<table definition> ::= CREATE [ { <table scope> | <table type> } ] TABLE [ IF NOT EXISTS ] <table name> <table contents source> [ WITH SYSTEM VERSIONING ] [ ON COMMIT { PRESERVE | DELETE } ROWS ]

<table scope> ::= { GLOBAL | LOCAL } TEMPORARY

<table type> :: = MEMORY | CACHED

<table contents source> ::= <table element list> | <as subquery clause>

<table element list> ::= <left paren> <table element> [ { <comma> <table element> }... ] <right paren>

<table element> ::= <column definition> | <table period definition> | <table constraint definition> | <like clause>

like clause

A <like clause> copies all column definitions from another table into the newly created table. Its three options indicate if the <default clause>, <identity column specification> and <generation clause> associated with the column definitions are copied or not. If an option is not specified, it defaults to EXCLUDING. The <generation clause> refers to columns that are generated by an expression but not to identity columns. All NOT NULL constraints are copied with the original columns, other constraints are not. The <like clause> can be used multiple times, allowing the new table to have copies of the column definitions of one or more other tables.

 CREATE TABLE t (id INTEGER PRIMARY KEY, LIKE atable INCLUDING DEFAULTS EXCLUDING IDENTITY)

<like clause> ::= LIKE <table name> [ <like options> ]

<like options> ::= <like option>...

<like option> ::= <identity option> | <column default option> | <generation option>

<identity option> ::= INCLUDING IDENTITY | EXCLUDING IDENTITY

<column default option> ::= INCLUDING DEFAULTS | EXCLUDING DEFAULTS

<generation option> ::= INCLUDING GENERATED | EXCLUDING GENERATED

as subquery clause

<as subquery clause> ::= [ <left paren> <column name list> <right paren> ] AS <table subquery> { WITH NO DATA | WITH DATA }

An <as subquery clause> used in table definition creates a table based on a <table subquery>. This kind of table definition is similar to a view definition. It can include new column names to override the column names specified in the subquery. If WITH DATA is specified, then the new table will contain the rows of data returned by the <table subquery>.

 CREATE TABLE t (a, b, c) AS (SELECT * FROM atable) WITH DATA

column definition

A column definition consists of a <column name> and in most cases a <data type> or <domain name> as minimum. The other elements of <column definition> are optional. Each <column name> in a table is unique.

<column definition> ::= <column name> [ <data type or domain name> ] [ <default clause> | <identity column specification> | <identity column sequence specification> | <generation clause> ] [ <update clause> ] [ <column constraint definition>... ] [ <collate clause> ]

<data type or domain name> ::= <data type> | <domain name>

<column constraint definition> ::= [ <constraint name definition> ] <column constraint> [ <constraint characteristics> ]

<column constraint> ::= NOT NULL | <unique specification> | <references specification> | <check constraint definition>

A <column constraint definition> is a shortcut for a <table constraint definition>. A constraint that is defined in this way is automatically turned into a table constraint. A name is automatically generated for the constraint and assigned to it.

If a <collate clause> is specified, then a UNIQUE or PRIMARY KEY constraint or an INDEX on the column will use the specified collation. Otherwise the default collation for the database is used.

GENERATED

generated columns

The value of a column can be auto-generated in two ways.

One way is specific to columns of integral types (INTEGER, BIGINT, etc.) and associates a sequence generator with the column. When a new row is inserted into the table, the value of the column is generated as the next available value in the sequence.

The SQL Standard supports the use of unnamed sequences with the IDENTITY keyword. In addition, HyperSQL supports the use of a named SEQUENCE object, which must be in the same schema as the table.

<identity column specification> ::= GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ <left paren> <common sequence generator options> <right paren> ]

<identity column sequence specification ::= GENERATED BY DEFAULT AS SEQUENCE <sequence name>

The <identity column specification> or <identity column sequence specification> can be specified for only a single column of the table.

The <identity column specification> is used for columns which represent values based on an unnamed sequence generator. It is possible to insert a row into the table without specifying a value for the column. The value is then generated by the sequence generators according to its rules. An identity column may or may not be the primary key. Example below:

 CREATE TABLE t1 (id INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 100), name VARCHAR(20) PRIMARY KEY)
 CREATE TABLE t2 (id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) PRIMARY KEY, name VARCHAR(20))

The <identity column sequence specification> is used when the column values are based on a named SEQUENCE object (which must already exist). Example below:

 CREATE TABLE t3 (id INTEGER GENERATED BY DEFAULT AS SEQUENCE seq, name VARCHAR(20) PRIMARY KEY)

Inserting rows is done in the same way for a named or unnamed sequence generator. In both cases, if no value is specified to be inserted, or the DEFAULT keyword is used for the column, the value is generated by the sequence generator. If a value is specified, this value is used if the column definition has the BY DEFAULT specification. If the column definition has the ALWAYS specification, a value can be specified but the OVERRIDING SYSTEM VALUES must be specified in the INSERT statement. In the example below, the OVERRIDING clause is required because a user value is provided.

 INSERT INTO t1 (id, name) OVERRIDING SYSTEM VALUE VALUES ( 14, 'Test Value')

The alternative form of the OVERRIDING clause is OVERRIDING USER VALUES. This is not used much as it is always possible to avoid it. When this option is specified, the database engine ignores the value provided by user and inserts the generated sequence value instead.

The other way in which the column value is auto-generated is by using the values of other columns in the same row. This method is often used to create an index on a value that is derived from other column values.

<generation clause> ::= GENERATED ALWAYS AS <generation expression>

<generation expression> ::= <left paren> <value expression> <right paren>

The <generation clause> is used for special columns which represent values based on the values held in other columns in the same row. The <value expression> must reference only other, non-generated, columns of the table in the same row. Any function used in the expression must be deterministic and must not access SQL-data. No <query expression> is allowed. When <generation clause> is used, <data type> must be specified.

A generated column can be part of a foreign key or unique constraints or a column of an index. This capability is the main reason for using generated columns. A generated column may contain a formula that computes a value based on the values of other columns. Fast searches of the computed value can be performed when an index is declared on the generated column. Or the computed values can be declared to be unique, using a UNIQUE constraint on the table. The computed column cannot be overridden by user supplied values. When a row is updated and the column values change, the generated columns are computed with the new values.

When a row is inserted into a table, or an existing row is updated, no value except DEFAULT can be specified for a generated column. In the example below, data is inserted into the non-generated columns and the generated column will contain 'Felix the Cat' or 'Pink Panther'.

 CREATE TABLE t (id INTEGER PRIMARY KEY, 
   firstname VARCHAR(20),
   lastname VARCHAR(20), 
   fullname VARCHAR(40) GENERATED ALWAYS AS (firstname || ' ' || lastname)) 
 INSERT INTO t (id, firstname, lastname) VALUES (1, 'Felix', 'the Cat')
 INSERT INTO t (id, firstname, lastname, fullname) VALUES (2, 'Pink', 'Panther', DEFAULT)

DEFAULT

default clause

A default clause can be used if GENERATED is not specified. If a column has a <default clause> then it is possible to insert a row into the table without specifying a value for the column.

<default clause> ::= DEFAULT <default option>

<default option> ::= <literal> | <datetime value function> | USER | CURRENT_USER | CURRENT_ROLE | SESSION_USER | SYSTEM_USER | CURRENT_CATALOG | CURRENT_SCHEMA | CURRENT_PATH | NULL

The type of the <default option> must match the type of the column.

In PGS (PostgreSQL) compatibility mode, a NEXTVAL function can be used. Also, in MSS compatibility mode, the default value can be enclosed in parentheses.

ON UPDATE

on update clause

If a column has a <on update clause> then every time an UPDATE or MERGE statement updates the values of the other columns of the row, the value in this column is updated to the CURRENT_TIMESTAMP. If the UPDATE statement explicitly updates this column, then the explicit value is used instead of CURRENT TIMESTAMP.

<on update clause> ::= ON UPDATE CURRENT_TIMESTAMP

The type of the column must be TIMESTAMP or TIMESTAMP WITH TIME ZONE.

This feature is not part of the SQL Standard and is similar to MySQL's ON UPDATE clause.

CONSTRAINT

constraint name and characteristics

<constraint name definition> ::= CONSTRAINT <constraint name>

<constraint characteristics> ::= <constraint check time> [ [ NOT ] DEFERRABLE [ <constraint check time> ] ]

<constraint check time> ::= INITIALLY DEFERRED | INITIALLY IMMEDIATE

Specify the name of a constraint and its characteristics. By default, the constraint is NOT DEFERRABLE and INITIALLY IMMEDIATE. This means the constraint is enforced as soon as a data change statement is executed. If INITIALLY DEFERRED is specified, then the constraint is enforced when the session commits. The characteristics must be compatible. The constraint check time can be changed temporarily for an SQL session. HyperSQL does not support deferring constraint enforcement. This feature of the SQL Standard has been criticised because it allows a session to read uncommitted data that violates database integrity constraints but has not yet been checked.

CONSTRAINT

table constraint definition

<table constraint definition> ::= [ <constraint name definition> ] <table constraint> [ <constraint characteristics> ]

<table constraint> ::= <unique constraint definition> | <referential constraint definition> | <check constraint definition>

Three kinds of constraint can be defined on a table: UNIQUE (including PRIMARY KEY), FOREIGN KEY and CHECK. Each kind has its own rules to limit the values that can be specified for different columns in each row of the table.

UNIQUE

unique constraint definition

<unique constraint definition> ::= <unique specification> <left paren> <unique column list> <right paren> | UNIQUE ( VALUE )

<unique specification> ::= UNIQUE | PRIMARY KEY

<unique column list> ::= <column name list>

A unique constraint is specified on a single column or on multiple columns. On each set of columns taken together, only one UNIQUE constraint can be specified. Each column of a PRIMARY KEY constraint has an implicit NOT NULL constraint.

If UNIQUE( VALUE ) is specified, the constraint created on all columns of the table.

FOREIGN KEY

referential constraint definition

<referential constraint definition> ::= FOREIGN KEY <left paren> <referencing columns> <right paren> <references specification>

<references specification> ::= REFERENCES <referenced table and columns> [ MATCH <match type> ] [ <referential triggered action> ]

<match type> ::= FULL | PARTIAL | SIMPLE

<referencing columns> ::= <reference column list>

<referenced table and columns> ::= <table name> [ <left paren> <reference column list> <right paren> ]

<reference column list> ::= <column name list>

<referential triggered action> ::= <update rule> [ <delete rule> ] | <delete rule> [ <update rule> ]

<update rule> ::= ON UPDATE <referential action>

<delete rule> ::= ON DELETE <referential action>

<referential action> ::= CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION

A referential constraint allows links to be established between the rows of two tables. The specified list of <referencing columns> corresponds one by one to the columns of the specified list of <referenced columns> in another table (or sometimes in the same table). For each row in the table, a row must exist in the referenced table with equivalent values in the two column lists. There must exist a single unique constraint in the referenced table on all the <referenced columns>.

The [ MATCH match type ] clause is optional and has an effect only on multi-column foreign keys and only on rows containing at least a NULL in one of the <referencing columns>. If the clause is not specified, MATCH SIMPLE is the default. If MATCH SIMPLE is specified, then any NULL means the row can exist (without a corresponding row in the referenced table). If MATCH FULL is specified then either all the column values must be NULL or none of them. MATCH PARTIAL allows any NULL but the non NULL values must match those of a row in the referenced table. HyperSQL does not support MATCH PARTIAL.

Referential actions are specified with ON UPDATE and ON DELETE clauses. These actions take place when a row in the referenced table (the parent table) has referencing rows in the referencing table and it is deleted or modified with any SQL statement. The default is NO ACTION. This means the SQL statement that causes the DELETE or UPDATE is terminated with an exception. The RESTRICT option is similar and works exactly the same without deferrable constraints (which are not allowed by HyperSQL). The other three options, CASCADE, SET NULL and SET DEFAULT all allow the DELETE or UPDATE statement to complete. With DELETE statements the CASCADE option results in the referencing rows to be deleted. With UPDATE statements, the changes to the values of the referenced columns are copied to the referencing rows. With both DELETE or UPDATE statement, the SET NULL option results in the columns of the referencing rows to be set to NULL. Similarly, the SET DEFAULT option results in the columns of the referencing rows to be set to their default values.

CHECK

check constraint definition

<check constraint definition> ::= CHECK <left paren> <search condition> <right paren>

A CHECK constraint can exist for a TABLE or for a DOMAIN. The <search condition> evaluates to an SQL BOOLEAN value for each row of the table. Within the <search condition> all columns of the table row can be referenced. For all rows of the table, the <search condition> evaluates to TRUE or UNKNOWN. When a new row is inserted, or an existing row is updated, the <search condition> is evaluated and if it is FALSE, the insert or update fails.

A CHECK constraint for a DOMAIN is similar. In its <search condition>, the term VALUE is used to represents the value to which the DOMAIN applies.

 CREATE TABLE t (a VARCHAR(20) CHECK (a IS NOT NULL AND CHARACTER_LENGTH(a) > 2))

The search condition of a CHECK constraint cannot contain any function that is not deterministic. A check constraint is a data integrity constraint; therefore it must hold with respect to the rest of the data in the database. It cannot use values that are temporal or ephemeral. For example, CURRENT_USER is a function that returns different values depending on who is using the database, or CURRENT_DATE changes day-to-day. Some temporal expressions are retrospectively deterministic and are allowed in check constraints. For example, (CHECK VALUE < CURRENT_DATE) is valid, because CURRENT_DATE will not move backwards in time, but (CHECK VALUE > CURRENT_DATE) is not acceptable.

If you want to enforce the condition that a date value that is inserted into the database belongs to the future (at the time of insertion), or any similar constraint, then use a TRIGGER with the desired condition.

DROP TABLE

drop table statement

<drop table statement> ::= DROP TABLE [ IF EXISTS ] <table name> [ IF EXISTS ] <drop behavior>

Destroy a table. The default drop behaviour is RESTRICT and will cause the statement to fail if there is any view, routine or foreign key constraint that references the table. If <drop behavior> is CASCADE, it causes all schema objects that reference the table to drop. Referencing views are dropped. In the case of foreign key constraints that reference the table, the constraint is dropped, rather than the TABLE that contains it.

System-versioned tables are tables that contain a SYSTEM_TIME period consisting of pair of columns defined as auto-generated TIMESTAMP WITH TIME ZONE, together with the SYSTEM VERSIONING clause.

The basic component is the SYSTEM_TIME period. For each row currently in the table, the start timestamp column, designated as ROW START, contains the UTC timestamp of the transaction of the INSERT or UPDATE statement that last modified the row. The end timestamp column, designated as ROW END, contains a timestamp in the distant future (end of epoch) that indicates the expiration date of the row. HyperSQL uses DATE '10000-01-01' as the expiration timestamp. A table can have the SYSTEM_TIME period without system versioning.

When WITH SYSTEM VERSIONING is used in table definition, any DELETE or UPDATE is performed as usual. But the deleted rows, and the old versions of the updated rows, are kept in the table with the expiration timestamp changed to the UTC CURRENT_TIMESTAMP at the start of the transaction that contains the UPDATE or DELETE. For example, a row that is updated twice has two old versions kept in the table as well as the current version.

The history rows cannot be modified. Any DELETE or UPDATE statement only sees the current version of each row of the table and modifies them. SELECT statements also see the current version of the rows, unless the table reference in the SELECT statement is followed by FOR SYSTEM_TIME AS OF <timestamp> or FOR SYSTEM_TIME FROM <start timestamp> TO <end timestamp> or FOR SYSTEM_TIME BETWEEN <start timestamp> AND <end timestamp>.

In a CREATE TABLE statement, the two period columns must be defined as follows:

<period begin column name> <timestamp data type> GENERATED ALWAYS AS ROW START

<period end column name> <timestamp data type> GENERATED ALWAYS AS ROW END

The <table period definition> references the period column, in a format similar to a UNIQUE constraint.

<table period definition> ::= PERIOD FOR SYSTEM_TIME <left paren> <period begin column name> <comma> <period end column name> <right paren>

The timestamp type actually used by the system is always TIMESTAMP(6) WITH TIME ZONE, regardless of the type specified by the user.

An existing table can be converted to a system-versioned table. Two statement executions are needed. First, the ALTER TABLE statement to create the SYSTEM_TIME period and its columns must be executed, followed by the ALTER TABLE statement to add SYSTEM VERSIONING.

Conversely, system versioning can be removed from a table. The system period can be dropped after dropping system versioning.

It is not allowed to change the structure of a system-versioned table by adding or removing columns.

See the ALTER TABLE statements in this chapter.

Table settings statements change the attributes of tables. These attributes are specific to HyperSQL and are not part of the SQL Standard.

SET TABLE CLUSTERED

set table clustered property

<set table clustered statement> ::= SET TABLE <table name> CLUSTERED ON <left paren> <column name list> <right paren>

Set the row clustering property of a table. The <column name list> is a list of column names that must correspond to the columns of an existing PRIMARY KEY, UNIQUE or FOREIGN KEY index, or to the columns of a user defined index. This statement is only valid for CACHED or TEXT tables.

Tables rows are stored in the database files as they are created, sometimes at the end of the file, sometimes in the middle of the file. After a CHECKPOINT DEFRAG or SHUTDOWN COMPACT, the rows are reordered according to the primary key of the table, or if there is no primary key, in no particular order.

When several consecutive rows of a table are retrieved during query execution it is more efficient to retrieve rows that are stored adjacent to one another. After executing this command, nothing changes until a CHECKPOINT DEFRAG or SHUTDOWN COMPACT or SHUTDOWN SCRIPT is performed. After these operations, the rows are stored in the specified clustered order. The property is stored in the database and applies to all future reordering of rows. Note that if extensive inserts or updates are performed on the tables, the rows will get out of order until the next reordering.

SET TABLE TYPE

set table type

<set table type statement> ::= SET TABLE <table name> TYPE { MEMORY | CACHED }

Changes the storage type of an existing table between CACHED and MEMORY types.

Only a user with the DBA role can execute this statement.

SET TABLE writability

set table write property

<set table read only statement> ::= SET TABLE <table name> { READ ONLY | READ WRITE }

Set the writability property of a table. Tables are writable by default. This statement can be used to change the property between READ ONLY and READ WRITE. This is a feature of HyperSQL.

SET TABLE SOURCE

set table source statement

<set table source statement> ::= SET TABLE <table name> SOURCE <file and options> [DESC]

<file and options>::= <doublequote> <file path> [<semicolon> <property>...] <doublequote>

Set the text source for a text table. This statement cannot be used for tables that are not defined as TEXT TABLE.

In the example below, the text source of the table is set to "myfile", the field separator to the pipe symbol, and the varchar separator to the tilde symbol.

 SET TABLE mytable SOURCE 'myfile;fs=|;vs=.;vs=~'

Only a user with the DBA role can execute this statement.

SET TABLE SOURCE HEADER

set table source header statement

<set table source header statement> ::= SET TABLE <table name> SOURCE HEADER <header string>

Set the header for the text source for a text table. If this command is used, the <header string> is used as the first line of the source file of the text table. This line is not part of the table data. Only a user with the DBA role can execute this statement.

SET TABLE SOURCE on-off

set table source on-off statement

<set table source on-off statement> ::= SET TABLE <table name> SOURCE { ON | OFF }

Attach or detach a text table from its text source. This command does not change the properties or the name of the file that is the source of a text table. When OFF is specified, the command detaches the table from its source and closes the file for the source. In this state, it is not possible to read or write to the table. This allows the user to replace the file with a different file, or delete it. When ON is specified, the source file is read. Only a user with the DBA role can execute this statement.

Table manipulation statements modify the objects such as columns and constraints. Some of these statements are defined by the SQL Standard. Others are HyperSQL extensions.

ALTER TABLE

alter table statement

<alter table statement> ::= ALTER TABLE <table name> <alter table action>

<alter table action> ::= <add column definition> | <alter column definition> | <drop column definition> | <add table constraint definition> | <drop table constraint definition> | <add table period definition> | <drop table period definition> | <add system versioning clause> | <drop system versioning clause>

Change the definition of a table. Specific types of this statement are covered below.

ADD COLUMN

add column definition

<add column definition> ::= ADD [ COLUMN ] [IF NOT EXISTS] <column definition> [ BEFORE <other column name> ]

Add a column to an existing table. The <column definition> is specified the same way as it is used in <table definition>. HyperSQL allows the use of [ BEFORE <other column name> ] to specify at which position the new column is added to the table.

If the table contains rows, the new column must have a <default clause> or use one of the forms of GENERATED. The column values for each row is then filled with the result of the <default clause> or the generated value.

DROP COLUMN

drop column definition

<drop column definition> ::= DROP [ COLUMN ] <column name> <drop behavior>

Destroy a column of a base table. The <drop behavior> is either RESTRICT or CASCADE. If the column is referenced in a table constraint that references other columns as well as this column, or if the column is referenced in a VIEW, or the column is referenced in a TRIGGER, then the statement will fail if RESTRICT is specified. If CASCADE is specified, then any CONSTRAINT, VIEW or TRIGGER object that references the column is dropped with a cascading effect.

ADD CONSTRAINT

add table constraint definition

<add table constraint definition> ::= ADD <table constraint definition>

Add a constraint to a table. The existing rows of the table must conform to the added constraint, otherwise the statement will not succeed.

DROP CONSTRAINT

drop table constraint definition

<drop table constraint definition> ::= DROP CONSTRAINT <constraint name> <drop behavior>

Destroy a constraint on a table. The <drop behavior> has an effect only on UNIQUE and PRIMARY KEY constraints. If such a constraint is referenced by a FOREIGN KEY constraint, the FOREIGN KEY constraint will be dropped if CASCADE is specified. If the columns of such a constraint are used in a GROUP BY clause in the query expression of a VIEW or another kind of schema object, and a functional dependency relationship exists between these columns and the other columns in that query expression, then the VIEW or other schema object will be dropped when CASCADE is specified.

ADD SYSTEM PERIOD

add system period definition

<add table system period definition> ::= ADD PERIOD FOR SYSTEM_TIME <left paren> <period begin column name> <comma> <period end column name> <right paren> ADD COLUMN <period begin column name> <timestamp data type> GENERATED ALWAYS AS ROW START ADD COLUMN <period end column name> <timestamp data type> GENERATED ALWAYS AS ROW END

Add the system period definition and columns to a table. The long statement must be entered in full. The existing rows of the table are marked as created at the current timestamp with end-of-epoch expiration timestamp.

 ALTER TABLE t ADD PERIOD FOR SYSTEM_TIME(rs, re) ADD COLUMN rs TIMESTAMP GENERATED ALWAYS AS ROW START ADD COLUMN re TIMESTAMP GENERATED ALWAYS AS ROW END

DROP SYSTEM PERIOD

drop system period definition

<drop table system period definition> ::= DROP PERIOD FOR SYSTEM_TIME <drop behavior>

Drop the system period definition and columns of a table. The <drop behavior> is either RESTRICT or CASCADE. If the system period or its columns have been referenced in other database object such as VIEW or ROUTINE, then the statement will fail if RESTRICT is specified. If CASCADE is specified, then any such VIEW or other database object that references the period or its columns is dropped with a cascading effect

ADD SYSTEM VERSIONING

add system versioning clause

<add system versioning clause> ::= ADD SYSTEM VERSIONING

Add system versioning to a table that already has a SYSTEM_TIME period definition and columns.

 ALTER TABLE t ADD SYSTEM VERSIONING

DROP SYSTEM VERSIONING

drop system versioning clause

<drop table system period definition> ::= DROP SYSTEM VERSIONING

Drop system versioning of a table. The <drop behavior> is either RESTRICT or CASCADE. If system versioning has been referenced in other database object such as VIEW or ROUTINE, then the statement will fail if RESTRICT is specified. If CASCADE is specified, then any such VIEW or other database object that references system versioning is dropped with a cascading effect A references to system versioning consists of the FOR SYSTEM_TIME clause in a SELECT statement. With the successful execution of this statement, all the history rows in the table are deleted and only the current versions of rows survive. The period for SYSTEM_TIME, and its columns, survive after dropping versioning.

ALTER COLUMN

alter column definition

<alter column definition> ::= ALTER [ COLUMN ] <column name> <alter column action>

<alter column action> ::= <set column default clause> | <drop column default clause> | <alter column data type clause> | <alter identity column specification> | <alter column nullability> | <alter column name> | <add column identity specification> | <drop column identity specification>

Change a column and its definition. Specific types of this statement are covered below. See also the RENAME statement above.

SET DEFAULT

set column default clause

<set column default clause> ::= SET <default clause>

Set the default clause for a column. This can be used if the column is not defined as GENERATED.

DROP DEFAULT

drop column default clause

<drop column default clause> ::= DROP DEFAULT

Drop the default clause from a column.

SET DATA TYPE

alter column data type clause

<alter column data type clause> ::= SET DATA TYPE <data type>

Change the declared type of a column. The latest SQL Standard allows only changes to type properties such as maximum length, precision, or scale, and only changes that cause the property to enlarge. HyperSQL allows changing the type if all the existing values can be cast into the new type without string truncation or loss of significant digits.

alter column add identity generator or sequence

alter column add identity generator or sequence

<add column identity generator> ::= <identity column specification>

<add column sequence generator> ::= <identity column sequence specification>

Adds an identity specification or a sequence to the column. The type of the column must be an integral type and the existing values must not include nulls. This option is specific to HyperSQL

 ALTER TABLE mytable ALTER COLUMN id GENERATED ALWAYS AS IDENTITY (START WITH 20000)

 ALTER TABLE mytable ALTER COLUMN id GENERATED BY DEFAULT AS SEQUENCE seq

alter column identity generator

alter identity column specification

<alter identity column specification> ::= <alter identity column option>...

<alter identity column option> ::= <alter sequence generator restart option> | SET <basic sequence generator option>

Change the properties of an identity column. This command is similar to the commands used for changing the properties of named SEQUENCE objects discussed earlier and can use the same options.

 ALTER TABLE mytable ALTER COLUMN id RESTART WITH 1000
 ALTER TABLE mytable ALTER COLUMN id SET INCREMENT BY 5

DROP GENERATED

drop column identity generator

<drop column identity specification> ::= DROP GENERATED

Removes the identity generator from a column. After executing this statement, the column values are no longer generated automatically. This option is specific to HyperSQL

  ALTER TABLE mytable ALTER COLUMN id DROP GENERATED

SET [ NOT ] NULL

alter column nullability

<alter column nullability> ::= SET [ NOT ] NULL

Adds or removes a NOT NULL constraint from a column. This option is specific to HyperSQL

CREATE VIEW

view definition

<view definition> ::= CREATE VIEW [ IF NOT EXISTS ] <table name> <view specification> AS <query expression> [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

<view specification> ::= [ <left paren> <view column list> <right paren> ]

<view column list> ::= <column name list>

Define a view. The <query expression> is a SELECT or similar statement. The <view column list> is the list of unique names for the columns of the view. The number of columns in the <view column list> must match the number of columns returned by the <query expression>. If <view column list> is not specified, then the columns of the <query expression> should have unique names and are used as the names of the view column.

Some views are updatable. As covered elsewhere, an updatable view is based on a single table or updatable view. For updatable views, the optional CHECK OPTION clause can be specified. If this option is specified, then if a row of the view is updated or a new row is inserted into the view, then it should contain such values that the row would be included in the view after the change. If WITH CASCADED CHECK OPTION is specified, then if the <query expression> of the view references another view, then the search condition of the underlying view should also be satisfied by the update or insert operation.

DROP VIEW

drop view statement

<drop view statement> ::= DROP VIEW [ IF EXISTS ] <table name> [ IF EXISTS ] <drop behavior>

Destroy a view. The <drop behavior> is similar to dropping a table.

ALTER VIEW

alter view statement

<alter view statement> ::= ALTER VIEW <table name> <view specification> AS <query expression> [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

Alter a view. The statement is otherwise identical to CREATE VIEW. The new definition replaces the old. If there are database objects such as routines or views that reference the view, then these objects are recompiled with the new view definition. If the new definition is not compatible, the statement fails.

CREATE DOMAIN

domain definition

<domain definition> ::= CREATE DOMAIN <domain name> [ AS ] <predefined type> [ <default clause> ] [ <domain constraint>... ] [ <collate clause> ]

<domain constraint> ::= [ <constraint name definition> ] <check constraint definition> [ <constraint characteristics> ]

Define a domain. Although a DOMAIN is not strictly a type in the SQL Standard, it can be informally considered as a type. A DOMAIN is based on a <predefined type>, which is a base type defined by the Standard. It can have a <default clause>, similar to a column default clause. It can also have one or more CHECK constraints which limit the values that can be assigned to a column that has the DOMAIN as its type. The keyword VALUE is used in the constraint definition to refer to the value of the column.

If a column uses a domain that contains a <default clause>, it can have a column default clause as well, which overrides the default defined by the domain. In a table that contains a column based on a domain, the CHECK constraints in table definition apply in addition to the CHECK constraints of the domain.

 CREATE DOMAIN valid_string AS VARCHAR(20) DEFAULT 'NO VALUE' CHECK (VALUE IS NOT NULL AND CHARACTER_LENGTH(VALUE) > 2) 

ALTER DOMAIN

alter domain statement

<alter domain statement> ::= ALTER DOMAIN <domain name> <alter domain action>

<alter domain action> ::= <set domain default clause> | <drop domain default clause> | <add domain constraint definition> | <drop domain constraint definition>

Change a domain and its definition.

SET DEFAULT

set domain default clause

<set domain default clause> ::= SET <default clause>

Set the default value in a domain. This is allowed if the domain is already used in a table definition.

DROP DEFAULT

drop domain default clause

<drop domain default clause> ::= DROP DEFAULT

Remove the default clause of a domain. This is allowed if the domain is already used in a table definition. If a column uses the domain as its type, the domain default is removed. If there is no existing column default clause, the default clause of the domain becomes the column default clause.

ADD CONSTRAINT

add domain constraint definition

<add domain constraint definition> ::= ADD <domain constraint>

Add a constraint to a domain. This is allowed if the domain is already used in a table definition and the table data satisfies the constraint.

DROP CONSTRAINT

drop domain constraint definition

<drop domain constraint definition> ::= DROP CONSTRAINT <constraint name>

Remove a constraint on a domain. This is allowed if the domain is already used in a table definition. The constraint no longer applies to a column that uses the domain as its type.

DROP DOMAIN

drop domain statement

<drop domain statement> ::= DROP DOMAIN <domain name> <drop behavior>

Destroy a domain. If <drop behavior> is not CASCADE, an exception is raised if the domain is already used in any database object. When CASCADE is specified, it works differently from most other cascading operations. If a table features a column that has specified DOMAIN, the column survives and inherits the base data type of the domain. The default clause and the check constraint of the DOMAIN no longer apply to the column (this behaviour is different from the SQL Standard).

CREATE TRIGGER

trigger definition

<trigger definition> ::= CREATE TRIGGER <trigger name> <trigger action time> <trigger event> ON <table name> [ REFERENCING <transition table or variable list> ] <triggered action>

<trigger action time> ::= BEFORE | AFTER | INSTEAD OF

<trigger event> ::= INSERT | DELETE | UPDATE [ OF <trigger column list> ]

<trigger column list> ::= <column name list>

<triggered action> ::= [ FOR EACH { ROW | STATEMENT } ] [ <triggered when clause> ] <triggered SQL statement>

<triggered when clause> ::= WHEN <left paren> <search condition> <right paren>

<triggered SQL statement> ::= <SQL procedure statement> | BEGIN ATOMIC { <SQL procedure statement> <semicolon> }... END | [QUEUE <integer literal>] [NOWAIT] CALL <HSQLDB trigger class FQN>

<transition table or variable list> ::= <transition table or variable>...

<transition table or variable> ::= OLD [ ROW ] [ AS ] <old transition variable name> | NEW [ ROW ] [ AS ] <new transition variable name> | OLD TABLE [ AS ] <old transition table name> | NEW TABLE [ AS ] <new transition table name>

<old transition table name> ::= <transition table name>

<new transition table name> ::= <transition table name>

<transition table name> ::= <identifier>

<old transition variable name> ::= <correlation name>

<new transition variable name> ::= <correlation name>

Trigger definition is a relatively complex statement. The combination of <trigger action time> and <trigger event> determines the type of the trigger. Examples include BEFORE DELETE, AFTER UPDATE, INSTEAD OF INSERT. If the optional [ OF <trigger column list> ] is specified for an UPDATE trigger, then the trigger is activated only if one of the columns that is in the <trigger column list> is specified in the UPDATE statement that activates the trigger.

If a trigger is FOR EACH ROW, which is the default option, then the trigger is activated for each row of the table that is affected by the execution of an SQL statement. Otherwise, it is activated once only per statement execution. In the first case, there is a before and after state for each row. For UPDATE triggers, both before and after states exist, representing the row before the update, and after the update. For DELETE, triggers, there is only a before state. For INSERT triggers, there is only an after state. If a trigger is FOR EACH STATEMENT, then a transient table is created containing all the rows for the before state and another transient table is created for the after state.

The [ REFERENCING <transition table or variable> ] is used to give a name to the before and after data row or table. This name can be referenced in the <SQL procedure statement> to access the data.

The optional <triggered when clause> is a search condition, similar to the search condition of a DELETE or UPDATE statement. If the search condition is not TRUE for a row, then the trigger is not activated for that row.

The <SQL procedure statement> is limited to INSERT, DELETE, UPDATE and MERGE statements.

The <HSQLDB trigger class FQN> is a delimited identifier that contains the fully qualified name of a Java class that implements the org.hsqldb.Trigger interface.

HyperSQL does not yet allow the use of OLD TABLE or NEW TABLE in statement level trigger definitions.

DROP TRIGGER

drop trigger statement

<drop trigger statement> ::= DROP TRIGGER <trigger name>

Destroy a trigger.

schema routine

SQL-invoked routine

<SQL-invoked routine> ::= <schema routine>

<schema routine> ::= <schema procedure> | <schema function>

<schema procedure> ::= CREATE <SQL-invoked procedure>

<schema function> ::= CREATE <SQL-invoked function>

<SQL-invoked procedure> ::= PROCEDURE <schema qualified routine name> <SQL parameter declaration list> <routine characteristics> <routine body>

<SQL-invoked function> ::= { <function specification> | <method specification designator> } <routine body>

<SQL parameter declaration list> ::= <left paren> [ <SQL parameter declaration> [ { <comma> <SQL parameter declaration> }... ] ] <right paren>

<SQL parameter declaration> ::= [ <parameter mode> ] [ <SQL parameter name> ] <parameter type> [ RESULT ]

<parameter mode> ::= IN | OUT | INOUT

<parameter type> ::= <data type>

<function specification> ::= FUNCTION <schema qualified routine name> <SQL parameter declaration list> <returns clause> <routine characteristics> [ <dispatch clause> ]

<method specification designator> ::= SPECIFIC METHOD <specific method name> | [ INSTANCE | STATIC | CONSTRUCTOR ] METHOD <method name> <SQL parameter declaration list> [ <returns clause> ] FOR <schema-resolved user-defined type name>

<routine characteristics> ::= [ <routine characteristic>... ]

<routine characteristic> ::= <language clause> | <parameter style clause> | SPECIFIC <specific name> | <deterministic characteristic> | <SQL-data access indication> | <null-call clause> | <returned result sets characteristic> | <savepoint level indication>

<savepoint level indication> ::= NEW SAVEPOINT LEVEL | OLD SAVEPOINT LEVEL

<returned result sets characteristic> ::= DYNAMIC RESULT SETS <maximum returned result sets>

<parameter style clause> ::= PARAMETER STYLE <parameter style>

<dispatch clause> ::= STATIC DISPATCH

<returns clause> ::= RETURNS <returns type>

<returns type> ::= <returns data type> [ <result cast> ] | <returns table type>

<returns table type> ::= TABLE <table function column list>

<table function column list> ::= <left paren> <table function column list element> [ { <comma> <table function column list element> }... ] <right paren>

<table function column list element> ::= <column name> <data type>

<result cast> ::= CAST FROM <result cast from type>

<result cast from type> ::= <data type> [ <locator indication> ]

<returns data type> ::= <data type> [ <locator indication> ]

<routine body> ::= <SQL routine spec> | <external body reference>

<SQL routine spec> ::= [ <rights clause> ] <SQL routine body>

<rights clause> ::= SQL SECURITY INVOKER | SQL SECURITY DEFINER

<SQL routine body> ::= <SQL procedure statement>

<external body reference> ::= EXTERNAL [ NAME <external routine name> ] [ <parameter style clause> ]

<parameter style> ::= SQL | GENERAL

<deterministic characteristic> ::= DETERMINISTIC | NOT DETERMINISTIC

<SQL-data access indication> ::= NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA

<null-call clause> ::= RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

<maximum returned result sets> ::= <unsigned integer>

Define an SQL-invoked routine. A few of the options are not used by HyperSQL and have default behaviours. See the SQL-Invoked Routines chapter for more details of various options and examples.

ALTER routine

alter routine statement

<alter routine statement> ::= ALTER <specific routine designator> [ <alter routine characteristics> ] [ RESTRICT ] <routine body>

<alter routine characteristics> ::= <alter routine characteristic>...

<alter routine characteristic> ::= <language clause> | <parameter style clause> | <SQL-data access indication> | <null-call clause> | <returned result sets characteristic>

<alter routine body> ::= <SQL routine body>

Alter the characteristic and the body of an SQL-invoked routine. If RESTRICT is specified and the routine is already used in a different routine or view definition, an exception is raised. Altering the routine changes the implementation without changing the parameters. Defining recursive SQL/PSM SQL functions is only possible by altering a non-recursive routine body. An example is given in the SQL-Invoked Routines chapter.

An example is given below for a function defined as a Java method, then redefined as an SQL function.

 CREATE FUNCTION zero_pad(x BIGINT, digits INT, maxsize INT)
  RETURNS CHAR VARYING(100)
  SPECIFIC zero_pad_01
  NO SQL DETERMINISTIC
  LANGUAGE JAVA
  EXTERNAL NAME 'CLASSPATH:org.hsqldb.lib.StringUtil.toZeroPaddedString';

 ALTER SPECIFIC ROUTINE zero_pad_01
  LANGUAGE SQL
  BEGIN ATOMIC
  DECLARE str VARCHAR(128);
  SET str = CAST(x AS VARCHAR(128));
  SET str = SUBSTRING('0000000000000' FROM 1 FOR digits - CHAR_LENGTH(str)) + str;
  return str;
  END

DROP

drop routine statement

<drop routine statement> ::= DROP <specific routine designator> <drop behavior>

Destroy an SQL-invoked routine.

CREATE SEQUENCE

sequence generator definition

<sequence generator definition> ::= CREATE SEQUENCE [ IF NOT EXISTS ] <sequence generator name> [ <sequence generator options> ]

<sequence generator options> ::= <sequence generator option> ...

<sequence generator option> ::= <sequence generator data type option> | <common sequence generator options>

<common sequence generator options> ::= <common sequence generator option> ...

<common sequence generator option> ::= <sequence generator start with option> | <basic sequence generator option>

<basic sequence generator option> ::= <sequence generator increment by option> | <sequence generator maxvalue option> | <sequence generator minvalue option> | <sequence generator cycle option>

<sequence generator data type option> ::= AS <data type>

<sequence generator start with option> ::= START WITH <sequence generator start value>

<sequence generator start value> ::= <signed numeric literal>

<sequence generator increment by option> ::= INCREMENT BY <sequence generator increment>

<sequence generator increment> ::= <signed numeric literal>

<sequence generator maxvalue option> ::= MAXVALUE <sequence generator max value> | NO MAXVALUE

<sequence generator max value> ::= <signed numeric literal>

<sequence generator minvalue option> ::= MINVALUE <sequence generator min value> | NO MINVALUE

<sequence generator min value> ::= <signed numeric literal>

<sequence generator cycle option> ::= CYCLE | NO CYCLE

Define a named sequence generator. A SEQUENCE object generates a sequence of integers according to the specified rules. The simple definition without the options defines a sequence of numbers in INTEGER type starting at 1 and incrementing by 1. By default, the CYCLE property is set and the minimum and maximum limits are the minimum and maximum limits of the type of returned values. There are self-explanatory options for changing various properties of the sequence. The MAXVALUE and MINVALUE specify the upper and lower limits. If CYCLE is specified, after the sequence returns the highest or lowest value in range, the next value will respectively be the lowest or highest value in range. If NO CYCLE is specified, the use of the sequence generator results in an error once the limit has been reached.

The integer types: SMALLINT, INTEGER, BIGINT, DECIMAL and NUMERIC can be used as the type of the sequence. DECIMAL and NUMERIC types must have a scale of 0 and a precision not exceeding 18.

ALTER SEQUENCE

alter sequence generator statement

<alter sequence generator statement> ::= ALTER SEQUENCE <sequence generator name> <alter sequence generator options>

<alter sequence generator options> ::= <alter sequence generator option>...

<alter sequence generator option> ::= <alter sequence generator restart option> | <basic sequence generator option>

<alter sequence generator restart option> ::= RESTART [ WITH <sequence generator restart value> ]

<sequence generator restart value> ::= <signed numeric literal>

Change the definition of a named sequence generator. The same options that are used in the definition of the SEQUENCE can be used to alter it. The exception is the option for the start value which is RESTART WITH for the ALTER SEQUENCE statement.

If RESTART is used by itself (without a value), then the current value of the sequence is reset to the start value. Otherwise, the current value is reset to the given restart value.

DROP SEQUENCE

drop sequence generator statement

<drop sequence generator statement> ::= DROP SEQUENCE [ IF EXISTS ] <sequence generator name> [ IF EXISTS ] <drop behavior>

Destroy an external sequence generator. If the <drop behavior> is CASCADE, then all objects that reference the sequence are dropped. These objects can be VIEW, ROUTINE or TRIGGER objects.

SQL procedure statement

SQL procedure statement

The definition of CREATE TRIGGER and CREATE PROCEDURE statements refers to <SQL procedure statement>. The definition of this element is given below. However, only a subset of these statements is allowed in trigger or routine definition.

<SQL procedure statement> ::= <SQL executable statement>

<SQL executable statement> ::= <SQL schema statement> | <SQL data statement> | <SQL control statement> | <SQL transaction statement> | <SQL connection statement> | <SQL session statement> | <SQL diagnostics statement> | <SQL dynamic statement>

<SQL schema statement> ::= <SQL schema definition statement> | <SQL schema manipulation statement>

<SQL schema definition statement> ::= <schema definition> | <table definition> | <view definition> | <SQL-invoked routine> | <grant statement> | <role definition> | <domain definition> | <character set definition> | <collation definition> | <transliteration definition> | <assertion definition> | <trigger definition> | <user-defined type definition> | <user-defined cast definition> | <user-defined ordering definition> | <transform definition> | <sequence generator definition>

<SQL schema manipulation statement> ::= <drop schema statement> | <alter table statement> | <drop table statement> | <drop view statement> | <alter routine statement> | <drop routine statement> | <drop user-defined cast statement> | <revoke statement> | <drop role statement> | <alter domain statement> | <drop domain statement> | <drop character set statement> | <drop collation statement> | <drop transliteration statement> | <drop assertion statement> | <drop trigger statement> | <alter type statement> | <drop data type statement> | <alter sequence generator statement> | <drop sequence generator statement>

CREATE SYNONYM

create synonym statement

<create synonym statement> ::= CREATE SYNONYM <synonym name> FOR <target object name>

Creates a synonym for the <target object name>. The synonym is defined in the current schema, unless the name is qualified with a different schema name. The target object name can be a schema object in the current schema or in another schema. The synonym can be used only without the schema name.

DROP SYNONYM

drop synonym statement

<drop synonym statement> ::= DROP SYNONYM <synonym name>

Drops the synonym. The <synonym name> can be the simple name of the synonym or qualified with the schema name.

CREATE INDEX

create index statement

<create index statement> ::= CREATE INDEX [ IF NOT EXISTS ] <index name> ON <table name> <left paren> {<column name> [ASC | DESC]}, ... <right paren>

Creates an index on a group of columns of a table. The optional [ASC | DESC] specifies if the column is indexed in the ascending or descending order, but has no effect on how the index is created (it is allowed for compatibility with other database engines). HyperSQL can use all indexes in ascending or descending order as needed. Indexes should not duplicate the columns of PRIMARY KEY, UNIQUE or FOREIGN key constraints as each of these constraints creates an index automatically.

DROP INDEX

drop index statement

<drop index statement> ::= DROP INDEX [ IF EXISTS ] <index name> [ IF EXISTS ]

Destroy an index.

ALTER INDEX

change the columns of an index

<alter index statement> ::= ALTER INDEX <index name> <left paren> {<column name>} , ... <right paren>

Redefine an index with a new column list. This statement is more efficient than dropping an existing index and creating a new one.

ALTER CONSTRAINT

alter foreign key constraint definition

<alter constraint definition> ::= ALTER CONSTRAINT <constraint name> INDEX ADD <left paren> <extra column list> <right paren>

Add extra columns to the index of a FOREIGN KEY constraint. Only the index is extended over the extra columns and the FOREIGN KEY does not change. If the statement is used again, the previous extra columns of the index are replaced with the new extra columns. The FOREIGN KEY must have a user-defined name.

CREATE TYPE

user-defined type definition

<user-defined type definition> ::= CREATE TYPE <user-defined type body>

<user-defined type body> ::= <schema-resolved user-defined type name> [ AS <representation> ]

<representation> ::= <predefined type>

Define a user-defined type. Currently only simple distinct types can be defined without further attributes.

CREATE CAST

user-defined cast definition

<user-defined cast definition> ::= CREATE CAST <left paren> <source data type> AS <target data type> <right paren> WITH <cast function> [ AS ASSIGNMENT ]

<cast function> ::= <specific routine designator>

<source data type> ::= <data type>

<target data type> ::= <data type>

Define a user-defined cast. This feature may be supported in a future version of HyperSQL.

DROP CAST

drop user-defined cast statement

<drop user-defined cast statement> ::= DROP CAST <left paren> <source data type> AS <target data type> <right paren> <drop behavior>

Destroy a user-defined cast. This feature may be supported in a future version of HyperSQL.

CREATE CHARACTER SET

character set definition

<character set definition> ::= CREATE CHARACTER SET <character set name> [ AS ] <character set source> [ <collate clause> ]

<character set source> ::= GET <character set specification>

Define a character set. A new CHARACTER SET is based on an existing CHARACTER SET. The optional <collate clause> specifies the collation to be used, otherwise the collation is inherited from the default collation for the source CHARACTER SET. Currently this statement has no effect, as the character set used by HyperSQL is Unicode and there is no need for subset character sets.

DROP CHARACTER SET

drop character set statement

<drop character set statement> ::= DROP CHARACTER SET <character set name>

Destroy a character set. If the character set name is referenced in any database object, the command fails. Note that CASCADE or RESTRICT cannot be specified for this command.

CREATE COLLATION

collation definition

<collation definition> ::= CREATE COLLATION <collation name> FOR <character set specification> FROM <existing collation name> [ <pad characteristic> ]

<existing collation name> ::= <collation name>

<pad characteristic> ::= NO PAD | PAD SPACE

Define a collation. A new collation is based on an existing COLLATION and applies to an existing CHARACTER SET. The <character set specification> is always SQL_TEXT. The <existing collation name> is either SQL_TEXT or one of the language collations supported by HyperSQL. The <pad characteristic> specifies whether strings are padded with spaces for comparison.

This statement is typically used when a collation is required that does not pad spaces before comparing two strings. For example, CREATE COLLATION FRENCH_NOPAD FOR INFORMATION_SCHEMA.SQL_TEXT FROM "French" NO PAD, results in a French collation without padding. This collation can be used for sorting or for individual columns of tables.

DROP COLLATION

drop collation statement

<drop collation statement> ::= DROP COLLATION <collation name> <drop behavior>

Destroy a collation. If the <drop behavior> is CASCADE, then all references to the collation revert to the default collation that would be in force if the dropped collation was not specified.

CREATE TRANSLATION

transliteration definition

<transliteration definition> ::= CREATE TRANSLATION <transliteration name> FOR <source character set specification> TO <target character set specification> FROM <transliteration source>

<source character set specification> ::= <character set specification>

<target character set specification> ::= <character set specification>

<transliteration source> ::= <existing transliteration name> | <transliteration routine>

<existing transliteration name> ::= <transliteration name>

<transliteration routine> ::= <specific routine designator>

Define a character transliteration. This feature may be supported in a future version of HyperSQL.

DROP TRANSLATION

drop transliteration statement

<drop transliteration statement> ::= DROP TRANSLATION <transliteration name>

Destroy a character transliteration. This feature may be supported in a future version of HyperSQL.

CREATE ASSERTION

assertion definition

<assertion definition> ::= CREATE ASSERTION <constraint name> CHECK <left paren> <search condition> <right paren> [ <constraint characteristics> ]

Specify an integrity constraint. This feature may be supported in a future version of HyperSQL.

DROP ASSERTION

drop assertion statement

<drop assertion statement> ::= DROP ASSERTION <constraint name> [ <drop behavior> ]

Destroy an assertion. This feature may be supported in a future version of HyperSQL.

Each database object may reference other database objects. For example, a VIEW references tables in its SELECT statement. An SQL FUNCTION or PROCEDURE typically references tables, views, other routines, and sequences. There are views in the INFORMATION_SCHEMA with the word "USAGE" in the name. Each of these views lists references to objects of a particular type from a particular type, for example references to tables from routines.

From version 2.5.0, a new SQL statement lists all the database objects that use (reference) a particular database object. Alternatively, the statement lists all the database object that are used (referenced) by a particular database object.

EXPLAIN REFERENCES

explain references

<explain references statement> ::= EXPLAIN REFERENCES { TO | FROM } { TABLE | VIEW | DOMAIN | TYPE | SPACIFIC ROUTINE | SEQUENCE > <object name>

For example, EXPLAIN REFERENCES TO TABLE T1.

The SQL Standard defines a number of character sets and domains in the INFORMATION SCHEMA.

These domains are used in the INFORMATION SCHEMA views:

CARDINAL_NUMBER, YES_OR_NO, CHARACTER_DATA, SQL_IDENTIFIER, TIME_STAMP

All available collations are in the INFORMATION SCHEMA.

HyperSQL supports a vast range of views in the INFORMATION_SCHEMA. These include views specified by the SQL Standard, SQL/Schemata part, plus views that are specific to HyperSQL and are used for JDBC DatabaseMetaData queries, which are based on SQL/CLI part, or other information that is not covered by the SQL Standard. The names of views that are not part of SQL/Schemata start with SYSTEM_.

The views cover different types of information. These are covered in the next sections.

Users with the special ADMIN role can see the full information on all database objects. Ordinary, non-admin users can see information on the objects for which they have some privileges.

The rows returned to a non-admin user exclude objects on which the user has no privilege. The extent of the information in visible rows varies with the user's privilege. For example, the owner of a VIEW can see the text of the view query, but a user of the view cannot see this text. When a user cannot see the contents of some column, null is returned for that column.

The names of database objects are stored in hierarchical views. The top level view is INFORMATION_SCHEMA_CATALOG_NAME.

Below this level, there is a group of views that covers authorizations and roles, without referencing schema objects. These are AUTHORIZATIONS and ADMINSTRABLE_ROLE_AUTHORIZATIONS.

Also below the top level, there is the SCHEMATA view, which lists the schemas in the catalog.

The views that refer to top-level schema objects are divided by object type. These includes ASSERTIONS, CHARACTER_SETS, COLLATIONS, DOMAINS, ROUTINES, SEQUENCES, TABLES, USER_DEFINED_TYPES and VIEWS.

There are views that refer to objects that are dependent on the top-level schema objects. These include COLUMNS and PARAMETERS, views for constraints, including CHECK_CONSTRAINTS, REFERENTIAL_CONSTRAINTS and TABLE_CONSTRAINTS, and finally the TRIGGERS view.

The usage of each type of top-level object by another is covered by several views. For example, TRIGGER_SEQUENCE_USAGE or ROUTINE_TABLE_USAGE.

Several other views list the individual privileges owned or granted to each AUTHORIZATION. For example, ROLE_ROUTINE_GRANTS or TABLE_PRIVILEGES.

The INFORMATION_SCHEMA contains comprehensive information on the data types of each schema object and its elements. For example, the ROUTINES view includes the return data type for each FUNCTION definition. The columns for this information contain nulls for rows that cover PROCEDURE definitions.

The COLUMNS, PARAMETERS and SEQUENCES views contain the type information in columns with similar names.

The type information for ARRAY types is returned in the ELEMENT_TYPES view. When a row of the COLUMNS or other view indicates that the type of the object is an ARRAY type, then there is a corresponding entry for this row in the ELEMENT_TYPES view. The following columns in the ELEMENTS_TYPES view identify the database object whose data type is being described: OBJECT_CATALOG, OBJECT_SCHEMA, OBJECT_NAME, OBJECT_TYPE, COLLECTION_TYPE_IDENTIFIER. The last column's counterpart in the COLUMNS view is named differently as DTD_IDENTIFIER. So in order to determine the array element type of a column, an equi-join between the COLUMNS and ELEMENT_TYPES tables on the six listed columns in the ELEMENT_TYPES view and their counterparts in the COLUMNS view is needed.

A group of views, including SQL_IMPLEMENTATION_INFO, SQL_FEATURES, SQL_SIZING and others cover the capabilities of HyperSQL in detail. These views hold static data and can be explored even when the database is empty.

There are some HyperSQL custom views cover the current state of operation of the database. These include SYSTEM_CACHEINFO, SYSTEM_SESSIONINFO and SYSTEM_SESSIONS views.

The following views are defined by the SQL Standard and supported by HyperSQL. The columns and contents exactly match the Standard requirements.

ADMINISTRABLE_ROLE_AUTHORIZATIONS

Information on ROLE authorizations, all granted by the admin role.

APPLICABLE_ROLES

Information on ROLE authorizations for the current user

ASSERTIONS

Empty view as ASSERTION objects are not yet supported.

AUTHORIZATIONS

Top level information on USER and ROLE objects in the database

CHARACTER_SETS

List of supported CHARACTER SET objects

CHECK_CONSTRAINTS

Additional information specific to each CHECK constraint, including the search condition

CHECK_CONSTRAINT_ROUTINE_USAGE

Information on FUNCTION objects referenced in CHECK constraints search conditions

COLLATIONS

Information on collations supported by the database.

COLUMNS

Information on COLUMN objects in TABLE and VIEW definitions

COLUMN_COLUMN_USAGE

Information on references to COLUMN objects from other, GENERATED, COLUMN objects

COLUMN_DOMAIN_USAGE

Information on DOMAIN objects used in type definition of COLUMN objects

COLUMN_PRIVILEGES

Information on privileges on each COLUMN object, granted to different ROLE and USER authorizations

COLUMN_UDT_USAGE

Information on distinct TYPE objects used in type definition of COLUMN objects

CONSTRAINT_COLUMN_USAGE

Information on COLUMN objects referenced by CONSTRAINT objects in the database

CONSTRAINT_PERIOD_USAGE

Information on application PERIOD objects referenced by CONSTRAINT objects in the database

CONSTRAINT_TABLE_USAGE

Information on TABLE and VIEW objects referenced by CONSTRAINT objects in the database

DATA_TYPE_PRIVILEGES

Information on top level schema objects of various kinds that reference TYPE objects

DOMAINS

Top level information on DOMAIN objects in the database.

DOMAIN_CONSTRAINTS

Information on CONSTRAINT definitions used for DOMAIN objects

ELEMENT_TYPES

Information on the type of elements of ARRAY used in database columns or routine parameters and return values

ENABLED_ROLES

Information on ROLE privileges enabled for the current session

INFORMATION_SCHEMA_CATALOG_NAME

Information on the single CATALOG object of the database

KEY_COLUMN_USAGE

Information on COLUMN objects of tables that are used by PRIMARY KEY, UNIQUE and FOREIGN KEY constraints

KEY_PERIOD_USAGE

Information on application PERIOD objects that are used by PRIMARY KEY, UNIQUE and FOREIGN KEY constraints

PARAMETERS

Information on parameters of each FUNCTION or PROCEDURE

PERIODS

Information on PERIOD objects defined in tables

REFERENTIAL_CONSTRAINTS

Additional information on FOREIGN KEY constraints, including triggered action and name of UNIQUE constraint they refer to

ROLE_AUTHORIZATION_DESCRIPTORS

ROLE_COLUMN_GRANTS

Information on privileges on COLUMN objects granted to or by the current session roles

ROLE_ROUTINE_GRANTS

Information on privileges on FUNCTION and PROCEDURE objects granted to or by the current session roles

ROLE_TABLE_GRANTS

Information on privileges on TABLE and VIEW objects granted to or by the current session roles

ROLE_UDT_GRANTS

Information on privileges on TYPE objects granted to or by the current session roles

ROLE_USAGE_GRANTS

Information on privileges on USAGE privileges granted to or by the current session roles

ROUTINE_COLUMN_USAGE

Information on COLUMN objects of different tables that are referenced in FUNCTION and PROCEDURE definitions

ROUTINE_JAR_USAGE

Information on JAR usage by Java language FUNCTION and PROCEDURE objects.

ROUTINE_PERIOD_USAGE

Information on table PERIOD objects referenced in FUNCTION and PROCEDURE objects.

ROUTINE_PRIVILEGES

Information on EXECUTE privileges granted on PROCEDURE and FUNCTION objects

ROUTINE_ROUTINE_USAGE

Information on PROCEDURE and FUNCTION objects that are referenced in FUNCTION and PROCEDURE definitions

ROUTINE_SEQUENCE_USAGE

Information on SEQUENCE objects that are referenced in FUNCTION and PROCEDURE definitions

ROUTINE_TABLE_USAGE

Information on TABLE and VIEW objects that are referenced in FUNCTION and PROCEDURE definitions

ROUTINES

Top level information on all PROCEDURE and FUNCTION objects in the database

SCHEMATA

Information on all the SCHEMA objects in the database

SEQUENCES

Information on SEQUENCE objects

SQL_FEATURES

List of all SQL:2023 standard core and optional features, including information on whether they are supported or not supported by HyperSQL

SQL_IMPLEMENTATION_INFO

Information on name, capabilities and defaults of the database engine software.

SQL_PACKAGES

List of SQL:2011 Standard packages, including information on whether they are supported or not supported by HyperSQL

SQL_PARTS

List of the SQL:2011 Standard parts, including information on whether they are supported or not supported by HyperSQL

SQL_SIZING

List of the SQL:2011 Standard maximum supported sizes for different features as supported by HyperSQL

SQL_SIZING_PROFILES

TABLES

Information on all TABLE and VIEW object, including the INFORMATION_SCHEMA views themselves

TABLE_CONSTRAINTS

Information on all table level constraints, including PRIMARY KEY, UNIQUE, FOREIGN KEY and CHECK constraints

TABLE_PRIVILEGES

Information on privileges on TABLE and VIEW objects owned or given to the current user

TRANSLATIONS

TRIGGERED_UPDATE_COLUMNS

Information on columns that have been used in TRIGGER definitions in the ON UPDATE clause

TRIGGERS

Top level information on the TRIGGER definitions in the databases

TRIGGER_COLUMN_USAGE

Information on COLUMN objects that have been referenced in the body of TRIGGER definitions

TRIGGER_PERIOD_USAGE

Information on PERIOD objects that have been referenced in the body of TRIGGER definitions

TRIGGER_ROUTINE_USAGE

Information on FUNCTION and PROCEDURE objects that have been used in TRIGGER definitions

TRIGGER_SEQUENCE_USAGE

Information on SEQUENCE objects that been referenced in TRIGGER definitions

TRIGGER_TABLE_USAGE

Information on TABLE and VIEW objects that have been referenced in TRIGGER definitions

USAGE_PRIVILEGES

Information on USAGE privileges granted to or owned by the current user

USER_DEFINED_TYPES

Top level information on TYPE objects in the database

VIEWS

Top Level information on VIEW objects in the database

VIEW_COLUMN_USAGE

Information on COLUMN objects referenced in the query expressions of the VIEW objects

VIEW_PERIOD_USAGE

Information on PERIOD objects referenced in the query expressions of the VIEW objects

VIEW_ROUTINE_USAGE

Information on FUNCTION and PROCEDURE objects that have been used in the query expressions of the VIEW objects

VIEW_TABLE_USAGE

Information on TABLE and VIEW objects that have been referenced in the query expressions of the VIEW objects

TIMEZONE

TIMEZONE()

Returns the current time zone displacement for the session. This value is the same as SESSION_TIMEZONE if the user has not changed the TIME ZONE of the session. Returns an INTERVAL HOUR TO MINUTE value. (HyperSQL)

SESSION_TIMEZONE

SESSION_TIMEZONE()

Returns the initial (default) time zone displacement for the current session. Returns an INTERVAL HOUR TO MINUTE value. (HyperSQL)

SESSIONTIMEZONE

SESSIONTIMEZONE()

Returns the same value as SESSION_TIMEZONE. (HyperSQL)

DATABASE_TIMEZONE

DATABASE_TIMEZONE()

Returns the time zone displacement for the database engine. This is based on where the database server process is located. Returns an INTERVAL HOUR TO MINUTE value. (HyperSQL)

DBTIMEZONE

DBTIMEZONE()

Similar to DATABASE_TIMEZONE. Returns a string. Works in ORA compatibility mode only.(HyperSQL)

CURRENT_DATE

CURRENT_DATE

CURRENT_TIME

CURRENT_TIME [ ( <time precision> ) ]

LOCALTIME

LOCALTIME [ ( <time precision> ) ]

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP [ ( <timestamp precision> ) ]

LOCALTIMESTAMP

LOCALTIMESTAMP [ ( <timestamp precision> ) ]

These datetime functions return the datetime value representing the moment the function is called. CURRENT_DATE returns a value of DATE type. CURRENT_TIME returns a value of TIME WITH TIME ZONE type with the time zone of the SQL session. LOCALTIME returns a value of TIME type. CURRENT_TIMESTAMP returns a value of TIMESTAMP WITH TIME ZONE type with the time zone of the SQL session. LOCALTIMESTAMP returns a value of TIMESTAMP type. If the optional [ ( <time precision> ) ] or [ ( <timestamp precision> ) ] is used, then the returned value has the specified fraction of the second precision. When the functions are used multiple times in a single SQL statement, the returned values represent the same point of time.

From version 2.7.0 the functions return values with microsecond precision. Previous versions returned values with millisecond precision. (SQL:2023)

NOW

NOW ()

This function is equivalent to LOCALTIMESTAMP. It can be used as a no-arg function as the parens are optional. (HyperSQL)

CURDATE

CURDATE ()

This function is equivalent to CURRENT_DATE. (JDBC)

CURTIME

CURTIME ()

This function is equivalent to LOCALTIME. (JDBC)

SYSDATE

SYSDATE

This no-arg function is similar to LOCALTIMESTAMP but it returns the timestamp of the HyperSQL server as opposed to the current session. The returned timestamp is without fraction of second. (HyperSQL)

SYSTIMESTAMP

SYSTIMESTAMP

This no-arg function is similar to CURRENT_TIMESTAMP but it returns the timestamp with time zone of the HyperSQL server as opposed to the current session. The function is enabled in ORA syntax mode only. (HyperSQL)

TODAY

TODAY

This no-arg function is equivalent to CURRENT_DATE. (HyperSQL)

DATENAME, DATEPART and EOMONTH

These functions are available in the MSS compatibility mode and perform the equivalent of EXTRACT function or the LAST_DAY function. (HyperSQL)

DAYNAME

DAYNAME ( <datetime value expr> )

This function is equivalent to EXTRACT ( DAY_NAME FROM ... ) Returns a string in the range of Sunday - Saturday. (JDBC)

DAYOFMONTH

DAYOFMONTH ( <datetime value expr> )

This function is equivalent to EXTRACT ( DAY_OF_MONTH FROM ... ) Returns an integer value in the range of 1-31. (JDBC)

DAYOFWEEK

DAYOFWEEK ( <datetime value expr> )

This function is equivalent to EXTRACT ( DAY_OF_WEEK FROM ... ) Returns an integer value in the range of 1-7. The first day of the week is Sunday. (JDBC)

DAYOFYEAR

DAYOFYEAR ( <datetime value expr> )

This function is equivalent to EXTRACT ( DAY_OF_YEAR FROM ... ) Returns an integer value in the range of 1-366. (JDBC)

DAYS

DAYS ( <datetime value expr> )

The <datetime value expr> is of DATE or TIMESTAMP type. This function returns the DAY number since the first day of the calendar. The first day is numbered 1. (HyperSQL)

HOUR

HOUR ( <datetime value expr> )

This function is equivalent to EXTRACT ( HOUR FROM ... ) Returns an integer value in the range of 0-23. (JDBC)

MINUTE

MINUTE ( <datetime value expr> )

This function is equivalent to EXTRACT ( MINUTE FROM ... ) Returns an integer value in the range of 0 - 59. (JDBC)

MONTH

MONTH ( <datetime value expr> )

This function is equivalent to EXTRACT ( MONTH FROM ... ) Returns an integer value in the range of 1-12. (JDBC)

MONTHNAME

MONTHNAME ( <datetime value expr> )

This function is equivalent to EXTRACT ( NAME_OF_MONTH FROM ... ) Returns a string in the range of January - December. (JDBC)

QUARTER

QUARTER ( <datetime value expr> )

This function is equivalent to EXTRACT ( QUARTER FROM ... ) Returns an integer in the range of 1 - 4. (JDBC)

SECOND

SECOND ( <datetime value expr> )

This function is equivalent to EXTRACT ( SECOND FROM ... ) Returns a decimal in the range of 0 - 60, with the same precision as the <datetime value expr>. (JDBC)

SECONDS_SINCE_MIDNIGHT

SECONDS_SINCE_MIDNIGHT ( <datetime value expr> )

This function is equivalent to EXTRACT ( SECONDS_SINCE_MIDNIGHT FROM ... ) Returns an integer in the range of 0 - 86399. (HyperSQL)

UNIX_MILLIS

UNIX_MILLIS ( [ <datetime value expression> ] )

This function returns a BIGINT value. With no parameter, it returns the number of milliseconds since 1970-01-01. With a DATE or TIMESTAMP parameter, it converts the argument into number of milliseconds since 1970-01-01. (HyperSQL)

UNIX_TIMESTAMP

UNIX_TIMESTAMP ( [ <datetime value expression> ] )

This function returns a BIGINT value. With no parameter, it returns the number of seconds since 1970-01-01. With a DATE or TIMESTAMP parameter, it converts the argument into number of seconds since 1970-01-01. See also the TIMESTAMP ( <num value expression> ) function to return a TIMESTAMP from a Unix timestamp. (HyperSQL)

WEEK

WEEK ( <datetime value expr> )

This function is equivalent to EXTRACT ( WEEK_OF_YEAR FROM ... ) Returns an integer in the range of 1 - 54. (JDBC)

YEAR

YEAR ( <datetime value expr> )

This function is equivalent to EXTRACT ( YEAR FROM ... ) Returns an integer in the range of 1 - 9999. (JDBC)

EXTRACT

EXTRACT ( <extract field> FROM <extract source> )

<extract field> ::= YEAR | MONTH | DAY | HOUR | MINUTE | DAY_OF_WEEK | WEEK_OF_YEAR | QUARTER | DAY_OF_YEAR | DAY_OF_MONTH |

TIMEZONE_HOUR | TIMEZONE_MINUTE | SECOND | SECONDS_SINCE_MIDNIGHT |

DAY_NAME | MONTH_NAME

<extract source> ::= <datetime value expr> | <interval value expr>

The EXTRACT function returns a field or element of the <extract source>. The <extract source> is a datetime or interval expression. The type of the return value is BIGINT for most of the <extract field> options. The exception is SECOND, where a DECIMAL value is returned which has the same precision as the datetime or interval expression. The field values DAY_NAME or MONTH_NAME result in a character string. When MONTH_NAME is specified, a string in the range January - December is returned. When DAY_NAME is specified, a string in the range Sunday -Saturday is returned.

If the <extract source> is FROM <datetime value expr>, different groups of <extract source> can be used depending on the data type of the expression. The TIMEZONE_HOUR | TIMEZONE_MINUTE options are valid only for TIME WITH TIMEZONE and TIMESTAMP WITH TIMEZONE data types. The HOUR | MINUTE | SECOND | SECONDS_MIDNIGHT options, are valid for TIME and TIMESTAMP types. The rest of the fields are valid for DATE and TIMESTAMP types.

If the <extract source> is FROM <interval value expr>, the <extract field> must be one of the fields of the INTERVAL type of the expressions. The YEAR | MONTH options may be valid for INTERVAL types based on months. The DAY | HOUR | MINUTE | SECOND | SECONDS_MIDNIGHT options may be valid for INTERVAL types based on seconds. For example, DAY | HOUR | MINUTE are the only valid fields for the INTERVAL DAY TO MINUTE data type. (SQL:2023 with HyperSQL extensions)

NEXT_DAY

NEXT_DAY ( <datetime value expr>, <character value expr>)

This function returns a TIMESTAMP for compatibility reasons. The return value is the next weekday named by the second argument that occurs after the first date. For example, next Wednesday is expressed as NEXT_DAY(CURRENT_DATE, 'WEDNESDAY'). (HyperSQL)

ADD_MONTHS

ADD_MONTHS ( <datetime value expr>, <numeric value expr>)

This function is similar but different to simple addition of a MONTH interval to a datetime value. The SQL Standard expression, <datetime value expr> + n MONTH, when used with the last day of a short month such as February, returns a date that has the same day of the month in the target month. The ADD_MONTHS function adjusts the target day to the last day of the target month. For all other days, the behaviour is the same. This function always returns a TIMESTAMP(0) value, regardless of the type of the argument. (HyperSQL)

The example below compares the output of the function and the expression.

 VALUES ADD_MONTHS ( DATE '2012-02-29' , 1), DATE '2012-02-29' + 1 MONTH

 C1                  C2         
 ------------------- ---------- 
 2012-03-31 00:00:00 2012-03-29 

LAST_DAY

LAST_DAY ( <datetime value expr> )

Returns the last day of the month for the given <datetime value expr>. The returned value preserves the year, month, hour, minute and second fields of the timestamp. The type of the result is always TIMESTAMP(0). (HyperSQL)

 VALUES LAST_DAY ( TIMESTAMP '2012-02-14 12:30:44')

 C1                  
 ------------------- 
 2012-02-29 12:30:44 

MONTHS_BETWEEN

MONTHS_BETWEEN ( <datetime value expr1> , <datetime value expr2> )

Returns a number (not an INTERVAL) possibly with a fraction, representing the number of months between two days. If both dates have the same day of month, or are on the last day of the month, the result is an exact numeric. Otherwise, the fraction is calculated based on 31 days per month. You can cast the resulting value into INTERVAL MONTH and use it for datetime arithmetic. (HyperSQL)

 VALUES MONTHS_BETWEEN ( TIMESTAMP '2013-02-14 12:30:44', TIMESTAMP '2012-01-04 12:30:44')

 C1                                  
 ----------------------------------- 
 13.32258064516129000000000000000000 

TIMESTAMPADD

TIMESTAMPADD ( <tsi datetime field>, <numeric value expression>, <datetime value expr>)

TIMESTAMPDIFF

TIMESTAMPDIFF ( <tsi datetime field>, <datetime value expr 1>, <datetime value expr 2>)

<tsi datetime field> ::= SQL_TSI_FRAC_SECOND | SQL_TSI_MILLI_SECOND | SQL_TSI_SECOND | SQL_TSI_MINUTE | SQL_TSI_HOUR | SQL_TSI_DAY | SQL_TSI_WEEK | SQL_TSI_MONTH | SQL_TSI_QUARTER | SQL_TSI_YEAR

HyperSQL supports full SQL Standard datetime features. It supports adding integers representing units of time directly to datetime values using the arithmetic plus operator. It also supports subtracting one <datetime value expr> from another in the given units of date or time using the minus operator. An example of <datetime value expr> + <numeric value expression> <datetime field> is LOCALTIMESTAMP + 5 DAY. An example of ( <datetime value expr> - <numeric value expression> ) <datetime field> is (CURRENT_DATE - DATE '2008-08-8') MONTH which returns the number of calendar months between the two dates.

The two JDBC functions, TIMESTAMPADD and TIMESTAMPDIFF perform a similar function to the above SQL expressions. The <tsi datetime field> names are keywords and are different from those used in the EXTRACT functions. These names are valid for use only when calling these two functions. With TIMESTAMPDIFF, the names indicate the unit of time used to compute the difference between two datetime fields. With TIMESTAMPADD they represent the unit of time used for the <numeric value expression>. The unit of time for each name is self-explanatory. In the case of SQL_TSI_FRAC_SECOND, the unit is nanosecond.

The type of the return value for TIMESTAMPADD is the same as the type of the datetime argument used, except when the argument type is DATE and a unit smaller than DAY is added the return type is TIMESTAMP. The return type for TIMESTAMPDIFF is always BIGINT, regardless of the type of arguments. The two datetime arguments of TIMESTAMPDIFF should be of the same type. The TIME type is not supported for the arguments to these functions.

TIMESTAMPDIFF is evaluated as <datetime value expr 2> - <datetime value expr 1>. (JDBC)

 TIMESTAMPADD ( SQL_TSI_MONTH, 3, DATE '2008-11-22' )

 TIMESTAMPDIFF ( SQL_TSI_HOUR, TIMESTAMP '2008-11-20 20:30:40', TIMESTAMP '2008-11-21 21:30:40' )

DATE_ADD

DATE_ADD ( <datetime value expr> , <interval value expr> )

DATE_SUB

DATE_SUB ( <datetime value expr> , <interval value expr> )

These functions are equivalent to arithmetic addition and subtraction, <datetime value expr> + <interval value expr> and <datetime value expr> - <interval value expr>. The functions are provided for compatibility with other databases. The supported interval units are the standard SQL interval unit listed in other chapters of this guide. The TIME type is supported for the argument to these functions. (HyperSQL)

 DATE_ADD ( DATE '2008-11-22', INTERVAL 3 MONTH )

 DATE_SUB ( TIMESTAMP '2008-11-22 20:30:40', INTERVAL 20 HOUR )

DATEADD

DATEADD ( <field>, <numeric value expr>, <datetime value expr> )

DATEDIFF

DATEDIFF ( <field>, <datetime value expr 1>, <datetime value expr 2> )

<field> ::= 'yy' | 'year' | 'qq' | 'quarter' |'mm' | 'month' | 'dd' | 'day' | 'hh' | 'hour' | 'mi' | 'minute' | 'ss' | 'second' | 'ms' | 'millisecond' | 'mcs' | 'microsecond' | 'ns' | 'nanosecond'

<field> ::= YY | YEAR | QQ | QUARTER | MM | MONTH | DD | DAY | HH | HOUR | MI | MINUTE | SS | SECOND | MS | MILLISECOND | MCS | MICROSECOND | NS | NANOSECOND

The DATEADD and DATEDIFF functions are alternatives to TIMESTAMPADD and TIMESTAMPDIFF, with similar field options. The field names are specified as strings or as keywords. The short field names translate to YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND and NANOSECOND. DATEDIFF is evaluated as <datetime value expr 2> - <datetime value expr 1>. (HyperSQL}

DATEDIFF ( <datetime value expr 1>, <datetime value expr 2> )

This special form of DATEDIFF does not have a field parameter and return the number of days between two dates. This form is evaluated as <datetime value expr 1> - <datetime value expr 2>, which is different from the main form. This form is compatible with some other database engines. The TIME type is not supported for the arguments to these functions. (HyperSQL}

 DATEADD ( 'month', 3, DATE '2008-11-22' )

 DATEDIFF ( 'hour', TIMESTAMP '2008-11-22 20:30:40', TIMESTAMP '2008-11-22 00:30:40' )

ROUND

ROUND ( <datetime value expr> [ , <char value expr> ] )

The <datetime value expr> is of DATE, TIME or TIMESTAMP type. The <char value expr> is one of the format strings: 'YYYY', 'YY', 'MONTH', 'MM', 'WW', 'IW', 'DDD', 'DD', 'HH24', 'HH12', 'HH', 'MI', 'SS'; represeting YEAR, MONTH, WEEK OF YEAR, ISO WEEK, DAY, HOUR, MINUTE or SECOND as listed in the table for TO_CHAR and TO_DATE format elements (see below). The datetime value is rounded up or down after the specified field and the rest of the fields to the right are set to the minimum value (one for MONTH and DAY, or zero, for the rest of the fields). For example, rounding a timestamp value on the DAY field results in midnight the same date or midnight the next day if the time is at or after 12 noon. If the second argument is omitted, the datetime value is rounded to the nearest day. (HyperSQL)

TRUNC

TRUNC ( <datetime value expr> [ , <char value expr> ] )

Similar to the ROUND function, the datetime value is truncated after the specified field and the rest of the fields to the right are set to the minimum value (one for MONTH and DAY, or zero, for the rest of the fields). For example, applying TRUNC to a timestamp value on the DAY field results in midnight the same date. Examples of ROUND and TRUNC functions are given below. If the second argument is omitted, the datetime value is truncated to midnight the same date. (HyperSQL)

 ROUND ( TIMESTAMP'2008-08-01 20:30:40', 'YYYY' )

 TIMESTAMP '2009-01-01 00:00:00'

 TRUNC ( TIMESTAMP'2008-08-01 20:30:40', 'YYYY' )

 TIMESTAMP '2008-01-01 00:00:00'

FROM_TZ

FROM_TZ ( <timestamp value expr>, <zone or interval spec string> )

This function takes the date-time (year, month, day, hour, minute, second, fraction) from the first argument and the time zone from the second argument to construct a TIMESTAMP WITH TIME ZONE value. The output can represent a different point of universal time (UTC) than the input. This is different from the AT TIME ZONE expression which keeps the UTC value and changes the zone. (HyperSQL)

-- the output shows the same timestamp as the input, but with the time zone of the specified region
VALUES FROM_TZ(TIMESTAMP'2022-03-28 11:00:00+4:00','America/Chicago')
 C1                              
 ------------------------------- 
 2022-03-28 11:00:00.000000-5:00 

-- this example has the same output as the previous one
VALUES FROM_TZ(TIMESTAMP'2022-03-28 11:00:00','-5:00')

NUMTODSINTERVAL

NUMTODSINTERVAL ( <numeric value expr>, <interval spec string> )

This function converts the numeric value to an interval, exactly like CAST. The interval spec string is one of 'DAY', 'HOUR', 'MINUTE', 'SECOND'. (HyperSQL)

NUMTOYMINTERVAL

NUMTOYMINTERVAL ( <numeric value expr>, <interval spec string> )

This function converts the numeric value to an interval, exactly like CAST. The interval spec string is 'YEAR' or 'MONTH'. (HyperSQL)

TIMESTAMP

TIMESTAMP ( <num value expr> )

TIMESTAMP ( <char value expr> )

TIMESTAMP ( <char value expr>, <char value expr> )

TIMESTAMP ( <date value expr>, <time value expr> )

This function translates the arguments into a TIMESTAMP WITHOUT TIME ZONE value.

When the single argument is a numeric value, it is interpreted as a Unix timestamp in seconds.

When the single argument is a formatted date or timestamp string, it is translated to a TIMESTAMP.

When two arguments are used, the first argument is the DATE part and the second argument is the TIME part of the returned TIMESTAMP value. The types of the arguments can be DATE and TIME respectively, or they can be any character string type. An example, including the result, is given below:

 TIMESTAMP ( '2008-11-22', '20:30:40' )

 TIMESTAMP '2008-11-22 20:30:40.000000'

TIMESTAMP_WITH_ZONE

TIMESTAMP_WITH_ZONE ( <num value expr> )

TIMESTAMP_WITH_ZONE ( <char value expr> )

This function translates the arguments into a TIMESTAMP WITH TIME ZONE value.

When the single argument is a numeric value, it is interpreted as a Unix timestamp in seconds.

When the single argument is TIMESTAMP, it is converted to TIMESTAMP WITH TIME ZONE.

The time zone of the returned value is the local time zone at the time of the timestamp argument. This accounts for daylight saving times. For example, if the local time zone was +4:00 at the time of the given Unix timestamp, the returned value is local timestamp at the time with time zone +4:00.

TO_CHAR

TO_CHAR ( <datetime value expr>, <char value expr> )

This function formats a datetime value to the format given in the second argument. See TO_TIMESTAMP below for the details of the format string. Format elements such as DAY or HH12 are used in the examples below, with the result included.

Separator characters (space, comma, period, hyphen, colon, semicolon, forward slash) can be included between the pattern elements. You can include a string literal inside the format string by enclosing it in double quotes (see the second example above). Unsupported format characters should not be used. (HyperSQL)

 TO_CHAR ( TIMESTAMP'2008-02-01 20:30:40', 'YYYY BC DAY DD MONTH HH12 AM' )
 2008 AD Friday 01 February 08 pm 

 TO_CHAR ( DATE'2018-03-01', '"in" YYYY", on" DD MONTH' )
 in 2018, on 01 March

TO_DATE

TO_DATE ( <char value expr>, <char value expr> )

TO_TIMESTAMP

TO_TIMESTAMP ( <char value expr>, <char value expr> )

TO_TIMESTAMP_TZ

TO_TIMESTAMP_TZ ( <char value expr>, <char value expr> )

These functions translate a formatted datetime string to a TIMESTAMP. TO_DATE returns a TIMESTAMP(0) value with no sub-second fraction. TO_TIMESTAMP returns a TIMESTAMP(6). TO_TIMESTAMP_TZ returns a TIMESTAMP(6) WITH TIME ZONE value. The format string can contain pattern elements from the list given below, plus punctuation and space characters. The pattern should contain all the necessary fields to construct a date, including, year, month, day of month, etc. The returned timestamp can then be cast into DATE if necessary. Two examples together with the result are given below:

 TO_TIMESTAMP ( '22/11/2008 20:30:40', 'DD/MM/YYYY HH:MI:SS' )
 TO_TIMESTAMP ( '22/11/2008 20:30:40', 'JAVA:dd/MM/uuuu HH:mm:ss' ) // Java format

 TIMESTAMP '2008-11-22 20:30:40.000000'

The format strings that can be used for TO_DATE and TO_TIMESTAMP are more restrictive than those used for TO_CHAR, because the format string must contain the elements needed to build a full DATE or TIMESTAMP value. For example, you cannot use the 'W', 'HH12' format elements.

The format is internally translated to a java.time.format.DateTimeFormatter format string. Unsupported format strings should not be used. With TO_CHAR, you can include a string literal inside the format string by enclosing it in double quotes.

From version 2.7.3 Java format strings as defined by java.time.format.DateTimeFormatter can be used directly as an alternative. These format strings must have the 'JAVA:' prefix as in the second example above, and the literals inside the format string must be enclosed in double single quotes as required by Java DateTimeFormatter.(HyperSQL)

The supported format components are all uppercase as follows: