Data Types

Index

INT BOOLEAN TINYINT SMALLINT BIGINT IDENTITY DECIMAL DOUBLE

REAL TIME DATE TIMESTAMP BINARY OTHER VARCHAR VARCHAR_IGNORECASE

CHAR BLOB CLOB UUID ARRAY LIST SET MAP

Details

Click on the header to switch between railroad diagram and BNF.

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

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

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

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 }

The timestamp data type. 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 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 ) ]

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

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

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

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.

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

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

LIST Type

LIST

A list of values.

Example:

LIST<int>

SET Type

SET

A set of values.

Example:

SET<int>

MAP Type

MAP

Maps keys to values.

Example:

MAP<int,varchar>