PostgreSQL/Table/Composite Column

Insert value to composite column

   <source lang="sql">

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=#

      </source>
   
  

Reference column name through table name

   <source lang="sql">

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=#

      </source>
   
  

To access a field of a composite column

   <source lang="sql">

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=#

      </source>