ruk·si

🐘 PostgreSQL
Composite Types

Updated at 2016-01-01 19:17

Composite type is a list of fields and their data types; like a row. They can be used in various ways e.g. as column type.

-- DROP TYPE wine;
CREATE TYPE wine AS (
    wine_vineyard   text,
    wine_type       text,
    wine_year       int
);

-- DROP TABLE CREATE TABLE wine_suggestions;
CREATE TABLE wine_suggestions (
    main_dish      text,
    suggested_wine wine
);

row() expression is used to create composite types instances. But row keyword is optional if there is more than one field.

INSERT INTO wine_suggestions VALUES
('Lobster Tail',   row('Tigrus',   'Chardonnay',         2012)),
('Elk Medallions', row('Rombauer', 'Cabernet Sauvignon', 2013)),
('Roasted Salmon',    ('Perros',   'Pinot Noir',         2014));
SELECT * FROM wine_suggestions;
-- "Lobster Tail";     "(Tigrus,Chardonnay,2012)"
-- "Elk Medallions";   "(Rombauer,"Cabernet Sauvignon",2013)"
-- "Roasted Salmon";   "(Perros,"Pinot Noir",2014)"

You access composite fields with (type).field syntax.

SELECT *
FROM wine_suggestions
WHERE (suggested_wine).wine_type = 'Chardonnay';

SELECT
    (suggested_wine).wine_vineyard,
    (suggested_wine).wine_type
FROM wine_suggestions
WHERE main_dish = 'Elk Medallions';

SELECT DISTINCT (w_s.suggested_wine).wine_year
FROM wine_suggestions w_s;

You can update individual fields of a composite type. After SET, don't add parenthesis.

UPDATE wine_suggestions SET
suggested_wine.wine_year = (suggested_wine).wine_year - 1;

Sources