View a markdown version of this page

Supported data types in Aurora DSQL - Amazon Aurora DSQL
Numeric data typesCharacter data typesDate and time data typesMiscellaneous data typesQuery runtime data types

Supported data types in Aurora DSQL

Aurora DSQL supports a subset of the common PostgreSQL types.

Numeric data types

Aurora DSQL supports the following PostgreSQL numeric data types.

Name Aliases Range and precision Storage size Index support
smallint int2 -32768 to +32767 2 bytes Yes

integer

int, int4

-2147483648 to +2147483647

4 bytes

 Yes

bigint

int8

-9223372036854775808 to +9223372036854775807

8 bytes

 Yes

real

float4

6 decimal digits precision

4 bytes

 Yes

double precision

float8

15 decimal digits precision

8 bytes

 Yes

numeric [ (p, s) ]

decimal [ (p, s) ]

dec[ (p,s)]

Exact numeric of selectable precision. The maximum precision is 38 and the maximum scale is 37.1 The default is numeric (18,6).

8 bytes + 2 bytes per precision digit. Maximum size is 27 bytes.

 Yes

1 – If you don't explicitly specify a size when you run CREATE TABLE or ALTER TABLE ADD COLUMN, Aurora DSQL enforces the defaults. Aurora DSQL applies limits when you run INSERT or UPDATE statements.

Character data types

Aurora DSQL supports the following PostgreSQL character data types.

Name Aliases Description Aurora DSQL limit Storage size Index support

character [ (n) ]

char [ (n) ]

Fixed-length character string

4096 bytes1

Variable up to 4100 bytes

 Yes

character varying [ (n) ]

varchar [ (n) ]

Variable-length character string

65535 bytes1

Variable up to 65539 bytes

 Yes

bpchar [ (n) ]

If fixed length, this is an alias for char. If variable length, this is an alias for varchar, where trailing spaces are semantically insignificant.

4096 bytes1

Variable up to 4100 bytes

 Yes

text

Variable-length character string

1 MiB1

Variable up to 1 MiB

 Yes

1 – If you don't explicitly specify a size when you run CREATE TABLE or ALTER TABLE ADD COLUMN, then Aurora DSQL enforces the defaults. Aurora DSQL applies limits when you run INSERT or UPDATE statements.

Date and time data types

Aurora DSQL supports the following PostgreSQL date and time data types.

Name Aliases Description Range Resolution Storage size Index support

date

Calendar date (year, month, day)

4713 BC – 5874897 AD

 1 day

4 bytes

 Yes

time [ (p) ] [ without time zone ]

timestamp

Time of day, with no time zone

 0 – 1 1 microsecond

8 bytes

 Yes

time [ (p) ] with time zone

timetz

time of day, including time zone

00:00:00+1559 – 24:00:00 –1559

 1 microsecond

12 bytes

 No

timestamp [ (p) ] [ without time zone ]

Date and time, with no time zone

 4713 BC – 294276 AD 1 microsecond

8 bytes

 Yes

timestamp [ (p) ] with time zone

timestamptz

Date and time, including time zone

 4713 BC – 294276 AD 1 microsecond

8 bytes

 Yes

interval [ fields ] [ (p) ]

Time span

 -178000000 years – 178000000 years 1 microsecond

16 bytes

 No

Miscellaneous data types

Aurora DSQL supports the following miscellaneous PostgreSQL data types.

Name Aliases Description Aurora DSQL limit Storage size Index support

boolean

bool

Logical Boolean (true/false)

1 byte

 Yes

bytea

Binary data ("byte array")

1 MiB1

Variable up to 1 MiB limit

 No

UUID

Universally unique identifier

16 bytes

 Yes

json

JSON data

1 MiB2

Variable up to 1 MiB limit.2

 No

1 – If you don't explicitly specify a size when you run CREATE TABLE or ALTER TABLE ADD COLUMN, then Aurora DSQL enforces the defaults. Aurora DSQL applies limits when you run INSERT or UPDATE statements.

2 – Aurora DSQL automatically applies compression to json columns and by default compresses large json values during INSERT and UPDATE operations. The 1 MiB limit applies to the compressed size, so you can store json values significantly larger than 1 MiB as long as they compress below the limit.

To disable compression, use the STORAGE keyword. For more information, see CREATE TABLE and ALTER TABLE.

JSON functions and operators

Aurora DSQL supports all PostgreSQL JSON functions and operators from section 9.16 JSON Functions and Operators with identical behavior.

Note

The functions json_populate_record and json_populate_recordset work with table and view row types, but not with custom composite types as Aurora DSQL doesn't currently support CREATE TYPE.

The following examples show json_populate_record and json_populate_recordset used with a table row type:

CREATE TABLE tt (c1 INT, c2 INT);
SELECT * FROM json_populate_record(null::tt, '{"c1": 1, "c2": 2}');
 c1 | c2 
----+----
  1 |  2
(1 row)
SELECT * FROM json_populate_recordset(null::tt, '[{"c1":1,"c2":2}, {"c1":3,"c2":4}]');
 c1 | c2 
----+----
  1 |  2
  3 |  4
(2 rows)

Query runtime data types

Query runtime data types are internal data types used at query execution time. These types are distinct from the PostgreSQL-compatible types like varchar and integer that you define in your schema. Instead, these types are runtime representations that Aurora DSQL uses when processing a query.

The following data types are supported only during query runtime:

Array type

Aurora DSQL supports arrays of the supported data types. For example, you can have an array of integers. The function string_to_array splits a string into a PostgreSQL-style array with the comma delimiter (,) as shown in the following example. You can use arrays in expressions, function outputs, or temporary computations during query execution.

SELECT string_to_array('1,2', ',');

The function returns a response similar to the following:

 string_to_array 
-----------------
 {1,2}
(1 row)
inet type

The data type represents IPv4, IPv6 host addresses, and their subnets. This type is useful when parsing logs, filtering on IP subnets, or doing network calculations within a query. For more information, see inet in the PostgreSQL documentation.

JSONB type

Aurora DSQL supports JSONB as a runtime data type for query processing. To store JSON data, use the json type.

Aurora DSQL supports all PostgreSQL JSONB functions from section 9.16 JSON Functions and Operators with identical behavior. The same composite type limitation described in JSON functions and operators applies to jsonb_populate_record and jsonb_populate_recordset.