▲
Search:
| Highlight keyword(s)
Home Download Cheat Sheet Documentation Quickstart Installation Tutorial Features Performance Advanced Reference SQL Grammar Functions Data Types 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 | FLOAT8 }
`DOUBLE` |
| `PRECISION` |
| `FLOAT` |
| `FLOAT8` |
A floating point number. Should not be used to represent currency values, because of rounding problems. Mapped to `java.lang.Double`. Example: DOUBLE ### REAL Type
    { REAL | FLOAT4 }
| `REAL` |
| `FLOAT4` |
A single precision floating point number. Should not be used to represent currency values, because of rounding problems. Mapped to `java.lang.Float`. Example: REAL ### TIME Type
    TIME
`TIME` The time data type. The format is hh:mm:ss. Mapped to `java.sql.Time`. When converted to a `java.sql.Date`, the date is set to `1970-01-01`. 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). Example: DATE ### TIMESTAMP Type
    { TIMESTAMP | DATETIME | SMALLDATETIME }
| `TIMESTAMP` |
| `DATETIME` |
| `SMALLDATETIME` |
The timestamp data type. The format is yyyy-`MM`-dd hh:mm:ss[.nnnnnnnnn]. Stored internally as a `BCD`-encoded date, and nano-seconds since midnight. Mapped to `java.sql.Timestamp` (`java.util.Date` is also supported). Example: TIMESTAMP ### TIMESTAMP WITH TIME ZONE Type
    TIMESTAMP WITH TIME ZONE
`TIMESTAMP WITH TIME ZONE`
`VERY MUCH STILL IN TESTING`. The timestamp with time zone data type. Stored internally as a `BCD`-encoded date, nano-seconds since midnight, and timezone offset in minutes. Note that range queries on this datatype may do some weird stuff close to `DST` boundaries. Mapped to `org.h2.api.TimestampWithTimeZone` 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 `com.vividsolutions.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