Supported types
CockroachDB supports the following data types.
| Type | Description | Example |
|---|---|---|
ARRAY |
A 1-dimensional, 1-indexed, homogeneous array of any non-array data type. | {"sky","road","car"} |
BIT |
A string of binary digits (bits). | B'10010101' |
BOOL |
A Boolean value. | true |
BYTES |
A string of binary characters. | b'\141\061\142\062\143\063' |
COLLATE |
The COLLATE feature lets you sort STRING values according to language- and country-specific rules, known as collations. |
'a1b2c3' COLLATE en |
DATE |
A date. | DATE '2016-01-25' |
ENUM |
A user-defined data type comprised of a set of static values. | ENUM ('club, 'diamond', 'heart', 'spade') |
DECIMAL |
An exact, fixed-point number. | 1.2345 |
FLOAT |
A 64-bit, inexact, floating-point number. | 1.2345 |
INET |
An IPv4 or IPv6 address. | 192.168.0.1 |
INT |
A signed integer, up to 64 bits. | 12345 |
INTERVAL |
A span of time. | INTERVAL '2h30m30s' |
NULL |
The undefined value. | NULL |
JSONB |
JSON (JavaScript Object Notation) data. | '{"first_name": "Lola", "last_name": "Dog", "location": "NYC", "online" : true, "friends" : 547}' |
OID |
An unsigned 32-bit integer. | 4294967295 |
SERIAL |
A pseudo-type that combines an integer type with a DEFAULT expression. |
148591304110702593 |
STRING |
A string of Unicode characters. | 'a1b2c3' |
TIMETIMETZ |
TIME stores a time of day in UTC.TIMETZ converts TIME values with a specified time zone offset from UTC. |
TIME '01:23:45.123456'TIMETZ '01:23:45.123456-5:00' |
TIMESTAMPTIMESTAMPTZ |
TIMESTAMP stores a date and time pairing in UTC.TIMESTAMPTZ converts TIMESTAMP values with a specified time zone offset from UTC. |
TIMESTAMP '2016-01-25 10:10:10'TIMESTAMPTZ '2016-01-25 10:10:10-05:00' |
TSQUERY |
A list of lexemes and operators used in full-text search. | 'list' & 'lexem' & 'oper' & 'use' & 'full' & 'text' & 'search' |
TSVECTOR |
A list of lexemes with optional integer positions and weights used in full-text search. | 'full':13 'integ':7 'lexem':4 'list':2 'option':6 'posit':8 'search':15 'text':14 'use':11 'weight':10 |
UUID |
A 128-bit hexadecimal value. | 7f9c24e8-3b12-4fef-91e0-56a2d5a246ec |
VECTOR |
A fixed-length array of floating-point numbers. | [1.0, 0.0, 0.0] |
Data type conversions and casts
CockroachDB supports explicit type conversions using the following methods:
<type> 'string literal', to convert from the literal representation of a value to a value of that type. For example:DATE '2008-12-21',INT '123', orBOOL 'true'.<value>::<data type>, or its equivalent longer formCAST(<value> AS <data type>), which converts an arbitrary expression of one built-in type to another (this is also known as type coercion or "casting"). For example:NOW()::DECIMAL,VARIANCE(a+2)::INT.Tip:To create constant values, consider using a type annotation instead of a cast, as it provides more predictable results.
Other built-in conversion functions when the type is not a SQL type, for example
from_ip(),to_ip()to convert IP addresses betweenSTRINGandBYTESvalues.
CockroachDB also supports implicit casting from string literals to arrays of all data types except the following:
BYTESENUMJSONBSERIALBox2D(spatial type)GEOGRAPHY(spatial type)GEOMETRY(spatial type)
For an example, see Implicit casting to ARRAYs.
You can find each data type's supported conversion and casting on its respective page in its section Supported casting & conversion.
