Overview of the data types supported in DuneSQL.
BOOLEAN
true
and false
.
VARBINARY
varbinary
data types.
SQL statements support usage of binary data with the prefix 0x
. The
binary data has to use hexadecimal format. For example, the binary form
of eh?
is X'65683F'
.
We have built custom functions to make it easier to work with varbinaries in DuneSQL. Check the varbinary functions page for more information.
TINYINT
-2^7
and a maximum value of 2^7 - 1
.
SMALLINT
-2^15
and a maximum value of 2^15 - 1
.
INTEGER
-2^31
and a maximum value of 2^31 - 1
. The name INT
is also
available for this type.
BIGINT
-2^63
and a maximum value of 2^63 - 1
.
UINT256
(Dune SQL)INT256
(Dune SQL)REAL
REAL '10.3'
, REAL '10.3e0'
, REAL '1.03e1'
DOUBLE
DOUBLE '10.3'
, DOUBLE '1.03e1'
, 10.3e0
, 1.03e1
DECIMAL
DECIMAL(10,3)
, DECIMAL(20)
Example literals: DECIMAL '10.3'
, DECIMAL '1234567890'
, 1.1
VARCHAR
varchar
, varchar(20)
SQL statements support simple literal, as well as Unicode usage:
'Hello winter !'
U&'Hello winter \2603 !'
U&'Hello winter #2603 !' UESCAPE '#'
U&
and requires an escape character
before any Unicode character usage with 4 digits. In the examples above
\2603
and #2603
represent a snowman character. Long Unicode codes
with 6 digits require usage of the plus symbol before the code. For
example, you need to use \+01F600
for a grinning face emoji.
CHAR
CHAR
type without length specified has
a default length of 1. A CHAR(x)
value always has x
characters. For
example, casting dog
to CHAR(7)
adds 4 implicit trailing spaces.
Leading and trailing spaces are included in comparisons of CHAR
values. As a result, two character values with different lengths
(CHAR(x)
and CHAR(y)
where x != y
) will never be equal.
Example type definitions: char
, char(20)
JSON
true
, false
or null
.
date and time functions
.
DATE
DATE '2001-08-22'
TIME
TIME
is an alias for TIME(3)
(millisecond precision).
TIME(P)
P
digits
of precision for the fraction of seconds. A precision of up to 12
(picoseconds) is supported.
Example: TIME '01:02:03.456'
TIME WITH TIME ZONE
TIMESTAMP
TIMESTAMP
is an alias for TIMESTAMP(3)
(millisecond precision).
TIMESTAMP(P)
P
digits of
precision for the fraction of seconds. A precision of up to 12
(picoseconds) is supported. This type is effectively a combination of
the DATE
and TIME(P)
types.
TIMESTAMP(P) WITHOUT TIME ZONE
is an equivalent name.
Timestamp values can be constructed with the TIMESTAMP
literal
expression. Alternatively, language constructs such as
localtimestamp(p)
, or a number of `date and time functions and
operators can
return timestamp values.
Casting to lower precision causes the value to be rounded, and not
truncated. Casting to higher precision appends zeros for the additional
digits.
The following examples illustrate the behavior:
TIMESTAMP WITH TIME ZONE
{#timestamp-with-time-zone-data-type}TIMESTAMP WITH TIME ZONE
is an alias for TIMESTAMP(3) WITH TIME ZONE
(millisecond precision).
TIMESTAMP(P) WITH TIME ZONE
P
digits
of precision for the fraction of seconds and with a time zone. Values of
this type are rendered using the time zone from the value. Time zones
can be expressed in the following ways:
UTC
, with GMT
, Z
, or UT
usable as aliases for UTC.+hh:mm
or -hh:mm
with hh:mm
as an hour and minute offset from
UTC. Can be written with or without UTC
, GMT
, or UT
as an
alias for UTC.INTERVAL YEAR TO MONTH
INTERVAL '3' MONTH
INTERVAL DAY TO SECOND
INTERVAL '2' DAY
ARRAY
ARRAY[1, 2, 3]
MAP
MAP(ARRAY['foo', 'bar'], ARRAY[1, 2])
ROW
CAST(ROW(1, 2e0) AS ROW(x BIGINT, y DOUBLE))
Named row fields are accessed with field reference operator (.
).
Example: CAST(ROW(1, 2.0) AS ROW(x BIGINT, y DOUBLE)).x
Named or unnamed row fields are accessed by position with the subscript
operator ([]
). The position starts at 1
and must be a constant.
Example: ROW(1, 2.0)[1]
IPADDRESS
4291#section-2.5.5.2
). When creating an
IPADDRESS
, IPv4 addresses will be mapped into that range. When
formatting an IPADDRESS
, any address within the mapped range will be
formatted as an IPv4 address. Other addresses will be formatted as IPv6
using the canonical format defined in 5952
.
Examples: IPADDRESS '10.0.0.1'
, IPADDRESS '2001:db8::1'
UUID
4122
\
Example: UUID '12151fd2-7586-11e9-8f9e-2a86e4085a59'
HyperLogLog
approx_distinct
. It starts as a sparse
representation, switching to a dense representation when it becomes more
efficient.
P4HyperLogLog
hyperloglog_type
, but it starts (and remains) in the dense representation.
SetDigest
QDigest
approx_percentile
can be reused. For example, one may be interested in
a daily reading of the 99th percentile values that are read over the
course of a week. Instead of calculating the past week of data with
approx_percentile
, qdigest
s could be stored daily, and quickly
merged to retrieve the 99th percentile value.
TDigest