Data Types
Index
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
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
| ||||||||||||||||||
VARCHAR | ||||||||||||||||||
| ||||||||||||||||||
VARCHAR_CASESENSITIVE |
|
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
| |||||||||||
CLOB | |||||||||||
| |||||||||||
NCLOB |
|
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
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 |
|
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
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
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
|
|
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 | |||
|
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
| |||||||||||
|
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 |
|
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 |
|
|
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 |
| 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 |
|
|
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 |
| 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
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
|
|
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 |
|
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 |
|
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 |
|
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
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 |
|
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 |
|
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 |
|
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 |
|
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 |
|
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 |
|
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 |
| 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 |
| 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 |
| 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 |
| TO SECOND |
|
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 |
| 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 |
| TO SECOND |
|
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 |
| TO SECOND |
|
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