Tuple

Name	Description
TUPLE	Fixed sequence of values without field names

A TUPLE is a STRUCT whose fields have no names: each field is addressed by its position, starting at 1, and can have its own type. TUPLE(INTEGER, VARCHAR) holds an INTEGER and a VARCHAR. It is the counterpart of a PostgreSQL anonymous record.

Creating Tuples

The row function returns a tuple of its arguments, and so does a parenthesized list of two or more values:

SQL

SELECT row(1, 'a') AS t, (1, 'a') AS u, typeof(row(1, 'a')) AS type;

 t     | u     | type
-------+-------+-------------------------
 (1,a) | (1,a) | TUPLE(INTEGER, VARCHAR)

Tuple Columns

Declare a column as TUPLE(type, ...) and fill it with row(...). A field of a tuple can be NULL while the tuple itself is not:

SQL

CREATE TABLE points (id INTEGER, p TUPLE(DOUBLE, DOUBLE));
INSERT INTO points VALUES (1, row(1.5, 2.5)), (2, NULL), (3, row(NULL, 4));
SELECT id, p, p IS NULL AS is_null FROM points ORDER BY id;

 id | p         | is_null
----+-----------+---------
  1 | (1.5,2.5) | f
  2 | NULL      | t
  3 | (,4)      | f

Search tables (CREATE TABLE … WITH (storage = 'search')) take tuple columns too.

CREATE TYPE gives a tuple type a name:

SQL

CREATE TYPE pair AS TUPLE(INTEGER, BIGINT);
CREATE TABLE pairs (p pair);
INSERT INTO pairs VALUES (row(1, 2));
SELECT p, typeof(p) AS type FROM pairs;

 p     | type
-------+------------------------
 (1,2) | TUPLE(INTEGER, BIGINT)

Accessing Fields

t[i] returns the field at position i, counting from 1, and is the same as struct_extract(t, i). The position must be a constant, and a position outside the tuple is an error. A tuple has no field names, so dot notation and the struct functions that work with names, such as struct_insert and struct_update, don't apply to it.

SQL

SELECT id, p[1] AS x, p[2] AS y, struct_extract(p, 2) AS also_y FROM points ORDER BY id;
SELECT p[3] FROM points;

 id | x    | y    | also_y
----+------+------+--------
  1 |  1.5 |  2.5 |    2.5
  2 | NULL | NULL |   NULL
  3 | NULL |    4 |      4

db error: ERROR: Key index 3 for struct_extract out of range - expected an index between 1 and 2

Casting

A tuple casts to a STRUCT with the same number of fields, and a STRUCT casts to a tuple, field by field in order: the names play no part. This is how row(...) fills a struct column, see Creating Structs with the row Function. A cast between different numbers of fields fails.

SQL

SELECT (row('a', 42)::STRUCT(v VARCHAR, i INTEGER)).v AS v, {'x': 1, 'y': 'b'}::TUPLE(INTEGER, VARCHAR) AS t;
SELECT row(1, 2)::STRUCT(a INTEGER);

 v | t
---+-------
 a | (1,b)

db error: ERROR: Type TUPLE(INTEGER, INTEGER) does not match with STRUCT(a INTEGER). Cannot cast STRUCTs of different size

Comparison and Ordering

Tuples compare like structs, field by field in order; see Comparison and Ordering.

PostgreSQL Clients

Clients receive a tuple as PostgreSQL's record type, in the text form PostgreSQL gives a record: (1,a), with a NULL field left empty. A cast to VARCHAR writes (1, a) instead. A tuple type made with CREATE TYPE reaches clients as that type: it is a composite type in pg_type, and pg_attribute names its fields element1, element2 and so on.

SQL

SELECT pg_typeof(row(1, 'a'))::TEXT AS pg_type, row(1, 'a')::VARCHAR AS text;

 pg_type | text
---------+--------
 record  | (1, a)