07 Jan 2022

PostgreSQL Datatypes

Enum

CREATE TYPE month AS ENUM ["Jan", "Feb", "Mar", ...]

Composite Types

→ List of field names with data types

CREATE TYPE inventory_item AS (
    name text,
    price numeric
);

Usage

→ Creating a table

CREATE TABLE on_hand(
    item inventory_item,
    count integer
);

→ Adding values

INSERT INTO on_hand
VALUES (ROW(’box’, 2.0), 3)

→ Accessing values

SELECT (on_hand.item).name 
FROM on_hand 
WHERE (on_hand.item).price > 10