Understanding Data Types

{{ocient}} supports many common data types such as int, short, date, and timestamp as well as complex types like array and tuple and specialized data types like point and linestring used for geospatial analysis for detailed information about supported data types, refer to the data types docid\ salunscyvkcxpgzh3m0hd page all of the following data types can be used as types for columns in tables and for queries when used in queries, scalar data conversion functions docid\ nujw0mginjx8o7schnzrq can be used to manipulate data types and various functions and aggregates can be run depending on the data type for example, numeric types can be summed, averaged or be processed with other statistical functions, and character types can be trimmed, manipulated, or aggregated into a single string basic data types such as int, boolean, and varchar behave similarly to other databases complex types such as arrays and tuples can be queried and inspected like any other column and also have special operators used to interrogate the sets of data numeric and boolean data types numeric and boolean types are used to represent data with different precision and scale integer values are supported from a 1 byte signed int up to an 8 byte signed int all integers are signed in ocient floating point values are possible as either single or double precision, and exact decimal precision is available with the decimal type the boolean type is used to represent true or false in the database all numeric types are fixed length data types bigint — 8 byte signed integer int — 4 byte signed integer smallint — 2 byte signed integer tinyint — 1 byte signed integer (alias byte ) float — 4 byte single precision floating point number (alias real or single precision ) double — 8 byte double precision floating point number (alias double precision ) decimal(p,s) — exact numerical decimal value with precision p and scale s boolean — 1 byte logical boolean representing true or false learn more about working with numeric data types in the following sections math functions and operators docid\ qmny2tfsokr3y4u6gaxxs scalar data conversion functions docid\ nujw0mginjx8o7schnzrq aggregate functions docid 72ic0blcf uqzlzeq1 j5 formatting functions docid\ sckysplqipva5pazgc0w9 character and binary types character types represent text and character data both fixed length and variable length options are available fixed length provides some benefits to the database storage and query engine when applicable, but indexes are available that target variable length character columns binary types represent byte array data char(n) — variable length character string with length n (alias character(n) ) varchar(n) — variable length character string with a maximum length of n binary(n) — fixed length binary array with length n (alias hash(n) ) varbinary(n) — variable length binary array with maximum length n (alias hash(n) ) the database ignores length of n for char, varchar, and varbinary the database uses n for sql conformance only learn more about working with character and binary data types in the following sections character / binary functions docid\ owrhxf8cezv2io8xd au6 scalar data conversion functions docid\ nujw0mginjx8o7schnzrq sorted aggregate functions docid\ tsu4axvmvvzzdmkyogrwj date and time types date and time types are used to represent time in ocient timestamps take on special capabilities when defined as a {{timekey}} on a table this is described in more detail in timekeys and clustering keys docid\ oxu8ziws8csyc5h7l4xxm ocient provides many functions to operate on time series data date — 4 byte calendar date representing year, month, day timestamp — 8 byte date and time with nanosecond precision in utc time zone time — 8 byte time of day in nanoseconds learn more about working with date and time data types in the following sections date / time functions docid\ ogk0mhshcuqlakjb6f0nr window aggregate functions docid\ lnzekaijm5m6ltgz6u2i1 formatting functions docid\ sckysplqipva5pazgc0w9 networking and unique identifier types ocient provides data types for processing of ip addresses and uuids that allow schemas to leverage improved searching and analysis of both types as well as easier loading of structured data into these types the ip type is designed for ip v6 and can handle ip v4 addresses as well ipv4 — 4 byte internet protocol v4 address ip — 16 byte internet protocol v6 address uuid — 16 byte universally unique identifier (e g , 01234567 89ab cdef 1357 0123456789ab ) geospatial data types geospatial analysis is a rich capability in ocient enabled by special data types and functions the foundation rests on point, linestring, and polygon types that represent respective points and shapes on the earth from these and composites of these types, many proximity, intersection, and computational geospatial analytics can be performed at an incredible scale point — geometric point in 2 dimensions linestring — geometric type composed of n points polygon — geometric type composed of n closed linestrings learn more about working with geospatial types in the geospatial functions docid\ hpngsvdeojhofhdmpyrmh section complex types arrays and tuples are sql container types used to store other sql types these allow for complex schemas to be represented as a column in a table instead of separate, normalized tables in some situations, using arrays, tuples, or arrays of tuples can lead to significant performance benefits use the matrix type to store one or two dimensional arrays of fixed size data types for matrix computations array — an array of any supported type other than array matrix — a one or two dimensional mathematical matrix used for matrix calculations tuple — a tuple of elements of different types tuple is similar to a structure in other programming languages you can include tuples in arrays learn more about complex data types in the following sections array functions and operators docid\ vyvbrjsxibucjxjqvvcad tuple functions and operators docid\ tq8vd4pbhgt3c1 my ym matrix functions and operators docid\ fuwszzewg80 kkiey6 uo sorted aggregate functions docid\ tsu4axvmvvzzdmkyogrwj related links general sql syntax docid\ ijazgahmd u0x4gsmz8e1 sql reference docid\ eaohxmozywvukdfizfpel