Data types

In CrateDB, data types define what kind of values can be stored in a column, how those values are stored internally, and how they behave in queries.

When you create a table with CREATE TABLE, you can explicitly set the data type for each column. Choosing the correct type is important for:

• Data integrity – ensures only valid values are stored.

• Storage efficiency – controls how much space is required.

• Query performance – affects indexing, comparisons, and functions.

Note: Data type names are reserved keywords in SQL. If you want to use them as column names, escape them with double quotes.

---

Supported Types

CrateDB supports a broad set of primitive and complex data types. The tables below provide a quick summary; see individual sections for details and examples.

Type	Description	Example
BOOLEAN	Logical true/false values.	true, false
VARCHAR(n), TEXT	Variable-length Unicode strings.	'foobar'
CHARACTER(n), CHAR(n)	Fixed-length, blank-padded Unicode strings.	'foo'
SMALLINT, INTEGER, BIGINT	Signed integers of different ranges.	12345, -12345
REAL	Inexact single-precision floating point.	3.4028235e+38
DOUBLE PRECISION	Inexact double-precision floating point.	1.7976931348623157e+308
NUMERIC(p, s)	Exact fixed-point numbers with user-defined precision and scale.	123.45
TIMESTAMP WITH TIME ZONE	Date/time with time zone.	'1970-01-02T00:00:00+01:00'
TIMESTAMP WITHOUT TIME ZONE	Date/time without time zone.	'1970-01-02T00:00:00'
DATE	Calendar date in UTC.	'2021-03-09'
TIME	Time of day (milliseconds since midnight) with optional time zone offset.	'13:00:00', '13:00:00+01:00'
BIT(n)	Sequence of bits.	B'00010010'
IP	IPv4 or IPv6 address.	'127.0.0.1', '0:0:0:0:0:ffff:c0a8:64'
OBJECT	JSON-like key/value structure.	{ "foo": "bar" }
ARRAY	Ordered list of values.	[1, 2, 3]
GEO_POINT	Latitude/longitude coordinate pair.	[13.46738, 52.50463]
GEO_SHAPE	GeoJSON geometry object.	{ "type": "Polygon", "coordinates": [...] }
FLOAT_VECTOR(n)	Fixed-length array of floating-point values.	[3.14, 42.21]
ROW	Composite type with multiple fields (tuple-like).	(no literal syntax yet)

---

Ranges and Widths

This table lists the internal storage width, allowed ranges, and important notes for each type.

Type	Width	Range	Notes
BOOLEAN	1 byte	true / false	Logical values
VARCHAR(n), TEXT	variable	1 → 2^31-1 chars [1]	Unicode, variable length
CHAR(n)	variable	1 → 2^31-1 chars [1]	Unicode, fixed length
SMALLINT	2 bytes	-32,768 → 32,767	Small integers
INTEGER	4 bytes	-2^31 → 2^31-1	Standard integers
BIGINT	8 bytes	-2^63 → 2^63-1	Large integers
NUMERIC	variable	Up to 131,072 digits before decimal, 16,383 after	Exact decimal
REAL	4 bytes	~6 decimal digits	Inexact
DOUBLE PRECISION	8 bytes	~15 decimal digits	Inexact
TIMESTAMP	8 bytes	292275054BC → 292278993AD	With/without TZ
DATE	8 bytes	same as TIMESTAMP	UTC
TIME	12 bytes	00:00:00 → 23:59:59.999999	Optional TZ
BIT(n)	variable	1 → 2^31-1 bits	Bit sequences
IP	8 bytes	IPv4/IPv6	Stored as BIGINT
OBJECT	variable	~2^31 elements	Nested structures
ARRAY	variable	~2^31 elements	Any element type
GEO_POINT	16 bytes	lat/long as DOUBLE PRECISION	Geographic point
GEO_SHAPE	variable	coordinates as DOUBLE PRECISION	GeoJSON geometry
FLOAT_VECTOR(n)	n	1 → 2048 elements	Floating-point vector

[1] When using the column store, the max size for text columns is 32,766 bytes unless indexing is disabled or the column is set to not use the column store.

---

Precedence and Type Conversion

When different data types are combined in an expression, CrateDB automatically converts the type with lower precedence to the one with higher precedence (if supported). If no implicit conversion exists, an error is returned.

Precedence order (highest → lowest):

1. Custom types (BIT, FLOAT_VECTOR)

2. GEO_SHAPE

3. JSON

4. OBJECT

5. GEO_POINT

6. Record (internal)

7. ARRAY

8. Numeric types

9. DOUBLE PRECISION

10. REAL

11. IP

12. BIGINT

13. TIMESTAMP WITHOUT TIME ZONE

14. TIMESTAMP WITH TIME ZONE

15. DATE

16. INTERVAL

17. REGCLASS

18. REGPROC

19. INTEGER

20. TIME WITH TIME ZONE

21. SMALLINT

22. BOOLEAN

23. "CHAR"

24. TEXT

25. CHARACTER

26. NULL