Tuple type

Use a tuple as an alternative to a user-defined type.

The tuple data type holds fixed-length sets of typed positional fields. Use a tuple as an alternative to a user-defined type. A tuple can accommodate many fields (32768), more than can be prudently used. Typically, create a tuple with a few fields.

In the table creation statement, use angle brackets and a comma delimiter to declare the tuple component types. Surround the tuple values in parentheses to insert the values into a table, as shown in the following example:

CREATE TABLE IF NOT EXISTS cycling.nation_rank (
  nation text PRIMARY KEY,
  info tuple<int, text, int>
);
INSERT INTO cycling.nation_rank (
  nation, info
) VALUES (
  'Spain', (1, 'Alejandro VALVERDE', 9054)
);

INSERT INTO cycling.nation_rank (
  nation, info
) VALUES (
  'France', (2, 'Sylvain CHAVANEL', 6339)
);

INSERT INTO cycling.nation_rank (
  nation, info
) VALUES (
  'Belgium', (3, 'Phillippe GILBERT', 6222)
);

INSERT INTO cycling.nation_rank (
  nation, info
) VALUES (
  'Italy', (4, 'Davide REBELLINI', 6090)
);
SELECT *
FROM cycling.nation_rank;
SELECT *
FROM cycling.nation_rank
WHERE info = (3, 'Phillippe GILBERT', 6222);

Output:

 nation  | info
---------+---------------------------------
   Spain | (1, 'Alejandro VALVERDE', 9054)
 Belgium |  (3, 'Phillippe GILBERT', 6222)
  France |   (2, 'Sylvain CHAVANEL', 6339)
   Italy |   (4, 'Davide REBELLINI', 6090)

(4 rows)

Use a tuple to filter a selection, as shown in the following example that creates an index and then queries the table:

CREATE INDEX IF NOT EXISTS ON
cycling.nation_rank (info);
SELECT *
FROM cycling.nation_rank
WHERE info = (3, 'Phillippe GILBERT', 6222);

Output:

 nation  | info
---------+--------------------------------
 Belgium | (3, 'Phillippe GILBERT', 6222)

(1 rows)

You can nest tuples as shown in the following example that stores each way point location name, along with the latitude and longitude:

CREATE TABLE IF NOT EXISTS cycling.route (
  race_id int,
  race_name text,
  point_id int,
  lat_long tuple<text, tuple<float, float>>,
  PRIMARY KEY (race_id, point_id)
);
INSERT INTO cycling.route (
  race_id, race_name, point_id, lat_long
) VALUES (
  500, '47th Tour du Pays de Vaud', 1, ('Onnens', (46.8444, 6.6667))
);

INSERT INTO cycling.route (
  race_id, race_name, point_id, lat_long
) VALUES (
  500, '47th Tour du Pays de Vaud', 2, ('Champagne', (46.833, 6.65))
);

INSERT INTO cycling.route (
  race_id, race_name, point_id, lat_long
) VALUES (
  500, '47th Tour du Pays de Vaud', 3, ('Novalle', (46.833, 6.6))
);

INSERT INTO cycling.route (
  race_id, race_name, point_id, lat_long
) VALUES (
  500, '47th Tour du Pays de Vaud', 4, ('Vuiteboeuf', (46.8, 6.55))
);

INSERT INTO cycling.route (
  race_id, race_name, point_id, lat_long
) VALUES (
  500, '47th Tour du Pays de Vaud', 5, ('Baulmes', (46.7833, 6.5333))
);

INSERT INTO cycling.route (
  race_id, race_name, point_id, lat_long
) VALUES (
  500, '47th Tour du Pays de Vaud', 6, ('Les Clées', (46.7222, 6.5222))
);
SELECT *
FROM cycling.route;

Output:

 race_id | point_id | lat_long                         | race_name
---------+----------+----------------------------------+---------------------------
     500 |        1 |    ('Onnens', (46.8444, 6.6667)) | 47th Tour du Pays de Vaud
     500 |        2 |    ('Champagne', (46.833, 6.65)) | 47th Tour du Pays de Vaud
     500 |        3 |       ('Novalle', (46.833, 6.6)) | 47th Tour du Pays de Vaud
     500 |        4 |     ('Vuiteboeuf', (46.8, 6.55)) | 47th Tour du Pays de Vaud
     500 |        5 |   ('Baulmes', (46.7833, 6.5333)) | 47th Tour du Pays de Vaud
     500 |        6 | ('Les Clées', (46.7222, 6.5222)) | 47th Tour du Pays de Vaud

(6 rows)