---
title: Data Types
slug: v6KYW2eYK-ItvDs2WtBn3
description: Review the various data types available in Ocient, optimized for efficient storage and retrieval.
docTags: 
createdAt: Thu Jul 21 2022 17:05:13 GMT+0000 (Coordinated Universal Time)
updatedAt: Mon Mar 30 2026 18:52:35 GMT+0000 (Coordinated Universal Time)
---

The Ocient® System supports a variety of data types for SQL functions and querying. For information on supported data types, see [Data Types for SQL Functions](./#data-types-for-sql-functions). Data type requirements for a specific function can be found in [SQL Reference](<././SQL Reference.md>) section.&#x20;

SQL statements, such as DDL and DCL commands, use generic data types numeric and string. For details, see [Data Types for SQL Statements](./#data-types-for-sql-statements).&#x20;

# Data Types for SQL Functions

The following data types are supported in table columns and queries.&#x20;

This table provides more information on the data types supported by the Ocient System. The example value column gives SQL input examples for the respective data types. &#x20;

The data types in this table apply to SQL functions.&#x20;

:::hint{type="info"}
The default column values for the Tuple, Matrix, and Array values in a `CREATE TABLE` statement require an alternate syntax than the examples shown in this table.&#x20;

For examples of how to specify these default values, see [Create a Table with All Data Types](<././Database Administration/Schema Design/CREATE TABLE SQL Statement Examples.md>).&#x20;
:::

| **Name**                                     | **Description**                                                                                                                                                               | **System Limits**                                                                                                                                 | **Example Value**                                                                                                         |
| -------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------- |
| ARRAY                                        | An array of the specified type in the format `TYPE[]`. <br />The `TYPE` type can be any supported data type besides ARRAY. For example, `INT[]` creates an array of integers. | Maximum memory allocation: 512MiB                                                                                                                 | `INT[](1,2,3)` <br />`[]()` can be used for empty arrays. For example: `BIGINT[]()`                                       |
| BIGINT                                       | 8-byte signed integer                                                                                                                                                         | Minimum value: `-9223372036854775808`<br /><br />Maximum value:<br />`9223372036854775807`                                                        | `9876543210`                                                                                                              |
| BINARY(N) or HASH(N)                         | Fixed-length binary array with length N                                                                                                                                       | Maximum memory allocation: 512MiB<br /><br />For HASH(N), the maximum value is N repeats of `0xFF`, and the minimum value is N repeats of `0x00`. | BINARY(4): `'0x01234567'`, <br />BINARY(3): `'0xabcdef'`                                                                  |
| BOOLEAN                                      | 1-byte logical Boolean value                                                                                                                                                  | None                                                                                                                                              | `TRUE`, `FALSE`                                                                                                           |
| CHARACTER(N) or CHAR(N) or CHAR or CHARACTER | Variable-length character string. Length N is only for compatibility. The Ocient System does not use this argument.                                                           | Maximum memory allocation: 512MiB                                                                                                                 | CHAR(16): `'This is a string'`                                                                                            |
| DATE                                         | 4-byte calendar date (year, month, day)                                                                                                                                       | Minimum value: `0001-01-01`<br /><br />Maximum value:<br />`9999-12-31`                                                                           | `'2020-02-02'`,<br />`'2000-01-01'`                                                                                       |
| DECIMAL(P,S)                                 | Exact numerical with precision P and scale S                                                                                                                                  | Minimum value: `-9999999999999999999999999999999`<br /><br />Maximum value: `9999999999999999999999999999999`                                     | `123.45`                                                                                                                  |
| DOUBLE                                       | 8-byte IEEE® double precision floating-point number                                                                                                                           | Minimum value:<br />-1.7977 x 10^308<br /><br />Maximum value:<br />1.7977 x 10^308<br /><br />                                                   | `3.141592`                                                                                                                |
| FUNCTION                                     | Lambda function or user-defined function                                                                                                                                      | None                                                                                                                                              | `(x int, y int) -> CASE WHEN x = y THEN 0 WHEN INT(COALESCE(x, 1000)) < INT(COALESCE(y, 1000)) THEN -1 ELSE 1 END`        |
| INT                                          | 4-byte signed integer                                                                                                                                                         | Minimum value: `-2147483648`<br /><br />Maximum value:<br />`2147483647`                                                                          | `123456789`                                                                                                               |
| IPV4                                         | 4-byte Internet Protocol version 4                                                                                                                                            | Minimum value:<br />`0.0.0.0`<br /><br />Maximum value:<br />`255.255.255.255`                                                                    | `'127.0.0.1'`                                                                                                             |
| IP                                           | 16-byte Internet Protocol version 6 (can also hold IPV4)                                                                                                                      | Minimum value:<br />`0000:0000:0000:0000:0000:0000:0000:0000`<br /><br />Maximum value:<br />`ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff`            | `'0123:4567:89ab:cdef:0123:4567:89ab:cdef'`, <br />`'1111::0'`, <br />`'127.0.0.1'`                                       |
| LINESTRING<br />                             | Geometric type composed of N points                                                                                                                                           | Maximum memory allocation: 512MiB                                                                                                                 | `'LINESTRING(0 0,2 0)'`                                                                                                   |
| MATRIX                                       | A one or two-dimensional mathematical matrix used for matrix calculations.                                                                                                    | Minimum value: None<br /><br />Maximum value: 16,384 elements                                                                                     | A Matrix\[2]\[2]: <br />`{ {0, 0}, {0, 0} }`<br />A Matrix \[2]\[4]: <br />`{ {0, 0, 0, 0}, {0, 0, 0, 0} }`               |
| POINT                                        | Geometric point in 2 dimensions                                                                                                                                               | None                                                                                                                                              | `'POINT(-87.6410 41.8841)'`                                                                                               |
| POLYGON                                      | Geometric type composed of N closed linestrings                                                                                                                               | Maximum memory allocation: 512MiB                                                                                                                 | `'POLYGON((0 0,2 0,5 5,0 2,0 0), (0 0,1 0,2 2,0 2,0 0))'`                                                                 |
| FLOAT                                        | 4-byte IEEE single precision floating-point number                                                                                                                            | Minimum value:<br />-3.4028 x 10^38<br /><br />Maximum value:<br />3.4028 x 10^38                                                                 | `2.718`                                                                                                                   |
| SMALLINT                                     | 2-byte signed integer                                                                                                                                                         | Minimum value: `-32768`<br /><br />Maximum value:<br />`32767`                                                                                    | `32767`                                                                                                                   |
| TIME                                         | 8-byte time of day in nanoseconds                                                                                                                                             | Minimum value: `00:00:00.000000000`<br /><br />Maximum value:<br />`23:59:59.999999999`                                                           | `'12:34:56.012345678'`                                                                                                    |
| TIMESTAMP                                    | 8-byte date and time in nanoseconds with no associated time zone.                                                                                                             | Minimum value: `1677-09-21 00:12:43.145224192`<br /><br />Maximum value:<br />`2262-04-11 23:47:16.854775807`                                     | `'2000-01-02 12:34:45'`<br />                                                                                             |
| TINYINT or BYTE                              | 1-byte signed integer                                                                                                                                                         | Minimum value: `-128`<br /><br />Maximum value:<br />`127`                                                                                        | `127`                                                                                                                     |
| TUPLE\<\<type1, type2, …​>>                  | Tuple of elements of different types.                                                                                                                                         | Maximum memory allocation: 512MiB                                                                                                                 | `tuple<<INT,INT>>(1,2)`<br />An Array of Tuples: <br />`tuple<<INT,INT>>[](tuple<<INT,INT>>(1,2), tuple<<INT,INT>>(3,4))` |
| UUID                                         | 16-byte universally unique identifier                                                                                                                                         | Minimum value:<br />`00000000-0000-0000-0000-000000000000`<br /><br />Maximum value:<br />`ffffffff-ffff-ffff-ffff-ffffffffffff`                  | `'01234567-89ab-cdef-1357-0123456789ab'`                                                                                  |
| VARBINARY(N)                                 | Variable-length binary array with maximum length of N                                                                                                                         | Maximum memory allocation: 512MiB                                                                                                                 | VARBINARY(6): `'0xaabbccddeeff'`                                                                                          |
| VARCHAR(N)                                   | Variable-length character string with maximum length of N                                                                                                                     | Maximum memory allocation: 512MiB                                                                                                                 | VARCHAR(4): `'This is a variable length string'`                                                                          |

# Data Types for SQL Statements

DDL, DCL, and General SQL Syntax statements define parameter data types as either numeric or string. These data types are simplified terms that follow these rules:&#x20;

- `numeric` — Any numeric value. This value defaults to the `BIGINT` data type if it is an integer with no decimal parts. If the number includes decimal parts, it defaults to `DOUBLE`.&#x20;
- `string` — A series of one or more characters. This value defaults to the `VARCHAR` data type.&#x20;

# Data Type Considerations

Integer literals in SQL statements are of type BIGINT unless explicitly cast to another type.

Floating point literals in SQL statements are of type DOUBLE unless explicitly cast to another type.

TRUE and FALSE can be used in SQL statements as valid BOOLEAN literals.

Floating point values must be strictly numeric. Infinity and NaN values are unsupported.

The decimal type supports a maximum precision of 31 and a scale of one less than the precision.

Interval types can only be used within an expression. If the final result of an outermost expression is an interval type, the Ocient System automatically converts it to a BIGINT and the value loses the units information.

- "yyyy" : The year as a four-digit number.
- "MM" : The month, from 01 through 12.
- "dd" : The day of the month, from 01 through 31.
- "HH" : The hour, using a 24-hour clock from 00 to 23.
- "mm" : The minute, from 00 through 59.
- "ss" : The second, from 00 through 59.
- "FFFFFFFFF" : If non-zero, the billionths of a second in a date and time value.

# Data Type Compatibility

The Ocient System casts data types to the highest common type when possible in expressions to produce an output of that type. These data-type promotions occur in these hierarchies. This promotion often applies to `COALESCE`, `CONCAT`, and `||`, and other operators. &#x20;

**Numeric Types**

```sql
DOUBLE
FLOAT
DECIMAL
BIGINT
INT
SMALLINT
TINYINT
```

**Geospatial Types**

```sql
POLYGON
LINESTRING
POINT
```

Geospatial functions might have semantic differences when you execute these functions with different types that have higher precedence than the type promotion of its arguments.&#x20;

# Size Limits

Variable-length columns have limits to how large the value can be.

Data types with a 124 KiB size limit:

- TUPLE\<\<type1, type2, …>> : Tuple value with a VARCHAR(N) or VARBINARY(N) type

Data types with a 512 MiB size limit:

- TYPE\[]: The inner values of the array are subject to the size limits of the inner type.
- LINESTRING
- POLYGON
- VARBINARY(N)
- VARCHAR(N)

:::hint{type="info"}
The database ignores length of N for CHAR, VARCHAR, and VARBINARY. The database uses N for SQL conformance only.&#x20;
:::

:::hint{type="info"}
When you query data, if the database must analyze a value that exceeds 124 KiB at query execution time, the system increases memory usage. Large computed values are non-freeable during query execution, and the system cannot offload to temporary disk. Queries that operate on computed large values with many rows can cause the system to run out of memory and result in the killing of the query.
:::

# Array

Array is a SQL type container that stores multiple elements of the same SQL type. Arrays can have zero or any number of values. For instance, an INT array can contain many integers, including NULL. Arrays in Ocient can store any SQL type: IPV4, IP, BOOLEAN, BYTE, SHORT, INT, LONG, FLOAT, DOUBLE, TIME, TIMESTAMP, CHAR, DATE, BINARY, HASH, POINT, UUID, DECIMAL, and TUPLE.

Arrays have special functions and operators that are described in the following sections:

- [Array Functions](<./Array Functions and Operators.md>)&#x20;
- [Array Operators](<./Array Functions and Operators.md>)&#x20;

# Tuple

Tuple is a SQL type container that is meant to store elements of different types. Ocient supports tuples using any of the supported SQL types: IPV4, IP, BOOLEAN, BYTE, SHORT, INT, BIGINT, FLOAT, DOUBLE, TIME, TIMESTAMP, CHAR, DATE, BINARY, HASH, POINT, UUID, and DECIMAL. In Ocient, tuples can either exist as single columns, SQL array elements, nested tuples, or tuples of arrays.

Tuple functions and operators are described in the following sections:

- [Tuple Functions](<./Tuple Functions and Operators.md>)
- [Tuple Operators](<./Tuple Functions and Operators.md>)&#x20;

# Matrix

Matrix is a SQL-type container. It is a fixed-size, one or two-dimensional array (mathematical matrix) of the same fixed-size SQL data types. Currently, Matrix only supports storing doubles. Matrix dimensions must be non-zero.

Matrix functions and operators are described in the following sections:

- [Matrix Functions](<./Matrix Functions and Operators.md>)&#x20;
- [Matrix Operators](<./Matrix Functions and Operators.md>)&#x20;

# Geospatial Data Types

Ocient supports three different geospatial geographies: POLYGONS, LINESTRINGS, and POINTS.

- A `POLYGON` can be constructed with a closed LINESTRING or an outer shell and an array of inner rings.
- A `LINESTRING` represents a series of POINTS connected by line segments. It can be constructed with either LINESTRINGS or POINTS.
- A `POINT` represents a point in space defined by an (x, y)/(long, lat) coordinate pair.

Polygon semantics in the Ocient System follow the [OGC standard (ISO 19125-1) for LinearRings](https://www.iso.org/standard/40114.html), where the exterior and holes can be meaningfully oriented either counter-clockwise or clockwise. To conceptualize this, imagine walking along the exterior of the polygon in the order of its defined points, so everything to your left is the "interior" of the polygon. A counter-clockwise polygon is the typical orientation where, using an example of a state boundary, the interior of the state corresponds to the interior of the polygon. If you define the state boundary using the clockwise orientation, the interior of the polygon represents everything except the state.&#x20;

:::hint{type="info"}
If a counter-clockwise exterior polygon has holes, the holes should be clockwise-oriented.&#x20;
:::

:::hint{type="info"}
Ensure that both the data and polygon literals are oriented in the way you intend when you use them in queries.&#x20;
:::

## Geography Equivalent

Many other geospatial implementations use an object-oriented approach to geospatial data types by implementing a `GEOGRAPHY` union type that might contain any combination of these types: `POINT`, `LINESTRING`, or `POLYGON`.&#x20;

Ocient supports `GEOGRAPHY` slightly differently by using `POLYGON` as an implicit union type. This container type means unclosed `POLYGON` types with no holes are equivalent to `LINESTRING` types, and that a `POLYGON` might contain a single `POINT`. This definition of a `POLYGON` differs from other implementations, where such `POLYGON` types would be considered degenerate. During loading, Ocient is able to convert incoming `GEOGRAPHY` data to the appropriate `POLYGON` equivalent. Ocient geospatial functions appropriately handle these cases as needed.

## Spatial Reference Identifier (SRID)

Similar to geography union types, many other geospatial implementations define an attribute table of column identifiers and an associated SRID. The system uses the SRID lookup to define semantics of functions such as `st_area`, which uses different calculations if the specified polygon is planar, defined on an ideal sphere, or on a spheroid. The identifiers also specify units of measure and the number of variables used to define a point in the reference system. However, Ocient only supports the storage of latitude and longitude coordinates for its geography types, and uses only GCS WGS 84 (EPSG code 4326) semantics. Rather than applying a transformation like you might in other implementations to change units or model accuracy, the geospatial measurement functions in the Ocient System allow you to specify the units of measure directly, as well as whether to use a faster idealized sphere model of the Earth, or a more accurate spheroidal model.&#x20;

Ensure that you set the units and spheroidal model correctly when performing critical measurements. Also, be careful when you export data from other databases to Ocient so that the incoming data is in GCS WGS 84 format. Ensure that you use the Loading and Transformation pipeline to prepend or strip SRID definitions from EWKT or EWKB-formatted data. Internally, the Ocient System has no concept of SRIDs, however it does support parsing and emitting SRID-prepended data for compatibility with external tools. When the database parses SRID-prepended data, the system ignores the SRID component and parses the data as a GCS WGS 84 defined geography.

## Geospatial Functions

Each geospatial geography has a number of functions and operators that can be used in SQL queries to perform analyses. Some functions apply to specific geography types. For more function and operator specifics, see [Geospatial Functions](<./Geospatial Functions.md>).

# Related Links

[Date and Time Functions](<./Date and Time Functions.md>)

[Character and Binary Functions](<./Character and Binary Functions.md>)

[Query Ocient](<././Query Ocient.md>)

[CREATE TABLE SQL Statement Examples](<././Database Administration/Schema Design/CREATE TABLE SQL Statement Examples.md>)
