ruk·si
/notes

🐘 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