Advanced data types and ENUMs

In the previous lesson we saw the basic CREATE TABLE command. Let's now explore some advanced data types that PostgreSQL offers to model our domains in a more precise and safe way.

Numeric and text types

PostgreSQL has several types for numbers and text. Choosing the right one helps both with validation and with storage space.

CREATE TABLE advanced_products (
  id           SERIAL PRIMARY KEY,
  name         VARCHAR(50) NOT NULL,
  description  TEXT,
  -- NUMERIC(p,s) dove p = cifre totali, s = cifre decimali
  price        NUMERIC(10, 2) NOT NULL,
  featured     BOOLEAN DEFAULT FALSE
);

• TEXT vs VARCHAR(n): In Postgres performance is identical. You use VARCHAR(n) only when you want to explicitly limit the inserted length at the database level.
• NUMERIC(10, 2): Perfect for money or arithmetic precision. Here it allows at most 8 integer digits and 2 decimals (10 in total). If you insert 10.555, Postgres rounds it to 10.56.

The UUID type

For secure identifiers exposed on the web (such as public IDs in URLs), UUID is often preferred over the classic SERIAL increment.

CREATE TABLE public_orders (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer    TEXT NOT NULL,
  total       NUMERIC(10, 2)
);

ENUM (Enumerations)

Often a column must contain only one of a few predefined values (e.g. order status: "new", "paid", "shipped"). One approach is to use the CHECK keyword, but PostgreSQL offers custom ENUM types.

-- Dobbiamo prima creare il tipo:
CREATE TYPE order_status AS ENUM ('nuovo', 'pagato', 'spedito', 'cancellato');

-- Ora possiamo usarlo in una tabella:
CREATE TABLE orders_demo (
  id SERIAL PRIMARY KEY,
  status order_status NOT NULL DEFAULT 'nuovo'
);

This guarantees that no one can ever insert an invalid status, protecting data consistency.

Try it

Review exercise