Panoramica dei tipi di dati: INT VARCHAR DATE BOOLEAN

Perché i tipi sono importanti

Tipi interi

SMALLINT  -- 2 bytes,  -32768 to 32767
INTEGER   -- 4 bytes,  ±2.1 billion       (alias: INT)
BIGINT    -- 8 bytes,  ±9.2 quintillion

-- Auto-incrementing variants:
SMALLSERIAL  BIGSERIAL  SERIAL

Numerico / decimale

CREATE TABLE invoices (
  id BIGSERIAL PRIMARY KEY,
  amount NUMERIC(12, 2) NOT NULL,   -- up to 10 digits + 2 decimals
  tax_rate NUMERIC(5, 4) NOT NULL   -- 0.0875 = 8.75%
);

Float e Numeric

-- Inexact:
SELECT 0.1::FLOAT + 0.2::FLOAT;   -- 0.30000000000000004

-- Exact:
SELECT 0.1::NUMERIC + 0.2::NUMERIC;  -- 0.3

Tipi testuali: VARCHAR e TEXT

-- VARCHAR(n) — variable-length, max n characters
email VARCHAR(255)

-- TEXT — variable-length, no declared limit
description TEXT

-- In PostgreSQL both have the same performance.
-- VARCHAR(n) just adds a length check.
-- CHAR(n) (fixed-length, blank-padded) — avoid.

Data e ora

DATE         -- 2024-03-15
TIME         -- 14:30:00
TIMESTAMP    -- 2024-03-15 14:30:00      (no time zone)
TIMESTAMPTZ  -- 2024-03-15 14:30:00+02   (recommended)
INTERVAL     -- 1 day, 3 hours

-- Prefer TIMESTAMPTZ unless you have a strong reason not to.

Booleano

CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  done BOOLEAN NOT NULL DEFAULT false
);

SELECT * FROM tasks WHERE done;        -- shorthand for done = true
SELECT * FROM tasks WHERE NOT done;

UUID

CREATE TABLE api_keys (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id BIGINT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

JSON e JSONB

-- JSON   — text, preserves whitespace, no index
-- JSONB  — binary, deduplicated keys, indexable with GIN

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  payload JSONB NOT NULL
);

Array

CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  tags TEXT[] NOT NULL DEFAULT '{}'
);

INSERT INTO posts (title, tags) VALUES
  ('Hello', ARRAY['sql', 'intro']);

SELECT * FROM posts WHERE 'sql' = ANY(tags);

Scegliere il tipo giusto

Riepilogo

Verifica rapida