Data Types

the {{ocient}} system supports a variety of data types for sql functions and querying for information on supported data types, see data types /#data types for sql functions data type requirements for a specific function can be found in sql reference docid\ twelobi0rarxjfiot1a70 section sql statements, such as ddl and dcl commands, use generic data types numeric and string for details, see data types /#data types for sql statements data types for sql functions t he 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 table sql statement examples docid 0rmcbcyysu 0ej2rmrcqy 136,117,135,256 true false unhandled content type unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type unhandled content type unhandled content type false unhandled content type unhandled content type unhandled content type false unhandled content type unhandled content type unhandled content type false unhandled content type unhandled content type unhandled content type false unhandled content type unhandled content type unhandled content type false unhandled content type unhandled content type unhandled content type false unhandled content type unhandled content type unhandled content type false unhandled content type unhandled content type unhandled content type false unhandled content type unhandled content type unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type unhandled content type unhandled content type false unhandled content type unhandled content type unhandled content type false unhandled content type unhandled content type unhandled content type false unhandled content type unhandled content type unhandled content type false unhandled content type unhandled content type unhandled content type false unhandled content type unhandled content type unhandled content type false unhandled content type unhandled content type unhandled content type false unhandled content type unhandled content type unhandled content type false unhandled content type unhandled content type unhandled content type false unhandled content type unhandled content type unhandled content type false unhandled content type 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 the bigint data type if it is an integer with no decimal parts if the number includes decimal parts, it defaults to double string — a series of one or more characters this value defaults to the varchar data 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 to coalesce , concat , and || , and other operators numeric types double float decimal bigint int smallint tinyint geospatial types 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 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 p olygon 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 kill 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; i e 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 and operators docid 89kk83 gif3icefcy1kuw array functions and operators docid 89kk83 gif3icefcy1kuw tuple tuple is a sql type container that is meant to store elements of different types ocient supports tuple using any of the supported sql types; i e 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 or as sql array elements there is currently no support for storing nested tuples or tuples of arrays tuple functions and operators are described in the following sections tuple functions and operators docid\ xunz45zvbfsvnbhzy99v5 tuple functions and operators docid\ xunz45zvbfsvnbhzy99v5 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 and operators docid\ dwjpaeks9otz2u sav2lj matrix functions and operators docid\ dwjpaeks9otz2u sav2lj 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 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 , 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 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 a geography 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 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 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 docid ncku w2ufneog xx3esf related links date and time functions docid\ zcon ufstf4uhc5airgpg character and binary functions docid\ kolkdboplprrwxs3g6gsh query ocient docid 4ycq1d8tkfmlsacorynf6 create table sql statement examples docid 0rmcbcyysu 0ej2rmrcqy