H2 Database Engine

https://www.h2database.com/html/
Admin
H2 Database Engine is a very fast, open-source Java SQL database that supports embedded and server...
Tokens:234,066
Snippets:2,179
Trust Score:9.6
Update:1 week ago
Show doc for...
Tokens:
Raw
### CREATE SEQUENCE examples

Source: https://www.h2database.com/html/commands.html

Two examples of creating sequences. The first creates a sequence with default settings. The second creates a sequence with an explicit data type and start value. Schema owner rights are required.

```sql
CREATE SEQUENCE SEQ_ID;
```

```sql
CREATE SEQUENCE SEQ2 AS INTEGER START WITH 10;
```

--------------------------------

### XMLSTARTDOC example

Source: https://www.h2database.com/html/functions.html

Example call to XMLSTARTDOC.

```sql
CALL XMLSTARTDOC()
```

--------------------------------

### SET @ example

Source: https://www.h2database.com/html/commands.html

Example of the SET @ command.

```sql
SET @TOTAL=0
```

--------------------------------

### SECOND example

Source: https://www.h2database.com/html/functions.html

Example call to SECOND.

```sql
SECOND(CREATED|interval)
```

--------------------------------

### NVL2 example

Source: https://www.h2database.com/html/functions.html

Example usage of NVL2.

```sql
NVL2(X, 'not null', 'null')
```

--------------------------------

### CREATE ALIAS examples

Source: https://www.h2database.com/html/commands.html

Examples of creating function aliases using FOR and AS, and calling them with CALL. Includes Java and Groovy source code examples.

```sql
CREATE ALIAS MY_SQRT FOR 'java.lang.Math.sqrt';  
CREATE ALIAS MY_ROUND FOR 'java.lang.Math.round(double)';  
CREATE ALIAS GET_SYSTEM_PROPERTY FOR 'java.lang.System.getProperty';  
CALL GET_SYSTEM_PROPERTY('java.class.path');  
CALL GET_SYSTEM_PROPERTY('com.acme.test', 'true');  
CREATE ALIAS REVERSE AS 'String reverse(String s) { return new StringBuilder(s).reverse().toString(); }';  
CALL REVERSE('Test');  
CREATE ALIAS tr AS '@groovy.transform.CompileStatic  
static String tr(String str, String sourceSet, String replacementSet){  
return str.tr(sourceSet, replacementSet);  
}  
'
```

--------------------------------

### CURRENT_USER example

Source: https://www.h2database.com/html/functions.html

Example usage of CURRENT_USER.

```sql
CURRENT_USER
```

--------------------------------

### SAVEPOINT example

Source: https://www.h2database.com/html/commands.html

Example of the SAVEPOINT command.

```sql
SAVEPOINT HALF_DONE
```

--------------------------------

### Create Database with H2 Shell

Source: https://www.h2database.com/html/tutorial.html

Starts the H2 Shell tool to create a new local database. The user is prompted for URL, driver, user, and password. The example shows connecting to `jdbc:h2:./path/to/database` and then quitting.

```shell
> java -cp h2-*.jar org.h2.tools.Shell

Welcome to H2 Shell
Exit with Ctrl+C
[Enter]   jdbc:h2:mem:2
URL       jdbc:h2:./path/to/database
[Enter]   org.h2.Driver
Driver
[Enter]   sa
User      your_username
Password  (hidden)
Type the same password again to confirm database creation.
Password  (hidden)
Connected

sql> quit
Connection closed
```

--------------------------------

### SESSION_ID example

Source: https://www.h2database.com/html/functions.html

Example call to SESSION_ID.

```sql
CALL SESSION_ID()
```

--------------------------------

### SYSTEM_RANGE examples: basic, step, empty, and negative step

Source: https://www.h2database.com/html/systemtables.html

Shows four queries against SYSTEM_RANGE with their expected outputs. The first returns all integers from 1 to 10. The second returns every second value. The third returns no rows because start < end with a negative step. The fourth returns values from 10 down to 2 with a step of -2.

```sql
SELECT X FROM SYSTEM_RANGE(1, 10);
-- 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
SELECT X FROM SYSTEM_RANGE(1, 10, 2);
-- 1, 3, 5, 7, 9
SELECT X FROM SYSTEM_RANGE(1, 10, -1);
-- No rows
SELECT X FROM SYSTEM_RANGE(10, 2, -2);
-- 10, 8, 6, 4, 2
```

--------------------------------

### Digit Example

Source: https://www.h2database.com/html/grammar.html

Example of a digit.

```text
0
```

--------------------------------

### ISO_YEAR example

Source: https://www.h2database.com/html/functions.html

Example call to ISO_YEAR.

```sql
ISO_YEAR(CREATED)
```

--------------------------------

### Query examples

Source: https://www.h2database.com/html/grammar.html

Examples of SELECT, TABLE, and VALUES queries.

```h2
SELECT ID FROM TEST;
```

```h2
TABLE TEST;
```

```h2
VALUES (1, 2), (3, 4);
```

--------------------------------

### CALL example

Source: https://www.h2database.com/html/commands.html

Example of calling an expression.

```sql
CALL 15*25
```

--------------------------------

### Expression Example

Source: https://www.h2database.com/html/grammar.html

Example of an expression using OR.

```text
ID=1 OR NAME='Hi'
```

--------------------------------

### SELECT Examples

Source: https://www.h2database.com/html/commands.html

Multiple SQL examples demonstrating basic SELECT, ORDER BY, GROUP BY, HAVING, UNION, OFFSET/FETCH, WITH TIES, subqueries, DISTINCT, DISTINCT ON, and FOR UPDATE WAIT.

```sql
SELECT * FROM TEST;
```

```sql
SELECT * FROM TEST ORDER BY NAME;
```

```sql
SELECT ID, COUNT(*) FROM TEST GROUP BY ID;
```

```sql
SELECT NAME, COUNT(*) FROM TEST GROUP BY NAME HAVING COUNT(*) > 2;
```

```sql
SELECT 'ID' COL, MAX(ID) AS MAX FROM TEST UNION SELECT 'NAME', MAX(NAME) FROM TEST;
```

```sql
SELECT * FROM TEST OFFSET 1000 ROWS FETCH FIRST 1000 ROWS ONLY;
```

```sql
SELECT A, B FROM TEST ORDER BY A FETCH FIRST 10 ROWS WITH TIES;
```

```sql
SELECT * FROM (SELECT ID, COUNT(*) FROM TEST
GROUP BY ID UNION SELECT NULL, COUNT(*) FROM TEST)
ORDER BY 1 NULLS LAST;
```

```sql
SELECT DISTINCT C1, C2 FROM TEST;
```

```sql
SELECT DISTINCT ON(C1) C1, C2 FROM TEST ORDER BY C1;
```

```sql
SELECT ID, V FROM TEST WHERE ID IN (1, 2, 3) FOR UPDATE WAIT 0.5;
```

--------------------------------

### Start second TCP server for cluster (server2)

Source: https://www.h2database.com/html/advanced.html

Starts an H2 TCP server on port 9102 with base directory server2, simulating the second redundant node. Used as the second step in the clustering example.

```bash
java org.h2.tools.Server
    -tcp -tcpPort 9102
    -baseDir server2

```

--------------------------------

### Data Type Example

Source: https://www.h2database.com/html/grammar.html

Example of a data type.

```text
INTEGER
```

--------------------------------

### Start first TCP server for cluster (server1)

Source: https://www.h2database.com/html/advanced.html

Starts an H2 TCP server on port 9101 with base directory server1, simulating the first node of the cluster. Used as the first step in the clustering example.

```bash
java org.h2.tools.Server
    -tcp -tcpPort 9101
    -baseDir server1

```

--------------------------------

### HELP Example

Source: https://www.h2database.com/html/commands.html

Example of displaying help for the SELECT command.

```sql
HELP SELECT

```

--------------------------------

### References Specification example

Source: https://www.h2database.com/html/grammar.html

Example of a REFERENCES clause.

```h2
REFERENCES TEST(ID)
```

--------------------------------

### Automatic Mixed Mode Connection Example

Source: https://www.h2database.com/html/features.html

Example of two applications connecting to the same database using automatic mixed mode. Both applications use the same URL with ;AUTO_SERVER=TRUE. The first connection starts a server internally; subsequent connections use server mode.

```java
// Application 1:
DriverManager.getConnection("jdbc:h2:/data/test;AUTO_SERVER=TRUE");

// Application 2:
DriverManager.getConnection("jdbc:h2:/data/test;AUTO_SERVER=TRUE");

```

--------------------------------

### SHOW TABLES example

Source: https://www.h2database.com/html/commands.html

Example of the SHOW command to list tables.

```sql
SHOW TABLES
```

--------------------------------

### Factor Example

Source: https://www.h2database.com/html/grammar.html

Example of a factor using multiplication.

```text
ID * 10
```

--------------------------------

### Override clause examples

Source: https://www.h2database.com/html/grammar.html

Examples of the OVERRIDING clause.

```h2
OVERRIDING SYSTEM VALUE
```

```h2
OVERRIDING USER VALUE
```

--------------------------------

### H2 Console built-in command: @prof_start and call hash

Source: https://www.h2database.com/html/tutorial.html

Start profiling and call the hash function with a large iteration count. This is part of the @prof_start command example.

```sql
@prof_start;
```

```sql
call hash('SHA256', '', 1000000);
```

--------------------------------

### Lucene Fulltext Search in Specific Column

Source: https://www.h2database.com/html/tutorial.html

Example showing how to search in a specific column. Column names must be uppercase (unless double quoted) and underscores must be doubled for names starting with underscore.

```sql
CREATE ALIAS IF NOT EXISTS FTL_INIT FOR "org.h2.fulltext.FullTextLucene.init";
CALL FTL_INIT();
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST(ID INT PRIMARY KEY, FIRST_NAME VARCHAR, LAST_NAME VARCHAR);
CALL FTL_CREATE_INDEX('PUBLIC', 'TEST', NULL);
INSERT INTO TEST VALUES(1, 'John', 'Wayne');
INSERT INTO TEST VALUES(2, 'Elton', 'John');
SELECT * FROM FTL_SEARCH_DATA('John', 0, 0);
SELECT * FROM FTL_SEARCH_DATA('LAST_NAME:John', 0, 0);
CALL FTL_DROP_ALL();
```

--------------------------------

### Comment Example

Source: https://www.h2database.com/html/grammar.html

Example of a line comment and a bracketed comment.

```sql
-- comment  
/* comment */
```

--------------------------------

### EXPLAIN example

Source: https://www.h2database.com/html/commands.html

Example of showing the execution plan for a SELECT query.

```sql
EXPLAIN SELECT * FROM TEST WHERE ID=1
```

--------------------------------

### Set H2 server cached objects via system property

Source: https://www.h2database.com/html/advanced.html

Command-line example for setting the h2.serverCachedObjects system property to 256 when starting the H2 server. Settings are case sensitive and can be read from INFORMATION_SCHEMA.SETTINGS.

```bash
java -Dh2.serverCachedObjects=256 org.h2.tools.Server

```

--------------------------------

### Data Type or Domain Example

Source: https://www.h2database.com/html/grammar.html

Examples of a data type and a domain name.

```text
INTEGER  
MY_DOMAIN
```

--------------------------------

### CREATE TABLE Example

Source: https://www.h2database.com/html/commands.html

A basic example creating a table named TEST with an integer primary key and a varchar column.

```sql
CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))
```

--------------------------------

### Column Definition Example

Source: https://www.h2database.com/html/grammar.html

Example CREATE TABLE statements showing column definitions with DEFAULT, PRIMARY KEY, and GENERATED ALWAYS AS expressions.

```sql
CREATE TABLE TEST(ID INT PRIMARY KEY,  
NAME VARCHAR(255) DEFAULT '' NOT NULL);  
CREATE TABLE TEST(ID BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  
QUANTITY INT, PRICE NUMERIC(10, 2),  
AMOUNT NUMERIC(20, 2) GENERATED ALWAYS AS (QUANTITY * PRICE));
```

--------------------------------

### Operand examples

Source: https://www.h2database.com/html/grammar.html

Examples of concatenation operations with strings, binary strings, and arrays.

```h2
'Hi' || ' Eva'
```

```h2
X'AB' || X'CD'
```

```h2
ARRAY[1, 2] || 3
```

```h2
1 || ARRAY[2, 3]
```

```h2
ARRAY[1, 2] || ARRAY[3, 4]
```

--------------------------------

### JDBC Connection Example

Source: https://www.h2database.com/html/tutorial.html

Loads the H2 JDBC driver and opens a connection to a database at `jdbc:h2:~/test` with user `sa` and an empty password. The database URL must start with `jdbc:h2:`. User names are case-insensitive, but passwords are case-sensitive.

```java
import java.sql.*;
public class Test {
    public static void main(String[] a)
            throws Exception {
        Connection conn = DriverManager.
            getConnection("jdbc:h2:~/test", "sa", "");
        // add application code here
        conn.close();
    }
}

```

--------------------------------

### CSVWRITE Example with Options

Source: https://www.h2database.com/html/grammar.html

Example of using CSVWRITE with a charset and field separator option. The field separator is set to a pipe character.

```sql
CALL CSVWRITE('test2.csv', 'SELECT * FROM TEST', 'charset=UTF-8 fieldSeparator=|');
```

--------------------------------

### Name example: TEST

Source: https://www.h2database.com/html/grammar.html

Example of a valid identifier name.

```h2
TEST
```

--------------------------------

### Bracketed Comment Example

Source: https://www.h2database.com/html/grammar.html

Example of a bracketed comment and a nested bracketed comment.

```sql
/* comment */  
/* comment /* nested comment */ comment */
```

--------------------------------

### CSV Options Example with STRINGDECODE

Source: https://www.h2database.com/html/grammar.html

Shows how to combine all CSV options into a single string using STRINGDECODE. Note that spaces must be escaped with a backslash and backslashes with another backslash.

```sql
STRINGDECODE('charset=UTF-8 escape=" fieldDelimiter=" fieldSeparator=, ' || 'lineComment=# lineSeparator=\n null= rowSeparator=')
```

--------------------------------

### BACKUP example

Source: https://www.h2database.com/html/commands.html

Example of backing up the database to a zip file.

```sql
BACKUP TO 'backup.zip'
```

--------------------------------

### CAST examples

Source: https://www.h2database.com/html/grammar.html

Three example CAST expressions: converting a column to INT, casting a timestamp to TIME(6), and casting a string with a format template. These illustrate basic type conversion, fractional seconds precision, and format template usage.

```sql
CAST(NAME AS INT);
CAST(TIMESTAMP '2010-01-01 10:40:00.123456' AS TIME(6));
CAST('12:00:00 P.M.' AS TIME FORMAT 'HH:MI:SS A.M.');
```

--------------------------------

### Data Change Delta Table Example

Source: https://www.h2database.com/html/grammar.html

Example of using FINAL TABLE with an INSERT command to return the new rows after execution.

```sql
SELECT ID FROM FINAL TABLE (INSERT INTO TEST (A, B) VALUES (1, 2))
```

--------------------------------

### Quoted Name examples

Source: https://www.h2database.com/html/grammar.html

Examples of quoted and Unicode identifiers.

```h2
"FirstName"
```

```h2
U&"\00d6ffnungszeit"
```

```h2
U&"/00d6ffnungszeit" UESCAPE '/'
```

--------------------------------

### CREATE USER example

Source: https://www.h2database.com/html/commands.html

Example of creating a user with a password. The password is case sensitive and can contain spaces.

```sql
CREATE USER GUEST PASSWORD 'abc'
```

--------------------------------

### Referential Constraint example

Source: https://www.h2database.com/html/grammar.html

Example of a foreign key constraint referencing a column.

```h2
FOREIGN KEY(ID) REFERENCES TEST(ID)
```

--------------------------------

### Grouping Element Example

Source: https://www.h2database.com/html/grammar.html

Examples of grouping elements: a single expression, a list of expressions, and an empty grouping.

```text
A  
(B, C)  
()
```

--------------------------------

### PERCENT_RANK Usage Examples

Source: https://www.h2database.com/html/functions-window.html

Examples demonstrating the use of PERCENT_RANK with and without partitioning.

```sql
SELECT PERCENT_RANK() OVER (ORDER BY ID), * FROM TEST;
SELECT PERCENT_RANK() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
```

--------------------------------

### Column Constraint Definition Example

Source: https://www.h2database.com/html/grammar.html

Example column constraints: NOT NULL, PRIMARY KEY, UNIQUE, REFERENCES, and CHECK.

```sql
NOT NULL  
PRIMARY KEY  
UNIQUE  
REFERENCES T2(ID)  
CHECK (VALUE > 0)
```

--------------------------------

### TRUNCATE_VALUE example

Source: https://www.h2database.com/html/functions.html

Example call to TRUNCATE_VALUE.

```sql
CALL TRUNCATE_VALUE(X, 10, TRUE);
```

--------------------------------

### RANK Usage Examples

Source: https://www.h2database.com/html/functions-window.html

Examples demonstrating the use of RANK with and without partitioning.

```sql
SELECT RANK() OVER (ORDER BY ID), * FROM TEST;
SELECT RANK() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
```

--------------------------------

### TRANSACTION_ID example

Source: https://www.h2database.com/html/functions.html

Example call to TRANSACTION_ID.

```sql
CALL TRANSACTION_ID()
```

--------------------------------

### SET example

Source: https://www.h2database.com/html/functions.html

Example of using SET to implement a running total.

```sql
SELECT X, SET(@I, COALESCE(@I, 0)+X) RUNNING_TOTAL FROM SYSTEM_RANGE(1, 10)
```

--------------------------------

### LEAD function examples

Source: https://www.h2database.com/html/functions-window.html

Example queries using LEAD. The first uses a default offset of 1. The second uses offset 2, default value 0, and IGNORE NULLS with partitioning.

```sql
SELECT LEAD(X) OVER (ORDER BY ID), * FROM TEST;
```

```sql
SELECT LEAD(X, 2, 0) IGNORE NULLS OVER (
PARTITION BY CATEGORY ORDER BY ID
), * FROM TEST;
```

--------------------------------

### EXECUTE IMMEDIATE example

Source: https://www.h2database.com/html/commands.html

Example of dynamically executing a SQL command to drop a constraint.

```sql
EXECUTE IMMEDIATE 'ALTER TABLE TEST DROP CONSTRAINT ' ||
QUOTE_IDENT((SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'TEST'
AND CONSTRAINT_TYPE = 'UNIQUE'));
```

--------------------------------

### NULLIF example

Source: https://www.h2database.com/html/functions.html

Example usage of NULLIF.

```sql
NULLIF(A, B)
```

--------------------------------

### FIRST_VALUE example queries

Source: https://www.h2database.com/html/functions-window.html

Example queries for FIRST_VALUE. The first shows basic usage, the second uses IGNORE NULLS with a PARTITION BY clause.

```sql
SELECT FIRST_VALUE(X) OVER (ORDER BY ID), * FROM TEST;
```

```sql
SELECT FIRST_VALUE(X) IGNORE NULLS OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
```

--------------------------------

### YEAR example

Source: https://www.h2database.com/html/functions.html

Example call to YEAR.

```sql
YEAR(CREATED)
```

--------------------------------

### ISO_WEEK example

Source: https://www.h2database.com/html/functions.html

Example call to ISO_WEEK.

```sql
ISO_WEEK(CREATED)
```

--------------------------------

### WEEK example

Source: https://www.h2database.com/html/functions.html

Example call to WEEK.

```sql
WEEK(CREATED)
```

--------------------------------

### SET PASSWORD example

Source: https://www.h2database.com/html/commands.html

Example usage of SET PASSWORD with a sample password.

```sql
SET PASSWORD 'abcstzri!.5'
```

--------------------------------

### SET MODE example

Source: https://www.h2database.com/html/commands.html

Example usage of SET MODE to switch to HSQLDB compatibility mode.

```sql
SET MODE HSQLDB
```

--------------------------------

### COMPRESS example call

Source: https://www.h2database.com/html/functions.html

Example: COMPRESS(STRINGTOUTF8('Test')) compresses the string 'Test'.

```sql
COMPRESS(STRINGTOUTF8('Test'))
```

--------------------------------

### QUARTER example

Source: https://www.h2database.com/html/functions.html

Example call to QUARTER.

```sql
QUARTER(CREATED)
```

--------------------------------

### TRANSLATE example

Source: https://www.h2database.com/html/functions.html

Example call to TRANSLATE.

```sql
CALL TRANSLATE('Hello world', 'eo', 'EO')
```
Tokens:
Raw