The 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 type requirements for a specific function can be found in SQL Reference section. SQL statements, such as DDL and DCL commands, use generic data types numeric and string. For details, see Data Types for SQL Statements.Documentation Index
Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
Use this file to discover all available pages before exploring further.
Data Types for SQL Functions
The following data types are supported in table columns and queries. 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. The data types in this table apply to SQL functions.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.For examples of how to specify these default values, see Create a Table with All Data Types.| Name | Description | System Limits | Example Value |
|---|---|---|---|
| ARRAY | An array of the specified type in the format TYPE[]. 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) []\() can be used for empty arrays. For example: BIGINT[]\() |
| BIGINT | 8-byte signed integer | Minimum value: -9223372036854775808Maximum value: 9223372036854775807 | 9876543210 |
| BINARY(N) or HASH(N) | Fixed-length binary array with length N | Maximum memory allocation: 512MiB For HASH(N), the maximum value is N repeats of 0xFF, and the minimum value is N repeats of 0x00. | BINARY(4): '0x01234567', 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-01Maximum value: 9999-12-31 | '2020-02-02','2000-01-01' |
| DECIMAL(P,S) | Exact numerical with precision P and scale S | Minimum value: -9999999999999999999999999999999Maximum value: 9999999999999999999999999999999 | 123.45 |
| DOUBLE | 8-byte double precision floating-point number | Minimum value: -1.7977 x 10^308 Maximum value: 1.7977 x 10^308 | 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: -2147483648Maximum value: 2147483647 | 123456789 |
| IPV4 | 4-byte Internet Protocol version 4 | Minimum value:0.0.0.0Maximum value: 255.255.255.255 | '127.0.0.1' |
| IP | 16-byte Internet Protocol version 6 (can also hold IPV4) | Minimum value:0000:0000:0000:0000:0000:0000:0000:0000Maximum value: ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | '0123:4567:89ab:cdef:0123:4567:89ab:cdef', '1111::0', '127.0.0.1' |
| LINESTRING | 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 Maximum value: 16,384 elements | A Matrix[2][2]: { {0, 0}, {0, 0} }A Matrix [2][4]: { {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: -3.4028 x 10^38 Maximum value: 3.4028 x 10^38 | 2.718 |
| SMALLINT | 2-byte signed integer | Minimum value: -32768Maximum value: 32767 | 32767 |
| TIME | 8-byte time of day in nanoseconds | Minimum value: 00:00:00.000000000Maximum value: 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.145224192Maximum value: 2262-04-11 23:47:16.854775807 | '2000-01-02 12:34:45' |
| TINYINT or BYTE | 1-byte signed integer | Minimum value: -128Maximum value: 127 | 127 |
| TUPLE<<type1, type2, …>> | Tuple of elements of different types. | Maximum memory allocation: 512MiB | tuple<<INT,INT>>(1,2)An Array of Tuples: tuple<<INT,INT>>[](tuple<<INT,INT>>(1,2), tuple<<INT,INT>>(3,4)) |
| UUID | 16-byte universally unique identifier | Minimum value:00000000-0000-0000-0000-000000000000Maximum value: 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:numeric— Any numeric value. This value defaults to theBIGINTdata type if it is an integer with no decimal parts. If the number includes decimal parts, it defaults toDOUBLE.string— A series of one or more characters. This value defaults to theVARCHARdata type.
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 toCOALESCE, CONCAT, and ||, and other operators.
Numeric Types
SQL
SQL
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
- TYPE[]: The inner values of the array are subject to the size limits of the inner type.
- LINESTRING
- POLYGON
- VARBINARY(N)
- VARCHAR(N)
The database ignores length of N for CHAR, VARCHAR, and VARBINARY. The database uses N for SQL conformance only.
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: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: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:Geospatial Data Types
Ocient supports three different geospatial geographies: POLYGONS, LINESTRINGS, and POINTS.- A
POLYGONcan be constructed with a closed LINESTRING or an outer shell and an array of inner rings. - A
LINESTRINGrepresents a series of POINTS connected by line segments. It can be constructed with either LINESTRINGS or POINTS. - A
POINTrepresents a point in space defined by an (x, y)/(long, lat) coordinate pair.
If a counter-clockwise exterior polygon has holes, the holes should be clockwise-oriented.
Ensure that both the data and polygon literals are oriented in the way you intend when you use them in queries.
Geography Equivalent
Many other geospatial implementations use an object-oriented approach to geospatial data types by implementing aGEOGRAPHY union type that might contain any combination of these types: POINT, LINESTRING, or POLYGON.
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 asst_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.
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.