Data Types
Index
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
| ( precisionInt |
| ) |
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
| ||||||||||
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
|
|
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
|
|
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 |
|
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
|
|
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
|
|
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 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
GEOMETRY Type
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