▲
Search:
| Highlight keyword(s)
Home Download Cheat Sheet Documentation Quickstart Installation Tutorial Features Performance Advanced Reference Commands Functions 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 DATE TIMESTAMP TIMESTAMP WITH TIME ZONE BINARY OTHER VARCHAR VARCHAR_IGNORECASE CHAR | BLOB CLOB UUID ARRAY ENUM GEOMETRY 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. Mapped to `java.lang.Integer`. Example: INT ## BOOLEAN
    BOOLEAN | BIT | BOOL
| `BOOLEAN` |
| `BIT` |
| `BOOL` |
Possible values: `TRUE` and `FALSE`. Mapped to `java.lang.Boolean`. Example: BOOLEAN ## TINYINT
    TINYINT
`TINYINT` Possible values are: -128 to 127. Mapped to `java.lang.Byte`. Example: TINYINT ## SMALLINT
    SMALLINT | INT2 | YEAR
| `SMALLINT` |
| `INT2` |
| `YEAR` |
Possible values: -32768 to 32767. Mapped to `java.lang.Short`. Example: SMALLINT ## BIGINT
    BIGINT | INT8
| `BIGINT` |
| `INT8` |
Possible values: -9223372036854775808 to 9223372036854775807. 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. 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. 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. 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. 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. 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. 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 it is saturated to 23:59:59.999999999. Example: TIME TIME(9) ## DATE
    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
    { TIMESTAMP [ ( precisionInt ) ] [ WITHOUT TIME ZONE ]
    | DATETIME [ ( precisionInt ) ] | SMALLDATETIME }
`TIMESTAMP` |
`( precisionInt )`
`WITHOUT TIME ZONE`
`DATETIME` |
`( precisionInt )`
| `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. Fractional seconds precision of `SMALLDATETIME` is always 0 and cannot be specified. Mapped to `java.sql.Timestamp` (`java.util.Date` may be used too). `java.time.LocalDateTime` is also supported on Java 8 and later versions. 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. 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. 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. 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. 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. 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. 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
`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
    { 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) ## 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. Mapped to `org.h2.api.Interval`. `java.time.Duration` is also supported on Java 8 and later versions. Example: INTERVAL MINUTE TO SECOND