PostgreSQL/Table/Composite Column

Insert value to composite column

postgres=#
postgres=# CREATE TYPE inventory_item AS (
postgres(#    name            text,
postgres(#    supplier_id     integer,
postgres(#    price           numeric
postgres(# );
CREATE TYPE
postgres=#
postgres=# CREATE TABLE on_hand (
postgres(#    item      inventory_item,
postgres(#    count     integer
postgres(# );
CREATE TABLE
postgres=#
postgres=# INSERT INTO on_hand VALUES (ROW("fuzzy dice", 42, 1.99), 1000);
INSERT 0 1
postgres=#
postgres=# select * from on_hand;
          item          | count
------------------------+-------
 ("fuzzy dice",42,1.99) |  1000
(1 row)
postgres=#
postgres=# SELECT (item).name FROM on_hand WHERE (item).price > 0.99;
    name
------------
 fuzzy dice
(1 row)
postgres=#
postgres=# drop table on_hand;
DROP TABLE
postgres=# drop type inventory_item;
DROP TYPE
postgres=#
postgres=#

Reference column name through table name

postgres=#
postgres=# CREATE TYPE inventory_item AS (
postgres(#    name            text,
postgres(#    supplier_id     integer,
postgres(#    price           numeric
postgres(# );
CREATE TYPE
postgres=#
postgres=# CREATE TABLE on_hand (
postgres(#    item      inventory_item,
postgres(#    count     integer
postgres(# );
CREATE TABLE
postgres=#
postgres=# INSERT INTO on_hand VALUES (ROW("fuzzy dice", 42, 1.99), 1000);
INSERT 0 1
postgres=#
postgres=# select * from on_hand;
          item          | count
------------------------+-------
 ("fuzzy dice",42,1.99) |  1000
(1 row)
postgres=#
postgres=# SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 0.99;
    name
------------
 fuzzy dice
(1 row)
postgres=#
postgres=# drop table on_hand;
DROP TABLE
postgres=# drop type inventory_item;
DROP TYPE
postgres=#
postgres=#
postgres=#

To access a field of a composite column

postgres=#
postgres=# CREATE TYPE inventory_item AS (
postgres(#    name            text,
postgres(#    supplier_id     integer,
postgres(#    price           numeric
postgres(# );
CREATE TYPE
postgres=#
postgres=# CREATE TABLE on_hand (
postgres(#    item      inventory_item,
postgres(#    count     integer
postgres(# );
CREATE TABLE
postgres=#
postgres=# INSERT INTO on_hand VALUES (ROW("fuzzy dice", 42, 1.99), 1000);
INSERT 0 1
postgres=#
postgres=# select * from on_hand;
          item          | count
------------------------+-------
 ("fuzzy dice",42,1.99) |  1000
(1 row)
postgres=#
postgres=#
postgres=# -- To access a field of a composite column
postgres=#
postgres=#
postgres=# SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
 name
------
(0 rows)
postgres=#
postgres=# drop table on_hand;
DROP TABLE
postgres=# drop type inventory_item;
DROP TYPE
postgres=#
postgres=#