H2 database logo   ▲

Home
Download
Cheat Sheet

Documentation
Quickstart
Installation
Tutorial
Features
Performance
Advanced

Reference
Commands
Functions
• Aggregate • Window

Data Types
SQL Grammar
System Tables
Javadoc
PDF (1.5 MB)

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

Appendix
History & Roadmap
License
Build
Links
MVStore
Architecture

 

Data Types

Index

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

Details

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

INT

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

Possible values: -2147483648 to 2147483647.

See also integer literal grammar. Mapped to java.lang.Integer.

Example:

INT

BOOLEAN

BOOLEAN | BIT | BOOL
BOOLEAN
BIT
BOOL

Possible values: TRUE, FALSE, and UNKNOWN (NULL).

See also boolean literal grammar. Mapped to java.lang.Boolean.

Example:

BOOLEAN

TINYINT

TINYINT
TINYINT

Possible values are: -128 to 127.

See also integer literal grammar. Mapped to java.lang.Byte.

Example:

TINYINT

SMALLINT

SMALLINT | INT2 | YEAR
SMALLINT
INT2
YEAR

Possible values: -32768 to 32767.

See also integer literal grammar. Mapped to java.lang.Short.

Example:

SMALLINT

BIGINT

BIGINT | INT8
BIGINT
INT8

Possible values: -9223372036854775808 to 9223372036854775807.

See also long literal grammar. Mapped to java.lang.Long.

Example:

BIGINT

IDENTITY

IDENTITY
IDENTITY

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

See also long literal grammar. Mapped to java.lang.Long.

Example:

IDENTITY

DECIMAL

{ 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.

See also numeric literal grammar. Mapped to java.math.BigDecimal.

Example:

DECIMAL(20, 2)

DOUBLE

{ 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.

See also numeric literal grammar. Mapped to java.lang.Double.

Example:

DOUBLE

REAL

{ 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.

See also numeric literal grammar. Mapped to java.lang.Float.

Example:

REAL

TIME

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.

See also time literal grammar. Mapped to java.sql.Time. java.time.LocalTime is also supported and recommended on Java 8 and later versions. Use java.time.LocalTime or String instead of java.sql.Time when non-zero precision is needed. Cast from higher fractional seconds precision to lower fractional seconds precision performs round half up; if result of rounding is higher than maximum supported value 23:59:59.999999999 the value is rounded down instead. The CAST operation to TIMESTAMP and TIMESTAMP WITH TIME ZONE data types uses the CURRENT_DATE for date fields, comparison operations with values of these data types use the 1970-01-01 instead.

Example:

TIME
TIME(9)

TIME WITH TIME ZONE

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

The time with time zone data type. If fractional seconds precision is specified it should be from 0 to 9, 0 is default.

See also time with time zone literal grammar. Mapped to java.time.OffsetTime on Java 8 and later versions. Cast from higher fractional seconds precision to lower fractional seconds precision performs round half up; if result of rounding is higher than maximum supported value 23:59:59.999999999 the value is rounded down instead. The CAST operation to TIMESTAMP and TIMESTAMP WITH TIME ZONE data types uses the CURRENT_DATE for date fields, comparison operations with values of these data types use the 1970-01-01 instead.

Example:

TIME WITH TIME ZONE
TIME(9) WITH TIME ZONE

DATE

DATE
DATE

The date data type. The proleptic Gregorian calendar is used.

See also date literal grammar. 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 time zone due to a daylight saving change). java.time.LocalDate is also supported and recommended on Java 8 and later versions.

If your time zone had LMT (local mean time) in the past and you use such old dates (depends on the time zone, usually 100 or more years ago), don't use java.sql.Date to read and write them.

If you deal with very old dates (before 1582-10-15) note that java.sql.Date uses a mixed Julian/Gregorian calendar, java.util.GregorianCalendar can be configured to proleptic Gregorian with setGregorianChange(new java.util.Date(Long.MIN_VALUE)) and used to read or write fields of dates.

Example:

DATE

TIMESTAMP

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

The timestamp data type. The proleptic Gregorian calendar is used. If fractional seconds precision is specified it should be from 0 to 9, 6 is default. Fractional seconds precision of SMALLDATETIME is always 0 and cannot be specified.

This data type holds the local date and time without time zone information. It cannot distinguish timestamps near transitions from DST to normal time. For absolute timestamps use the TIMESTAMP WITH TIME ZONE data type instead.

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

If your time zone had LMT (local mean time) in the past and you use such old dates (depends on the time zone, usually 100 or more years ago), don't use java.sql.Timestamp and java.util.Date to read and write them.

If you deal with very old dates (before 1582-10-15) note that java.sql.Timestamp and java.util.Date use a mixed Julian/Gregorian calendar, java.util.GregorianCalendar can be configured to proleptic Gregorian with setGregorianChange(new java.util.Date(Long.MIN_VALUE)) and used to read or write fields of timestamps.

Cast from higher fractional seconds precision to lower fractional seconds precision performs round half up.

Example:

TIMESTAMP
TIMESTAMP(9)

TIMESTAMP WITH TIME ZONE

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

The timestamp with time zone data type. The proleptic Gregorian calendar is used. If fractional seconds precision is specified it should be from 0 to 9, 6 is default.

See also timestamp with time zone literal grammar. Mapped to org.h2.api.TimestampWithTimeZone by default and can be optionally mapped to java.time.OffsetDateTime. java.time.ZonedDateTime 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. Cast from higher fractional seconds precision to lower fractional seconds precision performs round half up.

Example:

TIMESTAMP WITH TIME ZONE
TIMESTAMP(9) WITH TIME ZONE

BINARY

{ BINARY | VARBINARY | BINARY VARYING
| LONGVARBINARY | RAW | BYTEA }
[ ( precisionInt ) ]
BINARY
VARBINARY
BINARY VARYING
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.

See also bytes literal grammar. Mapped to byte[].

Example:

BINARY(1000)

OTHER

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

{ VARCHAR | CHARACTER VARYING | LONGVARCHAR | VARCHAR2 | NVARCHAR
| NVARCHAR2 | VARCHAR_CASESENSITIVE} [ ( precisionInt ) ]
VARCHAR
CHARACTER VARYING
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.

See also string literal grammar. Mapped to java.lang.String.

Example:

VARCHAR(255)

VARCHAR_IGNORECASE

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.

See also string literal grammar. Mapped to java.lang.String.

Example:

VARCHAR_IGNORECASE

CHAR

{ 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.

See also string literal grammar. Mapped to java.lang.String.

Example:

CHAR(10)

BLOB

{ BLOB | BINARY LARGE OBJECT
| TINYBLOB | MEDIUMBLOB | LONGBLOB | IMAGE | OID }
[ ( precisionInt [K|M|G|T|P]) ]
BLOB
BINARY LARGE OBJECT
TINYBLOB
MEDIUMBLOB
LONGBLOB
IMAGE
OID

 
( precisionInt
 
K
M
G
T
P
)

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
BLOB(10K)

CLOB

{ CLOB | CHARACTER LARGE OBJECT
| TINYTEXT | TEXT | MEDIUMTEXT | LONGTEXT | NTEXT | NCLOB }
[ ( precisionInt [K|M|G|T|P] [CHARACTERS|OCTETS]) ]
CLOB
CHARACTER LARGE OBJECT
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
NTEXT
NCLOB

 
( precisionInt
 
K
M
G
T
P
 
CHARACTERS
OCTETS
)

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.

Precision, if any, should be specified in characters, CHARACTERS and OCTETS units have no effect in H2.

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

Example:

CLOB
CLOB(10K)

UUID

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

ARRAY [ '[' maximumCardinalityInt ']' ]
ARRAY
 
[ maximumCardinalityInt ]

An array of values. Maximum cardinality, if any, specifies maximum allowed number of elements in the array.

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

Use PreparedStatement.setArray(..) or PreparedStatement.setObject(.., new Object[] {..}) to store values, and ResultSet.getObject(..) or ResultSet.getArray(..) to retrieve the values.

Example:

ARRAY
ARRAY[10]

ENUM

{ 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

GEOMETRY
[({ GEOMETRY |
{ POINT
| LINESTRING
| POLYGON
| MULTIPOINT
| MULTILINESTRING
| MULTIPOLYGON
| GEOMETRYCOLLECTION } [Z|M|ZM]}
[, sridInt] )]
GEOMETRY
 
(
GEOMETRY
POINT
LINESTRING
POLYGON
MULTIPOINT
MULTILINESTRING
MULTIPOLYGON
GEOMETRYCOLLECTION
 
Z
M
ZM
 
, sridInt
)

A spatial geometry type. If additional constraints are not specified this type accepts all supported types of geometries. A constraint with required geometry type and dimension system can be set by specifying name of the type and dimension system. A whitespace between them is optional. 2D dimension system does not have a name and assumed if only a geometry type name is specified. POINT means 2D point, POINT Z or POINTZ means 3D point. GEOMETRY constraint means no restrictions on type or dimension system of geometry. A constraint with required spatial reference system identifier (SRID) can be set by specifying this identifier.

Mapped to org.locationtech.jts.geom.Geometry if JTS library is in classpath and to java.lang.String otherwise. May be represented in textual format using the WKT (well-known text) or EWKT (extended well-known text) format. Values are stored internally in EWKB (extended well-known binary) format. Only a subset of EWKB and EWKT features is supported. Supported objects are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEOMETRYCOLLECTION. Supported dimension systems are 2D (XY), Z (XYZ), M (XYM), and ZM (XYZM). SRID (spatial reference system identifier) is supported.

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

Example:

GEOMETRY
GEOMETRY(POINT)
GEOMETRY(POINT Z)
GEOMETRY(POINT Z, 4326)
GEOMETRY(GEOMETRY, 4326)

JSON

JSON
JSON

A RFC 8259-compliant JSON text.

See also json literal grammar. Mapped to byte[]. To set a JSON value with java.lang.String in a PreparedStatement use a FORMAT JSON data format (INSERT INTO TEST(ID, DATA) VALUES (?, ? FORMAT JSON)). Without the data format VARCHAR values are converted to a JSON string values.

Order of object members is preserved as is. Duplicate object member names are allowed.

Example:

JSON

INTERVAL

intervalYearType | intervalMonthType | intervalDayType
| intervalHourType| intervalMinuteType | intervalSecondType
| intervalYearToMonthType | intervalDayToHourType
| intervalDayToMinuteType | intervalDayToSecondType
| intervalHourToMinuteType | intervalHourToSecondType
| intervalMinuteToSecondType
intervalYearType
intervalMonthType
intervalDayType
intervalHourType
intervalMinuteType
intervalSecondType
intervalYearToMonthType
intervalDayToHourType
intervalDayToMinuteType
intervalDayToSecondType
intervalHourToMinuteType
intervalHourToSecondType
intervalMinuteToSecondType

Interval data type. There are two classes of intervals. Year-month intervals can store years and months. Day-time intervals can store days, hours, minutes, and seconds. Year-month intervals are comparable only with another year-month intervals. Day-time intervals are comparable only with another day-time intervals.

Mapped to org.h2.api.Interval.

Example:

INTERVAL DAY TO SECOND

Interval Data Types

INTERVAL YEAR

INTERVAL YEAR [ ( precisionInt ) ]
INTERVAL YEAR
 
( precisionInt )

Interval data type. If precision is specified it should be from 1 to 18, 2 is default.

See also year interval literal grammar. Mapped to org.h2.api.Interval. java.time.Period is also supported on Java 8 and later versions.

Example:

INTERVAL YEAR

INTERVAL MONTH

INTERVAL MONTH [ ( precisionInt ) ]
INTERVAL MONTH
 
( precisionInt )

Interval data type. If precision is specified it should be from 1 to 18, 2 is default.

See also month interval literal grammar. Mapped to org.h2.api.Interval. java.time.Period is also supported on Java 8 and later versions.

Example:

INTERVAL MONTH

INTERVAL DAY

INTERVAL DAY [ ( precisionInt ) ]
INTERVAL DAY
 
( precisionInt )

Interval data type. If precision is specified it should be from 1 to 18, 2 is default.

See also day interval literal grammar. Mapped to org.h2.api.Interval. java.time.Duration is also supported on Java 8 and later versions.

Example:

INTERVAL DAY

INTERVAL HOUR

INTERVAL HOUR [ ( precisionInt ) ]
INTERVAL HOUR
 
( precisionInt )

Interval data type. If precision is specified it should be from 1 to 18, 2 is default.

See also hour interval literal grammar. Mapped to org.h2.api.Interval. java.time.Duration is also supported on Java 8 and later versions.

Example:

INTERVAL HOUR

INTERVAL MINUTE

INTERVAL MINUTE [ ( precisionInt ) ]
INTERVAL MINUTE
 
( precisionInt )

Interval data type. If precision is specified it should be from 1 to 18, 2 is default.

See also minute interval literal grammar. Mapped to org.h2.api.Interval. java.time.Duration is also supported on Java 8 and later versions.

Example:

INTERVAL MINUTE

INTERVAL SECOND

INTERVAL SECOND [ ( precisionInt [, fractionalPrecisionInt ] ) ]
INTERVAL SECOND
 
( precisionInt
 
, fractionalPrecisionInt
)

Interval data type. If precision is specified it should be from 1 to 18, 2 is default. If fractional seconds precision is specified it should be from 0 to 9, 6 is default.

See also second interval literal grammar. Mapped to org.h2.api.Interval. java.time.Duration is also supported on Java 8 and later versions.

Example:

INTERVAL SECOND

INTERVAL YEAR TO MONTH

INTERVAL YEAR [ ( precisionInt ) ] TO MONTH
INTERVAL YEAR
 
( precisionInt )
TO MONTH

Interval data type. If leading field precision is specified it should be from 1 to 18, 2 is default.

See also year to month interval literal grammar. Mapped to org.h2.api.Interval. java.time.Period is also supported on Java 8 and later versions.

Example:

INTERVAL YEAR TO MONTH

INTERVAL DAY TO HOUR

INTERVAL DAY [ ( precisionInt ) ] TO HOUR
INTERVAL DAY
 
( precisionInt )
TO HOUR

Interval data type. If leading field precision is specified it should be from 1 to 18, 2 is default.

See also day to hour interval literal grammar. Mapped to org.h2.api.Interval. java.time.Duration is also supported on Java 8 and later versions.

Example:

INTERVAL DAY TO HOUR

INTERVAL DAY TO MINUTE

INTERVAL DAY [ ( precisionInt ) ] TO MINUTE
INTERVAL DAY
 
( precisionInt )
TO MINUTE

Interval data type. If leading field precision is specified it should be from 1 to 18, 2 is default.

See also day to minute interval literal grammar. Mapped to org.h2.api.Interval. java.time.Duration is also supported on Java 8 and later versions.

Example:

INTERVAL DAY TO MINUTE

INTERVAL DAY TO SECOND

INTERVAL DAY [ ( precisionInt ) ] TO SECOND [ ( fractionalPrecisionInt ) ]
INTERVAL DAY
 
( precisionInt )
TO SECOND
 
( fractionalPrecisionInt )

Interval data type. If leading field precision is specified it should be from 1 to 18, 2 is default. If fractional seconds precision is specified it should be from 0 to 9, 6 is default.

See also day to second interval literal grammar. Mapped to org.h2.api.Interval. java.time.Duration is also supported on Java 8 and later versions.

Example:

INTERVAL DAY TO SECOND

INTERVAL HOUR TO MINUTE

INTERVAL HOUR [ ( precisionInt ) ] TO MINUTE
INTERVAL HOUR
 
( precisionInt )
TO MINUTE

Interval data type. If leading field precision is specified it should be from 1 to 18, 2 is default.

See also hour to minute interval literal grammar. Mapped to org.h2.api.Interval. java.time.Duration is also supported on Java 8 and later versions.

Example:

INTERVAL HOUR TO MINUTE

INTERVAL HOUR TO SECOND

INTERVAL HOUR [ ( precisionInt ) ] TO SECOND [ ( fractionalPrecisionInt ) ]
INTERVAL HOUR
 
( precisionInt )
TO SECOND
 
( fractionalPrecisionInt )

Interval data type. If leading field precision is specified it should be from 1 to 18, 2 is default. If fractional seconds precision is specified it should be from 0 to 9, 6 is default.

See also hour to second interval literal grammar. Mapped to org.h2.api.Interval. java.time.Duration is also supported on Java 8 and later versions.

Example:

INTERVAL HOUR TO SECOND

INTERVAL MINUTE TO SECOND

INTERVAL MINUTE [ ( precisionInt ) ] TO SECOND [ ( fractionalPrecisionInt ) ]
INTERVAL MINUTE
 
( precisionInt )
TO SECOND
 
( fractionalPrecisionInt )

Interval data type. If leading field precision is specified it should be from 1 to 18, 2 is default. If fractional seconds precision is specified it should be from 0 to 9, 6 is default.

See also minute to second interval literal grammar. Mapped to org.h2.api.Interval. java.time.Duration is also supported on Java 8 and later versions.

Example:

INTERVAL MINUTE TO SECOND