Skip to main content

Built-in Data Types

The compiler supports the following SQL data types:

NameDescriptionAliases
BOOLEANA boolean valueBOOL
TINYINT8-bit signed integer using two's complement.
SMALLINT16-bit signed integer using two's complement.INT2
INTEGER32-bit signed integer using two's complement.INT, SIGNED, INT4
BIGINT64-bit signed integer using two's complement.INT8, INT64
DECIMAL(precision, scale)A high precision fixed-point type, with a precision (total number of decimal digits) and a scale (number of decimal digits after period).DEC, NUMERIC, NUMBER
REALIEEE 32-bit floating point numberFLOAT4, FLOAT32
DOUBLEIEEE 64-bit floating point numberFLOAT8, FLOAT64
VARCHAR(n)A string value with maximum fixed width. Trailing spaces are removed when converting a value to this type.CHARACTER VARYING(n)
CHAR(n)A string value with a fixed width. Values are truncated if longer, or padded with spaces if shorter, to be brought to the specified size.CHARACTER(n)
VARCHARA string of unlimited length. Trailing spaces are removed when converting a CHAR(n) value to this type.STRING, TEXT
BINARY(n)A byte string with a fixed width.
VARBINARYA byte string with an unlimited width.BYTEA
NULLA type comprising only the NULL value.
INTERVALA SQL interval. Two types of intervals are supported: long intervals (comprising years and months), and short intervals, comprising days, hours, minutes, seconds.
TIMEA time of the day.
TIMESTAMPA value containing a date and a time without a timezone.DATETIME
DATEA date value.
GEOMETRYA geographic data type (only rudimentary support at this point).
ARRAYAn array with element of the specified type. Used as a suffix for another type (e.g., INT ARRAY)
MAPA map with keys and values of specified types. The syntax is MAP<KEYTYPE, VALUETYPE>
VARIANTA dynamically-typed value that can wrap any other SQL type
  • For DECIMAL types: 23.456 has a precision of 5 and a scale of 3. If scale is missing it is assumed to be 0.

  • A suffix of NULL or NOT NULL can be appended to a type name to indicate the nullability. A type with no suffix is not nullable by default.

  • The FLOAT type is not supported. Please use REAL or DOUBLE instead. Various SQL dialects do not agree on the size of the FLOAT type, so we have decided to prohibit its use to avoid subtle bugs.

  • INTERVAL and NULL types are not supported in table schemas or user-defined types as field types. They can only used in expressions.

  • VARIANT is used to implement JSON. See JSON support Currently VARIANT values are not supported in table or output views; they can only be used in intermediate computations.

Computations on nullable types

A type is nullable if it can represent the NULL value. For input tables the nullability of a column is declared explicitly. For intermediate results and output views the compiler infers the nullability of each column using type inference rules.

Most SQL operations are defined for nullable types. Our compiler follows the SQL standard in this respect. Most operations (e.g., +), when applied a NULL operand will produce a NULL value.

User-defined structures

Users can declare new structure types. Such types can be used for columns, record fields, user-defined function parameters or results.

For example, we can declare types address_typ and employee_typ:

CREATE TYPE address_typ AS (
street VARCHAR(30),
city VARCHAR(20),
state CHAR(2),
postal_code VARCHAR(6));

CREATE TYPE employee_typ AS (
employee_id DECIMAL(6),
first_name VARCHAR(20),
last_name VARCHAR(25),
email VARCHAR(25),
phone_number VARCHAR(20),
hire_date DATE,
job_id VARCHAR(10),
salary DECIMAL(8,2),
commission_pct DECIMAL(2,2),
manager_id DECIMAL(6),
department_id DECIMAL(4),
address address_typ);

An expression that constructs a structure uses the type name, e.g.:

employee_typ(315, 'Francis', 'Logan', 'FLOGAN',
'555.777.2222', DATE '2004-05-01', 'SA_MAN', 11000, .15, 101, 110,
address_typ('376 Mission', 'San Francisco', 'CA', '94222'))

Tables can have structure-valued columns, but these have to be fully qualified using both the table name and the column name in programs:

CREATE TABLE PERS(p0 employee_typ, p1 employee_typ);
CREATE VIEW V AS SELECT PERS.p0.address FROM PERS
WHERE PERS.p0.first_name = 'Mike'

Grammar for specifying types

type:
typeName
[ collectionsTypeName ]*

typeName:
sqlTypeName
| compoundIdentifier
| MAP < type , type >

sqlTypeName:
char [ precision ] [ charSet ]
| varchar [ precision ] [ charSet ]
| DATE
| time
| timestamp
| GEOMETRY
| decimal [ precision [, scale] ]
| BOOLEAN
| integer
| BINARY [ precision ]
| varbinary [ precision ]
| TINYINT
| SMALLINT
| BIGINT
| REAL
| double
| VARIANT

collectionsTypeName:
ARRAY

char:
CHARACTER | CHAR

varchar:
char VARYING | VARCHAR

decimal:
DECIMAL | DEC | NUMERIC | NUMBER

integer:
INTEGER | INT

varbinary:
BINARY VARYING | VARBINARY

double:
DOUBLE [ PRECISION ]

time:
TIME [ precision ] [ timeZone ]

timestamp:
TIMESTAMP [ precision ] [ timeZone ]

charSet:
CHARACTER SET charSetName

timeZone:
WITHOUT TIME ZONE

A compoundIdentifier is a sequence of identifiers separated by dots.