Advanced Data Types: Arrays, HSTORE, UUID, ENUM & Ranges

-- An array column stores MANY values in one cell.
-- Add [] to any type to make it an array of that type.
CREATE TABLE articles (
    id    SERIAL PRIMARY KEY,
    title TEXT,
    tags  TEXT[] NOT NULL DEFAULT '{}'   -- a text array; default = empty {}
);

INSERT INTO articles (title, tags) VALUES
  ('Intro to SQL',     ARRAY['sql','beginner']),          -- ARRAY[...] literal
  ('Postgres Arrays',  '{sql,postgres,advanced}'),        -- '{...}' literal
  ('Cooking Pasta',    ARRAY['food','italia
...

-- Read an element by POSITION (Postgres arrays start at 1, not 0!):
SELECT title, tags[1] AS first_tag FROM articles;

-- "Is this value anywhere in the array?" — two equivalent ways:
SELECT title FROM articles WHERE 'sql' = ANY(tags);     -- ANY: value vs each element
SELECT title FROM articles WHERE tags @> ARRAY['sql'];  -- @> : array CONTAINS array

-- "Do the arrays share ANY value?" — the && overlap operator:
SELECT title FROM articles WHERE tags && ARRAY['food','sql'];

-- Handy array fu
...

-- unnest() explodes an array into ONE ROW PER ELEMENT.
-- Useful for grouping, counting, or joining on individual values.
SELECT title, unnest(tags) AS tag
FROM articles;
-- Intro to SQL    | sql
-- Intro to SQL    | beginner
-- Postgres Arrays | sql
-- Postgres Arrays | postgres ...

-- Combine with GROUP BY to count how often each tag is used:
SELECT tag, COUNT(*) AS uses
FROM articles, unnest(tags) AS tag
GROUP BY tag
ORDER BY uses DESC, tag;

-- HSTORE stores flat KEY => VALUE string pairs in one column.
-- It is a Postgres extension, so enable it once per database:
CREATE EXTENSION IF NOT EXISTS hstore;

CREATE TABLE profiles (
    user_id     INT PRIMARY KEY,
    preferences HSTORE          -- e.g. theme, language, font_size
);

INSERT INTO profiles (user_id, preferences) VALUES
  (1, 'theme => dark,  language => en'),
  (2, 'theme => light, language => fr, beta => on');

-- ->  reads the value for a key (NULL if the key is missing
...

-- A UUID is a 128-bit id like a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11.
-- gen_random_uuid() (built in on modern Postgres) makes a fresh one.
CREATE TABLE orders (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- auto-filled id
    customer    TEXT,
    total       NUMERIC(10,2),
    created_at  TIMESTAMP DEFAULT now()
);

-- You don't supply id — the DEFAULT generates it for you:
INSERT INTO orders (customer, total) VALUES ('Ada', 49.99)
RETURNING id;   -- returns the new UUID, e.g.
...

-- An ENUM is a custom type limited to a fixed list of labels.
-- It documents the allowed values AND enforces them.
CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered', 'cancelled');

CREATE TABLE shipments (
    id     SERIAL PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'pending'
);

INSERT INTO shipments (status) VALUES ('pending'), ('shipped'), ('delivered');

-- ENUMs compare and SORT in their DEFINITION order, not alphabetically:
SELECT status FROM shipments ORDER 
...

-- A range type stores an interval (a low and high bound) in one value.
-- Built-ins include int4range, numrange, daterange, tsrange.
-- Brackets set inclusivity:  [ = inclusive,  ) = exclusive.
SELECT
  int4range(1, 10)             AS ints,    -- 1..9  (10 excluded)
  '[2024-06-01,2024-06-05)'::daterange AS stay;   -- Jun 1..4

-- A booking table where each stay is a daterange:
CREATE TABLE bookings (
    id    SERIAL PRIMARY KEY,
    room  INT,
    stay  daterange
);
INSERT INTO bookings (room
...