H2 Logo
English Japanese
Search:

Highlight keyword(s)
Home
Quickstart
Installation
Tutorial
Features
Performance
Advanced Topics
JaQu
Download

Reference
SQL Grammar
Functions
Data Types
Javadoc
Docs as PDF
Error Analyzer

Appendix
Build
History & Roadmap
Links
FAQ
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.
See also java.lang.Integer.

Example:
INT

BOOLEAN Type

BOOLEAN | BIT | BOOL

Possible values: TRUE and FALSE.
See also java.lang.Boolean.

Example:
BOOLEAN

TINYINT Type

TINYINT

Possible values are: -128 to 127.
See also java.lang.Byte.

Example:
TINYINT

SMALLINT Type

SMALLINT | INT2 | YEAR

Possible values: -32768 to 32767.
See also java.lang.Short.

Example:
SMALLINT

BIGINT Type

BIGINT | INT8

Possible values: -9223372036854775808 to 9223372036854775807.
See also java.lang.Long.

Example:
BIGINT

IDENTITY Type

IDENTITY

Auto-Increment value.
Possible values: -9223372036854775808 to 9223372036854775807.
See also 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.
See also 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.
See also 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.
See also java.lang.Float.

Example:
REAL

TIME Type

TIME

The format is hh:mm:ss.
See also java.sql.Time.

Example:
TIME

DATE Type

DATE

The format is yyyy-MM-dd.
See also java.sql.Date.

Example:
DATE

TIMESTAMP Type

{TIMESTAMP | DATETIME | SMALLDATETIME}

The format is yyyy-MM-dd hh:mm:ss[.nnnnnnnnn].
See also java.sql.Timestamp.

Example:
TIMESTAMP

BINARY Type

{BINARY | VARBINARY | LONGVARBINARY | RAW | BYTEA}
[( precisionInt )]

Represents a byte array. For very long arrays, use BLOB.
There is no maximum precision. The maximum size is the memory available.
The precision is a size constraint; only the actual data is persisted.
The whole object is kept in memory when using this data type.
For large text data BLOB should be used.

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.

Example:
OTHER

VARCHAR Type

{VARCHAR | LONGVARCHAR |
VARCHAR2 | NVARCHAR | NVARCHAR2 | VARCHAR_CASESENSITIVE}
[( precisionInt )]

Unicode String. Use two single quotes ('') to create a quote.
There is no maximum precision. The maximum size is the memory available.
The precision is a size constraint; only the actual data is persisted.
For large text data CLOB should be used; see there for details.
See also 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.
There is no maximum precision. The maximum size is the memory available.
For large text data CLOB should be used; see there for details.

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.
There is no maximum precision. The maximum size is the memory available.
For large text data CLOB should be used; see there for details.

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.

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.
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.
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.
Use PreparedStatement.setCharacterStream to store values.

Example:
CLOB

UUID Type

UUID

Universally unique identifier. This is a 128 bit value.
Use PreparedStatement.setBytes or setString to store values.

Example:
UUID

ARRAY Type

ARRAY

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

Example:
ARRAY