Data types¶

Primitive types¶

Primitive types are scalar values:

• boolean
• char
• smallint
• integer
• bigint
• real
• numeric
• double precision
• text
• ip
• timestamp with time zone
• timestamp without time zone
• date
• interval

Geographic types¶

Geographic types are nonscalar values representing points or shapes in a 2D world:

• geo_point
• geo_shape

Container types¶

Container types are nonscalar values that can contain other values:

• object
• array

character varying(n), varchar(n)¶

The character varying(n) or varchar(n) character data types represent variable length strings. All unicode characters are allowed.

The optional length specification n is a positive integer that defines the maximum length, in characters, of the values that have to be stored or cast. The minimum length is 1. The maximum length is defined by the upper integer range.

An attempt to store a string literal that exceeds the specified length of the character data type results in an error.

cr> CREATE TABLE users (id varchar, name varchar(6));
CREATE OK, 1 row affected (... sec)

cr> INSERT INTO users (id, name) VALUES ('1361', 'john doe')
SQLParseException['john doe' is too long for the text type of length: 6]

If the excess characters are all spaces, the string literal will be truncated to the specified length.

cr> INSERT INTO users (id, name) VALUES ('1', 'john     ')
INSERT OK, 1 row affected (... sec)

cr> SELECT id, name, char_length(name) AS name_length
... FROM users;
+----+------+-------------+
| id | name | name_length |
+----+------+-------------+
| 1  | john |           6 |
+----+------+-------------+
SELECT 1 row in set (... sec)

If a value is explicitly cast to varchar(n), then an over-length value will be truncated to n characters without raising an error.

cr> SELECT 'john doe'::varchar(4) AS name;
+------+
| name |
+------+
| john |
+------+
SELECT 1 row in set (... sec)

character varying and varchar without the length specifier are aliases for the text data type, see also type aliases.

text¶

A text-based basic type containing one or more characters. All unicode characters are allowed.

cr> CREATE TABLE users (name text);
CREATE OK, 1 row affected (... sec)

Floating-Point Types¶

The real and double precision data types are inexact, variable-precision numeric types. It means that these types are stored as an approximation. Therefore, storage, calculation, and retrieval of the value will not always result in an exact representation of the actual floating-point value.

For instance, the result of applying sum or avg aggregate functions may slightly vary between query executions or comparing floating-point values for equality might not always match.

cr> SELECT 0.0 / 0.0 AS a, 1.0 / 0.0 as B, 1.0 / -0.0 AS c;
+-----+----------+-----------+
| a   | b        | c         |
+-----+----------+-----------+
| NaN | Infinity | -Infinity |
+-----+----------+-----------+
SELECT 1 row in set (... sec)

cr> create table my_table3 (
...   first_column integer,
...   second_column bigint,
...   third_column smallint,
...   fourth_column double precision,
...   fifth_column real,
...   sixth_column char
... );
CREATE OK, 1 row affected (... sec)

cr> INSERT INTO my_table3 (fourth_column, fifth_column)
... VALUES ('NaN', 'Infinity');
INSERT OK, 1 row affected (... sec)

Arbitrary Precision Numbers¶

The numeric type literals store exact numeric data values and perform exact calculations on them.

This type is usually used when it is important to preserve exact precision or handle values that exceed the range of the numeric types of the fixed length. The aggregations and arithmetic operations on numeric values are much slower compared to operations on the integer or floating-point types.

The numeric type can be configured with the precision and scale. The precision of a numeric is the total count of significant digits in the unscaled numeric value. The scale of a numeric is the count of decimal digits in the fractional part, to the right of the decimal point. For example, the number 123.45 has a precision of 5 and a scale of 2. Integers have a scale of zero.

To declare the numeric type with the precision and scale use the syntax:

NUMERIC(precision, scale)

Alternatively, only the precision can be specified, the scale will be zero or positive integer in this case:

NUMERIC(precision)

Without configuring the precision and scale the numeric type value will be represented by an unscaled value of the unlimited precision:

NUMERIC

The numeric type backed internally by the Java BigDecimal class. For more detailed information about its behaviour, see BigDecimal documentation.

Timestamps¶

The timestamp types consist of the concatenation of a date and time, followed by an optional time zone.

Internally, timestamp values are mapped to the UTC milliseconds since 1970-01-01T00:00:00Z stored as bigint.

Timestamps are always returned as bigint values.

The syntax for timestamp string literals is as follows:

date-element [time-separator [time-element [offset]]]

time-separator: 'T' | ' '
date-element:   yyyy-MM-dd
time-element:   HH:mm:ss [fraction]
fraction:       '.' digit+
offset:         {+ | -} HH [:mm] | 'Z'

For more detailed information about the date and time elements, see pattern letters and symbols.

cr> select '1970-01-02T00:00:00+0100'::timestamp with time zone as ts_z,
...        '1970-01-02T00:00:00Z'::timestamp with time zone ts_z,
...        '1970-01-02T00:00:00'::timestamp with time zone ts_z,
...        '1970-01-02 00:00:00'::timestamp with time zone ts_z_sql_format;
+----------+----------+----------+-----------------+
|     ts_z |     ts_z |     ts_z | ts_z_sql_format |
+----------+----------+----------+-----------------+
| 82800000 | 86400000 | 86400000 |        86400000 |
+----------+----------+----------+-----------------+
SELECT 1 row in set (... sec)

cr> select 1.0::timestamp with time zone AS ts;
+------+
|   ts |
+------+
| 1000 |
+------+
SELECT 1 row in set (... sec)

cr> select '1970-01-02T00:00:00+0200'::timestamp without time zone as ts,
...        '1970-01-02T00:00:00+0400'::timestamp without time zone as ts,
...        '1970-01-02T00:00:00Z'::timestamp without time zone as ts,
...        '1970-01-02 00:00:00Z'::timestamp without time zone as ts_sql_format;
+----------+----------+----------+---------------+
|       ts |       ts |       ts | ts_sql_format |
+----------+----------+----------+---------------+
| 86400000 | 86400000 | 86400000 |      86400000 |
+----------+----------+----------+---------------+
SELECT 1 row in set (... sec)

time with time zone¶

The time type consists of time followed by an optional time zone.

timetz is an alias for time with time zone.

time with time zone literals can be constructed using a string literal and a cast. The syntax for string literal is as follows:

time-element [offset]

time-element: time-only [fraction]
time-only:    HH[[:][mm[:]ss]]
fraction:     '.' digit+
offset:       {+ | -} time-only | geo-region
geo-region:   As defined by ISO 8601.

Where time-only can contain optional seconds, or optional minutes and seconds, and can use : as a separator optionally.

fraction accepts up to 6 digits, as precision is in micro seconds.

Time zone syntax as defined by ISO 8601 time zone designators.

cr> select '13:59:59.999999'::timetz;
+------------------+
| 13:59:59.999999  |
+------------------+
| [50399999999, 0] |
+------------------+
SELECT 1 row in set (... sec)

cr> select '13:59:59.999999+02:00'::timetz;
+-----------------------+
| 13:59:59.999999+02:00 |
+-----------------------+
| [50399999999, 7200]   |
+-----------------------+
SELECT 1 row in set (... sec)

date¶

The date data type can be used to represent values with a year, month and a day.

To construct values of the type date you can cast a string literal or a numeric literal to date. If a numeric value is used, it must contain the number of milliseconds since 1970-01-01T00:00:00Z.

The string format for dates is YYYY-MM-DD. For example 2021-03-09. This format is the only currently supported for PostgreSQL clients.

cr> select '2021-03-09'::date as cd;
+---------------+
|            cd |
+---------------+
| 1615248000000 |
+---------------+
SELECT 1 row in set (... sec)

Interval¶

<interval_literal> ::=
    INTERVAL [ <sign> ] <string_literal> <interval_qualifier>

<interval_qualifier> ::=
    <start_field> [ TO <end_field>]

<start_field> ::= <datetime_field>
<end_field> ::= <datetime_field>

<datetime_field> ::=
      YEAR
    | MONTH
    | DAY
    | HOUR
    | MINUTE
    | SECOND

<year_month_literal> ::=
    INTERVAL [ {+ | -} ]'yy' <interval_qualifier> |
    INTERVAL [ {+ | -} ]'[ yy- ] mm' <interval_qualifier>

cr> select INTERVAL '01-02' YEAR TO MONTH AS result;
+------------------------+
| result                 |
+------------------------+
| 1 year 2 mons 00:00:00 |
+------------------------+
SELECT 1 row in set (... sec)

<day_time_literal> ::=
    INTERVAL [ {+ | -} ]'dd [ <space> hh [ :mm [ :ss ]]]' <interval_qualifier>
    INTERVAL [ {+ | -} ]'hh [ :mm [ :ss [ .nn ]]]' <interval_qualifier>
    INTERVAL [ {+ | -} ]'mm [ :ss [ .nn ]]' <interval_qualifier>
    INTERVAL [ {+ | -} ]'ss [ .nn ]' <interval_qualifier>

cr> select INTERVAL '10 23:10' DAY TO MINUTE AS result;
+-------------------------+
| result                  |
+-------------------------+
| 1 weeks 3 days 23:10:00 |
+-------------------------+
SELECT 1 row in set (... sec)

cr> select INTERVAL '1-2 3 4:5:6' AS result;
+-------------------------------+
| result                        |
+-------------------------------+
| 1 year 2 mons 3 days 04:05:06 |
+-------------------------------+
SELECT 1 row in set (... sec)

cr> select INTERVAL 'P1Y2M3DT4H5M6S' AS result;
+-------------------------------+
| result                        |
+-------------------------------+
| 1 year 2 mons 3 days 04:05:06 |
+-------------------------------+
SELECT 1 row in set (... sec)

cr> select INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds' AS result;
+-------------------------------+
| result                        |
+-------------------------------+
| 1 year 2 mons 3 days 04:05:06 |
+-------------------------------+
SELECT 1 row in set (... sec)

Temporal arithmetic¶

The following table specifies the declared types of arithmetic expressions that involve temporal operands:

Definition¶

To define a geo_shape column:

<columnName> geo_shape

A geographical index with default parameters is created implicitly to allow for geographical queries.

The default definition for the column type is:

<columnName> geo_shape INDEX USING geohash WITH (precision='50m', distance_error_pct=0.025)

There are two geographic index types: geohash (the default) and quadtree. These indices are only allowed on geo_shape columns. For more information, see Geo shape index structure.

Both of these index types accept the following parameters:

Geo shape index structure¶

Computations on very complex polygons and geometry collections are exact but very expensive. To provide fast queries even on complex shapes, CrateDB uses a different approach to store, analyze and query geo shapes.

The surface of the earth is represented as a number of grid layers each with higher precision. While the upper layer has one grid cell, the layer below contains many cells for the equivalent space.

Each grid cell on each layer is addressed in 2d space either by a Geohash for geohash trees or by tightly packed coordinates in a Quadtree. Those addresses conveniently share the same address-prefix between lower layers and upper layers. So we are able to use a Trie to represent the grids, and Tries can be queried efficiently as their complexity is determined by the tree depth only.

A geo shape is transformed into these grid cells. Think of this transformation process as dissecting a vector image into its pixelated counterpart, reasonably accurately. We end up with multiple images each with a better resolution, up to the configured precision.

Every grid cell that processed up to the configured precision is stored in an inverted index, creating a mapping from a grid cell to all shapes that touch it. This mapping is our geographic index.

The main difference is that the geohash supports higher precision than the quadtree tree. Both tree implementations support precision in order of fractions of millimeters.

Representation¶

Columns with the geo_shape type are represented and inserted as object containing a valid GeoJSON geometry object:

{
  type = 'Polygon',
  coordinates = [
     [ [100.0, 0.0], [101.0, 0.0], [101.0, 1.0], [100.0, 1.0], [100.0, 0.0] ]
  ]
}

Alternatively a WKT string can be used to represent a geo_shape as well:

'POLYGON ((5 5, 10 5, 10 10, 5 10, 5 5))'

strict¶

The column policy can be configured to be strict, rejecting any subcolumn that is not defined upfront in the schema. As you might have guessed, defining strict objects without subcolumns results in an unusable column that will always be null, which is the most useless column one could create.

Example:

cr> create table my_table12 (
...   title text,
...   author object(strict) as (
...     name text,
...     birthday timestamp with time zone
...   )
... );
CREATE OK, 1 row affected (... sec)

dynamic¶

Another option is dynamic, which means that new subcolumns can be added in this object.

Note that adding new columns to an object with a dynamic policy will affect the schema of the table. Once a column is added, it shows up in the information_schema.columns table and its type and attributes are fixed. They will have the type that was guessed by their inserted/updated value and they will always be analyzed as-is with the plain, which means the column will be indexed but not tokenized in the case of text columns.

If a new column a was added with type integer, adding strings to this column will result in an error.

Examples:

cr> create table my_table13 (
...   title text,
...   author object as (
...     name text,
...     birthday timestamp with time zone
...   )
... );
CREATE OK, 1 row affected (... sec)

which is exactly the same as:

cr> create table my_table14 (
...   title text,
...   author object(dynamic) as (
...     name text,
...     birthday timestamp with time zone
...   )
... );
CREATE OK, 1 row affected (... sec)

New columns added to dynamic objects are, once added, usable as usual subcolumns. One can retrieve them, sort by them and use them in where clauses.

ignored¶

The third option is ignored. Explicitly defined columns within an ignored object behave the same as those within object columns declared as dynamic or strict (e.g., column constraints are still enforced, columns that would be indexed are still indexed, and so on). The difference is that with ignored, dynamically added columns do not result in a schema update and the values won’t be indexed. This allows you to store values with a mixed type under the same key.

An example:

cr> CREATE TABLE metrics (
...   id TEXT PRIMARY KEY,
...   payload OBJECT (IGNORED) as (
...     tag TEXT
...   )
... );
CREATE OK, 1 row affected (... sec)

cr> INSERT INTO metrics (id, payload) values ('1', {"tag"='AT', "value"=30});
INSERT OK, 1 row affected (... sec)

cr> INSERT INTO metrics (id, payload) values ('2', {"tag"='AT', "value"='str'});
INSERT OK, 1 row affected (... sec)

cr> refresh table metrics;
REFRESH OK, 1 row affected (... sec)

cr> SELECT payload FROM metrics ORDER BY id;
+-------------------------------+
| payload                       |
+-------------------------------+
| {"tag": "AT", "value": 30}    |
| {"tag": "AT", "value": "str"} |
+-------------------------------+
SELECT 2 rows in set (... sec)

cr> SELECT id, payload FROM metrics ORDER BY payload['value']::text DESC;
+----+-------------------------------+
| id | payload                       |
+----+-------------------------------+
| 2  | {"tag": "AT", "value": "str"} |
| 1  | {"tag": "AT", "value": 30}    |
+----+-------------------------------+
SELECT 2 rows in set (... sec)

cr> SELECT sum(payload['value']::bigint) FROM metrics;
SQLParseException[Cannot cast value `str` to type `bigint`]

Inserting objects¶

{ [ ident = expr [ , ... ] ] }

{}

{ my_bool_column = true }

{ my_str_col = 'this is a text value' }

{ my_int_col = 1234, my_float_col = 5.6 }

{ my_array_column = ['v', 'a', 'l', 'u', 'e'] }

{ "CamelCaseColumn" = 'this is a text value' }

{ nested_obj_colmn = { int_col = 1234, str_col = 'text value' } }

{ my_other_column = ? }

{ id = 1, name = 'foo', tags = ['apple', 'banana', 'pear'], size = 3.1415, valid = ? }

'{}'::object

'{ "my_bool_column": true }'::object

'{ "my_str_col": "this is a text value" }'::object

'{ "my_int_col": 1234, "my_float_col": 5.6 }'::object

'{ "my_array_column": ["v", "a", "l", "u", "e"] }'::object

'{ "CamelCaseColumn": "this is a text value" }'::object

'{ "nested_obj_colmn": { "int_col": 1234, "str_col": "text value" } }'::object

Array constructor¶

Arrays can be written using the array constructor ARRAY[] or short []. The array constructor is an expression that accepts both literals and expressions as its parameters. Parameters may contain zero or more elements.

Synopsis:

[ ARRAY ] '[' element [ , ... ] ']'

All array elements must have the same data type, which determines the inner type of the array. If an array contains no elements, its element type will be inferred by the context in which it occurs, if possible.

[]
[null]
[1, 2, 3, 4, 5, 6, 7, 8]
['Zaphod', 'Ford', 'Arthur']
[?]
ARRAY[true, false]
ARRAY[column_a, column_b]
ARRAY[ARRAY[1, 2, 1 + 2], ARRAY[3, 4, 3 + 4]]

'{ val1, val2, val3 }'

cr> SELECT '{ab, CD, "CD", null, "null"}'::array(text) AS arr;
+----------------------------------+
| arr                              |
+----------------------------------+
| ["ab", "CD", "CD", null, "null"] |
+----------------------------------+
SELECT 1 row in set (... sec)

[1, 2, 3]
['Zaphod', 'Ford', 'Arthur']

Regproc¶

The object identifier alias type that is used in the pg_catalog tables for referencing functions. For more information, see PostgreSQL Object Identifier Types.

Casting a column of the regproc alias data type to text or integer results in a function name or its oid, respectively.

Regclass¶

A type for the object identifier of the pg_class table in the pg_catalog table.

Casting a column of the regclass to text or integer results in a relation name or its oid, respectively.

oidvector¶

This is a system type used to represent one or more OID values.

It looks similar to an array of integers, but doesn’t support any of the scalar functions or expressions that can be used on regular arrays.

CAST¶

A type cast specifies a conversion from one data type to another. It will only succeed if the value of the expression is convertible to the desired data type, otherwise an error is returned.

CrateDB supports two equivalent syntaxes for type casts:

cast(expression as type)
expression::type

Example usages:

cr> select cast(port['http'] as boolean) from sys.nodes limit 1;
+-------------------------------+
| cast(port['http'] AS boolean) |
+-------------------------------+
| TRUE                          |
+-------------------------------+
SELECT 1 row in set (... sec)

cr> select (2+10)/2::text AS col;
+-----+
| col |
+-----+
|   6 |
+-----+
SELECT 1 row in set (... sec)

It is also possible to convert array structures to different data types, e.g. converting an array of integer values to a boolean array.

cr> select cast([0,1,5] as array(boolean)) AS active_threads ;
+---------------------+
| active_threads      |
+---------------------+
| [false, true, true] |
+---------------------+
SELECT 1 row in set (... sec)

TRY_CAST¶

While cast throws an error for incompatible type casts, try_cast returns null in this case. Otherwise the result is the same as with cast.

try_cast(expression as type)

Example usages:

cr> select try_cast('true' as boolean) AS col;
+------+
| col  |
+------+
| TRUE |
+------+
SELECT 1 row in set (... sec)

Trying to cast a text to integer, will fail with cast if text is no valid integer but return null with try_cast:

cr> select try_cast(name as integer) AS name_as_int from sys.nodes limit 1;
+-------------+
| name_as_int |
+-------------+
|        NULL |
+-------------+
SELECT 1 row in set (... sec)

type 'string'¶

This cast operation is applied to a string literal and it effectively initializes a constant of an arbitrary type.

Example usages, initializing an integer and a timestamp constant:

cr> select integer '25' AS int;
+-----+
| int |
+-----+
|  25 |
+-----+
SELECT 1 row in set (... sec)

cr> select timestamp with time zone '2029-12-12T11:44:00.24446' AS ts;
+---------------+
| ts            |
+---------------+
| 1891770240244 |
+---------------+
SELECT 1 row in set (... sec)