H2 database logo

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
JaQu
Build
History & Roadmap
Links
License

 

Data Types

INT Type
BOOLEAN Type
TINYINT Type
SMALLINT Type
BIGINT Type
IDENTITY Type
DECIMAL Type
DOUBLE Type
REAL Type
TIME Type
DATE Type
TIMESTAMP Type
BINARY Type
OTHER Type
VARCHAR Type
VARCHAR_IGNORECASE Type
CHAR Type
BLOB Type
CLOB Type
UUID Type
ARRAY Type

INT Type

INT
INTEGER
MEDIUMINT
INT4
SIGNED

Possible values: -2147483648 to 2147483647.

Mapped to java.lang.Integer.

Example:

INT

BOOLEAN Type

BOOLEAN
BIT
BOOL

Possible values: TRUE and FALSE.

Mapped to java.lang.Boolean.

Example:

BOOLEAN

TINYINT Type

TINYINT

Possible values are: -128 to 127.

Mapped to java.lang.Byte.

Example:

TINYINT

SMALLINT Type

SMALLINT
INT2
YEAR

Possible values: -32768 to 32767.

Mapped to java.lang.Short.

Example:

SMALLINT

BIGINT Type

BIGINT
INT8

Possible values: -9223372036854775808 to 9223372036854775807.

Mapped to java.lang.Long.

Example:

BIGINT

IDENTITY Type

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
)

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
FLOAT4
FLOAT8

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

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

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

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

The format is yyyy-MM-dd hh:mm:ss[.nnnnnnnnn].

Mapped to java.sql.Timestamp (java.util.Date is also supported).

Example:

TIMESTAMP

BINARY Type

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

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 get 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 )

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

VARCHAR(255)

VARCHAR_IGNORECASE Type

VARCHAR_IGNORECASE
 
( precisionInt )

Same as VARCHAR, but not case sensitive when comparing. Stored in mixed case. The maximum precision is Integer.MAX_VALUE characters, but 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:

VARCHAR_IGNORECASE

CHAR Type

CHAR
CHARACTER
NCHAR
 
( precisionInt )

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

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

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.

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

Example:

UUID

ARRAY Type

ARRAY

An array of values. Use a value list (1, 2) or PreparedStatement.setObject(.., new Object[] {..}) to store values.

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

Example:

ARRAY