PartiQL Types

This document defines the types and their relation to the SQL-99 types. It is a draft, but this model has been implemented in partiql-lang-kotlin, and is used for Scribe translations.

Absent Values

There are two absent values — these are NOT types, but are included for completeness.

Value Description

NULL

The null value

MISSING

The missing value

Boolean Type

Boolean Type
Type Description Values

BOOLEAN

Boolean value

TRUE or FALSE

Numeric Types

Type Description Range

TINYINT

Signed integer that can be stored in one byte

-128 to +127

SMALLINT

Signed integer that can be stored in two bytes

-32768 to +32767

INTEGER

Signed integer that can be stored in four bytes

-2147483648 to +2147483647

BIGINT

Signed integer that can be stored in eight bytes

-9223372036854775808 to +9223372036854775807

NUMERIC(P,S)

Exact numeric with fixed precision P and scale S

…​

DECIMAL(P,S)

Exact numeric with fixed precision P and scale S

…​

FLOAT(P)

Floating point type with precision P

…​

REAL

Single-precision, binary floating point (IEEE 754 32-bit)

…​

DOUBLE PRECISION

Double-precision, binary floating point (IEEE 754 64-bit)

…​

1) TINYINT represents an 8-bit signed integer. It is not defined in SQL-99 or Ion, but is defined in popular systems such as Spark, Trino and MySQL. It also completes the bottom-end of the fixed-width integer types.

2) SMALLINT represents a 16-bit signed integer. From SQL, "SMALLINT specifies the data type exact numeric, with scale of 0 (zero) and binary or decimal precision. The choice of binary versus decimal precision is implementation-defined, but shall be the same as INTEGER. The precision of SMALLINT shall be less than or equal to the precision of INTEGER" — SQL-99 p.125.

3) INTEGER represents a 32-bit signed integer. From SQL, "INTEGER specifies the data type exact numeric, with binary or decimal precision and scale of 0 (zero). The choice of binary versus decimal precision is implementation-defined, but shall be the same as SMALLINT." — SQL-99 p.125

4) BIGINT represents a 64-bit signed integer. It is not defined in SQL-99 or Ion, but is defined in most SQL systems.

5) NUMERIC(p,s) represents an exact numeric type with precision P and scale S. It is equivalent to DECIMAL(p,s).

6) DECIMAL(p,s) represents an exact numeric type with precision P and scale S. SQL defines decimals as having precision equal to or greater than the given precision. Like other systems, we truncate extraneous precision so that NUMERIC(p,s) is equivalent to DECIMAL(p,s).

7) FLOAT(P) represents an approximate numeric type with binary precision equal to or greater than p. This is currently not implemented in PartiQL.

8) REAL represents an IEEE-754 32-bit floating point number. It is PartiQL’s implementation-defined choice of SQL’s REAL. It does not have a corresponding Ion type, but does have an ISL constraint ieee74_float: binary32.

9) DOUBLE PRECISION represents an IEEE-754 64-bit floating point number. It is PartiQL’s implementation-defined choice of SQL’s DOUBLE PRECISION. It corresponds to the Ion float type and has the ISL constraint eee74_float: binary64 .

Character String Types

Type Description

CHAR(n)

Unicode codepoint sequence of fixed length N

VARCHAR(N)

Unicode codepoint sequence of variable length up to N

STRING

Unicode codepoint sequence of arbitrary length

1) CHAR(N) represents a unicode codepoint sequence of fixed length n

2) VARCHAR(N) represents a unicode codepoint sequence of variable length up to N.

3) STRING represents a unicode codepoint sequence of arbitrary length.

Bit String Types

Type Description

BIT(N)

Bit string of fixed length N

VARBIT(N)

Bit string of variable length up to N
SQL defines the "bit string" types (SQL-99 p.11) but these are not formalized in PartiQL.

Lob Types (Large Object)

Byte String Types
Type Description

BLOB(N)

Octet string of variable length up to n

BLOB

Octet string of arbitrary length

CLOB(N)

Unicode codepoint sequence of variable length up to n

CLOB

Unicode codepoint sequence of arbitrary length
The partiql-lang-kotlin implementation requires specifying the BLOB/CLOB length, but like STRING it may not be necessary.

Datetime and Interval Types

Type Description

DATE

A date with no time

TIME(P)

A date-less time with seconds precision P and no time zone offset

TIMEZ(P)

A date-less time with seconds precision P and time zone offset Z

TIMESTAMP(P)

A date and time with seconds precision P and no time zone offset

TIMESTAMP(P, Z)

A date and time with seconds precision P and time zone offset Z

INTERVAL_YM

A time interval with seconds precision P

INTERVAL_DT

A time interval with seconds precision P
A time zone offset z is an hour/minute pair of integers

1) DATE reprsents a year, month, and day.

2) TIME represents a local time without a timezone offset.

  • Decimal seconds precision (up to nanosecond e.g. 9)

  • Required fields HOUR, MINUTE, and SECOND

3) TIMEZ (TIME WITH TIMEZONE) represents a time with a given timezone offset in hours:minutes.

  • Decimal seconds precision (up to nanosecond e.g. 9)

  • Required fields HOUR, MINUTE, and SECOND.

  • Required HOUR/MINUTE offset.

4) TIMESTAMP represents a (date, time) pair without a timezone offset.

  • Decimal seconds precision (up to nanosecond e.g. 9).

5) TIMESTAMPZ (TIMESTAMP WITH TIMEZONE) represents a (DATE, TIMEZ) pair with a given timezone offset in hours:minutes.

6) INTERVAL. An interval is composed of a contiguous subset of the fields. The subset of the fields is called the “precision” of the value. The leading field, either “YEAR” or “DAY” is only constrained by the “leading-field” precision. All fields are integers except for “SECOND” which has “fractional seconds precision”. In SQL, there are year-month and day-time intervals which are NOT mutually comparable. Year-month intervals are ONLY mutually comparable with other year-month intervals. Day-time intervals are mutually comparable only with other day-time intervals. For example, you cannot create a DAY-HOUR interval — but you can in PostgreSQL!

Collection Types

Type Description

ARRAY

Ordered, heterogeneous, variable length

T ARRAY

Ordered, homogenous on T, variable length

T ARRAY[N]

Ordered, homogenous on T, fixed length N

BAG

Unordered, heterogeneous, variable length

T BAG

Unordered, homogenous on T, variable length

T BAG[N]

Unordered, homogenous on T, fixed length N

1) ARRAY represents an ordered collection of elements with type T. The number of elements N is the array’s cardinality. Unlike SQL, an array definition does not require a fixed size. If an explicit element type T is not given, then dynamic is inferred.

Syntax Rules — Let T be a type definition and N be an unsigned integer.

-- 1
T ARRAY[N] <=> ARRAY<T>[N]

-- 2
ARRAY[N] <=> DYANMIC ARRAY[N] <=> ARRAY<DYNAMIC>[N]

-- 3
ARRAY <=> DYNAMIC ARRAY <=> ARRAY<DYNAMIC> <=> LIST

2) BAG represents an unordered collection of elements with type T.

Syntax Rules — Let T be a type definition and N be an unsigned integer. The number of elements N is the bag’s cardinality. If an explicit element type T is not given, then dynamic is inferred.

-- 1
T BAG[N] <=> BAG<T>[N]

-- 2
BAG[N] <=> DYANMIC BAG[N] <=> BAG<DYNAMIC>[N]

-- 3
BAG <=> DYNAMIC BAG <=> BAG<DYNAMIC>

Structural Types

This section is incomplete as PartiQL currently does not have precise definitions for tuples, structs, maps, etc. In this table, the STRUCT type is always open and unordered (but may have some known fields) and ROW which is always closed and ordered. This is not entirely correct, and we might need a closed unordered struct. If the struct is closed with no fields, then we should be able to derive an ordering.

Type Description

STRUCT

An open and unordered tuple with named elements

ROW

A closed and ordered tuple with named elements
Fields are name-value pairs where names are strings
Examples
ROW(x INT, y INT)       -- SQL-like
ROW<x: INT, y: INT>     -- Hive-like

STRUCT(x INT, y INT)    -- SQL-like
STRUCT<x: INT, y: INT>  -- Hive-like