H2 database logo   ▲

Home
Download
Cheat Sheet

Documentation
Quickstart
Installation
Tutorial
Features
Performance
Advanced

Reference
SQL Grammar
Functions
Data Types
System Tables
Javadoc
PDF (1 MB)

Support
FAQ
Error Analyzer
Google Group (English)
Google Group (Japanese)
Google Group (Chinese)

Appendix
History & Roadmap
License
Build
Links
JaQu
MVStore
Architecture

 

Data Types

Index

INT Type
BOOLEAN Type
TINYINT Type
SMALLINT Type
BIGINT Type
IDENTITY Type
DECIMAL Type
DOUBLE Type
REAL Type
TIME Type
DATE Type
TIMESTAMP Type
TIMESTAMP WITH TIME ZONE Type
BINARY Type
OTHER Type
VARCHAR Type
VARCHAR_IGNORECASE Type
CHAR Type
BLOB Type
CLOB Type
UUID Type
ARRAY Type
ENUM Type
GEOMETRY Type

Details

Click on the header to switch between railroad diagram and BNF.

INT Type

INT | INTEGER | MEDIUMINT | INT4 | SIGNED
INT
INTEGER
MEDIUMINT
INT4
SIGNED

Possible values: -2147483648 to 2147483647.

Mapped to java.lang.Integer.

Example:

INT

BOOLEAN Type

BOOLEAN | BIT | BOOL
BOOLEAN
BIT
BOOL

Possible values: TRUE and FALSE.

Mapped to java.lang.Boolean.

Example:

BOOLEAN

TINYINT Type

TINYINT
TINYINT

Possible values are: -128 to 127.

Mapped to java.lang.Byte.

Example:

TINYINT

SMALLINT Type

SMALLINT | INT2 | YEAR
SMALLINT
INT2
YEAR

Possible values: -32768 to 32767.

Mapped to java.lang.Short.

Example:

SMALLINT

BIGINT Type

BIGINT | INT8
BIGINT
INT8

Possible values: -9223372036854775808 to 9223372036854775807.

Mapped to java.lang.Long.

Example:

BIGINT

IDENTITY Type

IDENTITY
IDENTITY

Auto-Increment value. Possible values: -9223372036854775808 to 9223372036854775807. Used values are never re-used, even when the transaction is rolled back.

Mapped to java.lang.Long.

Example:

IDENTITY

DECIMAL Type

{ DECIMAL | NUMBER | DEC | NUMERIC } ( precisionInt [ , scaleInt ] )
DECIMAL
NUMBER
DEC
NUMERIC
( precisionInt
 
, scaleInt
)

Data type with fixed precision and scale. This data type is recommended for storing currency values.

Mapped to java.math.BigDecimal.

Example:

DECIMAL(20, 2)

DOUBLE Type

{ DOUBLE [ PRECISION ] | FLOAT [ ( precisionInt ) ] | FLOAT8 }
DOUBLE
 
PRECISION
FLOAT
 
( precisionInt )
FLOAT8

A floating point number. Should not be used to represent currency values, because of rounding problems. If precision value is specified for FLOAT type name, it should be from 25 to 53.

Mapped to java.lang.Double.

Example:

DOUBLE

REAL Type

{ REAL | FLOAT ( precisionInt ) | FLOAT4 }
REAL
FLOAT ( precisionInt )
FLOAT4

A single precision floating point number. Should not be used to represent currency values, because of rounding problems. Precision value for FLOAT type name should be from 0 to 24.

Mapped to java.lang.Float.

Example:

REAL

TIME Type

TIME [ ( precisionInt ) ] [ WITHOUT TIME ZONE ]
TIME
 
( precisionInt )
 
WITHOUT TIME ZONE

The time data type. The format is hh:mm:ss[.nnnnnnnnn]. If fractional seconds precision is specified it should be from 0 to 9, 0 is default.

Mapped to java.sql.Time. When converted to a java.sql.Date, the date is set to 1970-01-01. java.time.LocalTime is also supported on Java 8 and later versions. Resolution of java.sql.Time is limited to milliseconds, use String or java.time.LocalTime if you need nanosecond resolution.

Example:

TIME

DATE Type

DATE
DATE

The date data type. The format is yyyy-MM-dd.

Mapped to java.sql.Date, with the time set to 00:00:00 (or to the next possible time if midnight doesn't exist for the given date and timezone due to a daylight saving change). java.time.LocalDate is also supported on Java 8 and later versions.

Example:

DATE

TIMESTAMP Type

{ TIMESTAMP [ ( precisionInt ) ] [ WITHOUT TIME ZONE ]
| DATETIME | SMALLDATETIME }
TIMESTAMP
 
( precisionInt )
 
WITHOUT TIME ZONE
DATETIME
SMALLDATETIME

The timestamp data type. The format is yyyy-MM-dd hh:mm:ss[.nnnnnnnnn]. Stored internally as a BCD-encoded date, and nanoseconds since midnight. If fractional seconds precision is specified it should be from 0 to 9, 6 is default.

Mapped to java.sql.Timestamp (java.util.Date may be used too). java.time.LocalDateTime is also supported on Java 8 and later versions.

Example:

TIMESTAMP

TIMESTAMP WITH TIME ZONE Type

TIMESTAMP [ ( precisionInt ) ] WITH TIME ZONE
TIMESTAMP
 
( precisionInt )
WITH TIME ZONE

The timestamp with time zone data type. Stored internally as a BCD-encoded date, nanoseconds since midnight, and time zone offset in minutes. If fractional seconds precision is specified it should be from 0 to 9, 6 is default.

Mapped to org.h2.api.TimestampWithTimeZone. java.time.OffsetDateTime and java.time.Instant are also supported on Java 8 and later versions.

Values of this data type are compared by UTC values. It means that 2010-01-01 10:00:00+01 is greater than 2010-01-01 11:00:00+03.

Conversion to TIMESTAMP uses time zone offset to get UTC time and converts it to local time using the system time zone. Conversion from TIMESTAMP does the same operations in reverse and sets time zone offset to offset of the system time zone.

Example:

TIMESTAMP WITH TIME ZONE

BINARY Type

{ BINARY | VARBINARY | LONGVARBINARY | RAW | BYTEA }
[ ( precisionInt ) ]
BINARY
VARBINARY
LONGVARBINARY
RAW
BYTEA

 
( precisionInt )

Represents a byte array. For very long arrays, use BLOB. The maximum size is 2 GB, but the whole object is kept in memory when using this data type. The precision is a size constraint; only the actual data is persisted. For large text data BLOB or CLOB should be used.

Mapped to byte[].

Example:

BINARY(1000)

OTHER Type

OTHER
OTHER

This type allows storing serialized Java objects. Internally, a byte array is used. Serialization and deserialization is done on the client side only. Deserialization is only done when getObject is called. Java operations cannot be executed inside the database engine for security reasons. Use PreparedStatement.setObject to store values.

Mapped to java.lang.Object (or any subclass).

Example:

OTHER

VARCHAR Type

{ VARCHAR | LONGVARCHAR | VARCHAR2 | NVARCHAR
| NVARCHAR2 | VARCHAR_CASESENSITIVE} [ ( precisionInt ) ]
VARCHAR
LONGVARCHAR
VARCHAR2
NVARCHAR
NVARCHAR2
VARCHAR_CASESENSITIVE
 
( precisionInt )

A Unicode String. Use two single quotes ('') to create a quote.

The maximum precision is Integer.MAX_VALUE. The precision is a size constraint; only the actual data is persisted.

The whole text is loaded into memory when using this data type. For large text data CLOB should be used; see there for details.

Mapped to java.lang.String.

Example:

VARCHAR(255)

VARCHAR_IGNORECASE Type

VARCHAR_IGNORECASE [ ( precisionInt ) ]
VARCHAR_IGNORECASE
 
( precisionInt )

Same as VARCHAR, but not case sensitive when comparing. Stored in mixed case.

The maximum precision is Integer.MAX_VALUE. The precision is a size constraint; only the actual data is persisted.

The whole text is loaded into memory when using this data type. For large text data CLOB should be used; see there for details.

Mapped to java.lang.String.

Example:

VARCHAR_IGNORECASE

CHAR Type

{ CHAR | CHARACTER | NCHAR } [ ( precisionInt ) ]
CHAR
CHARACTER
NCHAR
 
( precisionInt )

A Unicode String. This type is supported for compatibility with other databases and older applications. The difference to VARCHAR is that trailing spaces are ignored and not persisted.

The maximum precision is Integer.MAX_VALUE. The precision is a size constraint; only the actual data is persisted.

The whole text is kept in memory when using this data type. For large text data CLOB should be used; see there for details.

Mapped to java.lang.String.

Example:

CHAR(10)

BLOB Type

{ BLOB | TINYBLOB | MEDIUMBLOB | LONGBLOB | IMAGE | OID }
[ ( precisionInt ) ]
BLOB
TINYBLOB
MEDIUMBLOB
LONGBLOB
IMAGE
OID

 
( precisionInt )

Like BINARY, but intended for very large values such as files or images. Unlike when using BINARY, large objects are not kept fully in-memory. Use PreparedStatement.setBinaryStream to store values. See also CLOB and Advanced / Large Objects.

Mapped to java.sql.Blob (java.io.InputStream is also supported).

Example:

BLOB

CLOB Type

{ CLOB | TINYTEXT | TEXT | MEDIUMTEXT | LONGTEXT | NTEXT | NCLOB }
[ ( precisionInt ) ]
CLOB
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
NTEXT
NCLOB

 
( precisionInt )

CLOB is like VARCHAR, but intended for very large values. Unlike when using VARCHAR, large CLOB objects are not kept fully in-memory; instead, they are streamed. CLOB should be used for documents and texts with arbitrary size such as XML or HTML documents, text files, or memo fields of unlimited size. Use PreparedStatement.setCharacterStream to store values. See also Advanced / Large Objects.

VARCHAR should be used for text with relatively short average size (for example shorter than 200 characters). Short CLOB values are stored inline, but there is an overhead compared to VARCHAR.

Mapped to java.sql.Clob (java.io.Reader is also supported).

Example:

CLOB

UUID Type

UUID
UUID

Universally unique identifier. This is a 128 bit value. To store values, use PreparedStatement.setBytes, setString, or setObject(uuid) (where uuid is a java.util.UUID). ResultSet.getObject will return a java.util.UUID.

Please note that using an index on randomly generated data will result on poor performance once there are millions of rows in a table. The reason is that the cache behavior is very bad with randomly distributed data. This is a problem for any database system.

For details, see the documentation of java.util.UUID.

Example:

UUID

ARRAY Type

ARRAY
ARRAY

An array of values. Mapped to java.lang.Object[] (arrays of any non-primitive type are also supported).

Use a value list (1, 2) or PreparedStatement.setObject(.., new Object[] {..}) to store values, and ResultSet.getObject(..) or ResultSet.getArray(..) to retrieve the values.

Example:

ARRAY

ENUM Type

{ ENUM (string [, ... ]) }
ENUM ( string
 
, ...
)

A type with enumerated values. Mapped to java.lang.Integer.

The first provided value is mapped to 0, the second mapped to 1, and so on.

Duplicate and empty values are not permitted.

Example:

ENUM('clubs', 'diamonds', 'hearts', 'spades')

GEOMETRY Type

GEOMETRY
GEOMETRY

A spatial geometry type, based on the org.locationtech.jts library. Normally represented in textual format using the WKT (well known text) format.

Use a quoted string containing a WKT formatted string or PreparedStatement.setObject() to store values, and ResultSet.getObject(..) or ResultSet.getString(..) to retrieve the values.

Example:

GEOMETRY