Data Types

Index

CHARACTER CHARACTER VARYING CHARACTER LARGE OBJECT VARCHAR_IGNORECASE BINARY BINARY VARYING BINARY LARGE OBJECT BOOLEAN TINYINT SMALLINT

INTEGER BIGINT NUMERIC REAL DOUBLE PRECISION DECFLOAT DATE TIME TIME WITH TIME ZONE TIMESTAMP

TIMESTAMP WITH TIME ZONE INTERVAL JAVA_OBJECT ENUM GEOMETRY JSON UUID ARRAY ROW

Details

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

Non-standard syntax is marked in green. Compatibility-only non-standard syntax is marked in red, don't use it unless you need it for compatibility with other databases or old versions of H2.

CHARACTER

{ CHARACTER | CHAR | NATIONAL { CHARACTER | CHAR } | NCHAR }
[ ( lengthInt [CHARACTERS|OCTETS] ) ]

A Unicode String of fixed length.

Length, if any, should be specified in characters, CHARACTERS and OCTETS units have no effect in H2. The allowed length is from 1 to 1,000,000,000 characters. If length is not specified, 1 character is used by default.

The whole text is kept in memory when using this data type. For variable-length strings use CHARACTER VARYING data type instead. For large text data CHARACTER LARGE OBJECT should be used; see there for details.

Too short strings are right-padded with space characters. Too long strings are truncated by CAST specification and rejected by column assignment.

Two CHARACTER strings of different length are considered as equal if all additional characters in the longer string are space characters.

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

Example:

CHARACTER
CHAR(10)

CHARACTER VARYING

{ { CHARACTER | CHAR } VARYING
| VARCHAR
| { NATIONAL { CHARACTER | CHAR } | NCHAR } VARYING
| { VARCHAR_CASESENSITIVE } }
[ ( lengthInt [CHARACTERS|OCTETS] ) ]

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

The allowed length is from 1 to 1,000,000,000 characters. The length is a size constraint; only the actual data is persisted. Length, if any, should be specified in characters, CHARACTERS and OCTETS units have no effect in H2.

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

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

Example:

CHARACTER VARYING(100)
VARCHAR(255)

CHARACTER LARGE OBJECT

{ { CHARACTER | CHAR } LARGE OBJECT | CLOB
| { NATIONAL CHARACTER | NCHAR } LARGE OBJECT | NCLOB }
[ ( lengthLong [K|M|G|T|P] [CHARACTERS|OCTETS]) ]

CHARACTER LARGE OBJECT is intended for very large Unicode character string values. Unlike when using CHARACTER VARYING, large CHARACTER LARGE OBJECT values are not kept fully in-memory; instead, they are streamed. CHARACTER LARGE OBJECT 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 Large Objects section.

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

Length, 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:

CHARACTER LARGE OBJECT
CLOB(10K)

VARCHAR_IGNORECASE

VARCHAR_IGNORECASE
[ ( lengthInt [CHARACTERS|OCTETS] ) ]

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

The allowed length is from 1 to 1,000,000,000 characters. The length is a size constraint; only the actual data is persisted. Length, if any, should be specified in characters, CHARACTERS and OCTETS units have no effect in H2.

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

BINARY

BINARY [ ( lengthInt ) ]

Represents a binary string (byte array) of fixed predefined length.

The allowed length is from 1 to 1,000,000,000 bytes. If length is not specified, 1 byte is used by default.

The whole binary string is kept in memory when using this data type. For variable-length binary strings use BINARY VARYING data type instead. For large binary data BINARY LARGE OBJECT should be used; see there for details.

Too short binary string are right-padded with zero bytes. Too long binary strings are truncated by CAST specification and rejected by column assignment.

Binary strings of different length are considered as not equal to each other.

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

Example:

BINARY
BINARY(1000)

BINARY VARYING

{ BINARY VARYING | VARBINARY }
[ ( lengthInt ) ]

Represents a byte array.

The allowed length is from 1 to 1,000,000,000 bytes. The length is a size constraint; only the actual data is persisted.

The whole binary string is kept in memory when using this data type. For large binary data BINARY LARGE OBJECT should be used; see there for details.

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

Example:

BINARY VARYING(100)
VARBINARY(1000)

BINARY LARGE OBJECT

{ BINARY LARGE OBJECT | BLOB }
[ ( lengthLong [K|M|G|T|P]) ]

BINARY LARGE OBJECT is intended for very large binary values such as files or images. Unlike when using BINARY VARYING, large objects are not kept fully in-memory; instead, they are streamed. Use PreparedStatement.setBinaryStream to store values. See also CHARACTER LARGE OBJECT and Large Objects section.

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

Example:

BINARY LARGE OBJECT
BLOB(10K)

BOOLEAN

BOOLEAN

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

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

Example:

BOOLEAN

TINYINT

TINYINT

Possible values are: -128 to 127.

See also integer literal grammar.

In JDBC this data type is mapped to java.lang.Integer. java.lang.Byte is also supported.

In org.h2.api.Aggregate, org.h2.api.AggregateFunction, and org.h2.api.Trigger this data type is mapped to java.lang.Byte.

Example:

TINYINT

SMALLINT

SMALLINT

Possible values: -32768 to 32767.

See also integer literal grammar.

In JDBC this data type is mapped to java.lang.Integer. java.lang.Short is also supported.

In org.h2.api.Aggregate, org.h2.api.AggregateFunction, and org.h2.api.Trigger this data type is mapped to java.lang.Short.

Example:

SMALLINT

INTEGER

INTEGER | INT

Possible values: -2147483648 to 2147483647.

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

Example:

INTEGER
INT

BIGINT

BIGINT

Possible values: -9223372036854775808 to 9223372036854775807.

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

Example:

BIGINT

NUMERIC

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

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

If precision is specified, it must be from 1 to 100000. If scale is specified, it must be from 0 to 100000, 0 is default.

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

Example:

NUMERIC(20, 2)

REAL

REAL | FLOAT ( precisionInt )

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 1 to 24.

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

Example:

REAL

DOUBLE PRECISION

DOUBLE PRECISION | FLOAT [ ( precisionInt ) ]

A double precision 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 PRECISION

DECFLOAT

DECFLOAT [ ( precisionInt ) ]

Decimal floating point number. This data type is not recommended to represent currency values, because of variable scale.

If precision is specified, it must be from 1 to 100000.

See also numeric literal grammar. Mapped to java.math.BigDecimal. There are three special values: 'Infinity', '-Infinity', and 'NaN'. These special values can't be read or set as BigDecimal values, but they can be read or set using java.lang.String, float, or double.

Example:

DECFLOAT
DECFLOAT(20)

DATE

DATE

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

See also date literal grammar.

In JDBC this data type is 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.

In org.h2.api.Aggregate, org.h2.api.AggregateFunction, and org.h2.api.Trigger this data type is mapped to java.time.LocalDate.

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

TIME

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.

In JDBC this data type is mapped to java.sql.Time. java.time.LocalTime is also supported and recommended.

In org.h2.api.Aggregate, org.h2.api.AggregateFunction, and org.h2.api.Trigger this data type is mapped to java.time.LocalTime.

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.

Example:

TIME
TIME(9)

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

Example:

TIME WITH TIME ZONE
TIME(9) WITH TIME ZONE

TIMESTAMP

TIMESTAMP [ ( precisionInt ) ] [ WITHOUT TIME ZONE ]

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.

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.

In JDBC this data type is mapped to java.sql.Timestamp (java.util.Date may be used too). java.time.LocalDateTime is also supported and recommended.

In org.h2.api.Aggregate, org.h2.api.AggregateFunction, and org.h2.api.Trigger this data type is mapped to java.time.LocalDateTime.

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

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 java.time.OffsetDateTime. java.time.ZonedDateTime and java.time.Instant are also supported.

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

INTERVAL

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

JAVA_OBJECT

{ JAVA_OBJECT | OBJECT | OTHER } [ ( lengthInt ) ]

This type allows storing serialized Java objects. Internally, a byte array with serialized form is used. The allowed length is from 1 (useful only with custom serializer) to 1,000,000,000 bytes. The length is a size constraint; only the actual data is persisted.

Serialization and deserialization is done on the client side only with two exclusions described below. Deserialization is only done when getObject is called. Java operations cannot be executed inside the database engine for security reasons. Use PreparedStatement.setObject with Types.JAVA_OBJECT or H2Type.JAVA_OBJECT as a third argument to store values.

If Java method alias has Object parameter(s), values are deserialized during invocation of this method on the server side.

If a linked table has a column with Types.JAVA_OBJECT JDBC data type and its database is not an another H2, Java objects need to be serialized and deserialized during interaction between H2 and database that owns the table on the server side of H2.

This data type needs special attention in secure environments.

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

Example:

JAVA_OBJECT
JAVA_OBJECT(10000)

ENUM

ENUM (string [, ... ])

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

Duplicate and empty values are not permitted. The maximum number of values is 65536. The maximum allowed length of complete data type definition with all values is 1,000,000,000 characters.

Example:

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

GEOMETRY

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, the maximum allowed length is 1,000,000,000 bytes. 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 [(lengthInt)]

A RFC 8259-compliant JSON text.

See also json literal grammar. Mapped to byte[]. The allowed length is from 1 to 1,000,000,000 bytes. The length is a size constraint; only the actual data is persisted.

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)) or use setObject(parameter, jsonText, H2Type.JSON) instead of setString().

Without the data format VARCHAR values are converted to JSON string values.

SQL/JSON null value JSON 'null' is distinct from the SQL null value NULL.

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

Example:

JSON

UUID

UUID

RFC 9562-compliant 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. To avoid this problem use UUID version 7 values.

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

Example:

UUID

ARRAY

baseDataType ARRAY [ '[' maximumCardinalityInt ']' ]

A data type for array of values. Base data type specifies the data type of elements. Array may have NULL elements. Maximum cardinality, if any, specifies maximum allowed number of elements in the array. The allowed cardinality is from 0 to 65536 elements.

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:

BOOLEAN ARRAY
VARCHAR(100) ARRAY
INTEGER ARRAY[10]

ROW

ROW (fieldName dataType [,...])

A row value data type. This data type should not be normally used as data type of a column.

See also row value expression grammar. Mapped to java.sql.ResultSet.

Example:

ROW(A INT, B VARCHAR(10))

Interval Data Types

INTERVAL YEAR

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.

Example:

INTERVAL YEAR

INTERVAL MONTH

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.

Example:

INTERVAL MONTH

INTERVAL DAY

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.

Example:

INTERVAL DAY

INTERVAL HOUR

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.

Example:

INTERVAL HOUR

INTERVAL MINUTE

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.

Example:

INTERVAL MINUTE

INTERVAL SECOND

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.

Example:

INTERVAL SECOND

INTERVAL YEAR 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.

Example:

INTERVAL YEAR TO MONTH

INTERVAL DAY 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.

Example:

INTERVAL DAY TO HOUR

INTERVAL DAY 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.

Example:

INTERVAL DAY TO MINUTE

INTERVAL DAY TO SECOND

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.

Example:

INTERVAL DAY TO SECOND

INTERVAL HOUR 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.

Example:

INTERVAL HOUR TO MINUTE

INTERVAL HOUR TO SECOND

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.

Example:

INTERVAL HOUR TO SECOND

INTERVAL MINUTE TO SECOND

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.

Example:

INTERVAL MINUTE TO SECOND