🧱 Data Types & Constraints

-- Without data types, this could happen:
INSERT INTO users (age) VALUES ('twenty-five');
-- Uh oh! "twenty-five" is text, not a number.
-- How do you calculate average age now? You can't!

-- With proper data types:
INSERT INTO users (age INTEGER) VALUES ('twenty-five');
-- ERROR: invalid input syntax for type integer
-- PostgreSQL catches the mistake immediately! 🎉

CREATE TABLE example_integers (
    age            SMALLINT,      -- Max 32,767 — plenty for age
    employee_id    INTEGER,       -- Max 2.1 billion — enough for most companies
    galaxy_stars   BIGINT         -- Max 9.2 quintillion — for astronomically big numbers
);

INSERT INTO example_integers VALUES (25, 100042, 400000000000);

-- NUMERIC(precision, scale)
-- precision = total digits, scale = digits after decimal

CREATE TABLE products (
    name     VARCHAR(100),
    price    NUMERIC(10, 2)   -- Up to 99,999,999.99
);

INSERT INTO products VALUES ('Laptop', 999.99);
INSERT INTO products VALUES ('Coffee', 4.50);

-- PostgreSQL stores these EXACTLY as 999.99 and 4.50
-- No rounding errors. Ever. Period.

-- The horror of using FLOAT for money:
SELECT 0.1::FLOAT + 0.2::FLOAT;
-- Result: 0.30000000000000004  😱
-- That's NOT 0.30!

-- The safety of using NUMERIC for money:
SELECT 0.1::NUMERIC + 0.2::NUMERIC;
-- Result: 0.3  ✅ Exactly right!

CREATE TABLE weather_readings (
    station_id    INTEGER,
    temperature   REAL,              -- 23.456°C — doesn't need to be exact to the penny
    latitude      DOUBLE PRECISION,   -- 40.7128° — needs more decimal places
    longitude     DOUBLE PRECISION    -- -74.0060°
);

CREATE TABLE employees (
    id      SERIAL PRIMARY KEY,    -- 1, 2, 3, 4, 5 ... auto!
    name    VARCHAR(100)
);

-- You DON'T specify the id — PostgreSQL does it for you:
INSERT INTO employees (name) VALUES ('Alice');   -- id = 1
INSERT INTO employees (name) VALUES ('Bob');     -- id = 2
INSERT INTO employees (name) VALUES ('Charlie'); -- id = 3

CREATE TABLE employees (
    id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE text_demo (
    country_code  CHAR(2),        -- Always 2 chars: 'US', 'UK', 'IN'
    username      VARCHAR(50),    -- Up to 50 chars — "alice_wonder" uses 12
    bio           TEXT            -- No limit — write your life story
);

INSERT INTO text_demo VALUES ('US', 'alice_wonder', 'I love databases!');

-- Inserting a 2-char value into CHAR(10):
SELECT 'AB'::CHAR(10) = 'AB';
-- Result: true (PostgreSQL ignores trailing spaces for comparison)

SELECT LENGTH('AB'::CHAR(10));
-- Result: 2 (LENGTH trims trailing spaces)

SELECT OCTET_LENGTH('AB'::CHAR(10));
-- Result: 10 (but it's actually STORING 10 bytes!)

-- Instead of VARCHAR(100):
CREATE TABLE users (
    email TEXT CHECK (LENGTH(email) <= 254)
);
-- Same limit, but the error message says
-- "violates check constraint" instead of "value too long"

CREATE TABLE events (
    id           SERIAL PRIMARY KEY,
    event_name   VARCHAR(200),
    event_date   DATE,              -- Just the day
    start_time   TIME,              -- Just the clock time
    created_at   TIMESTAMPTZ,       -- Full timestamp with timezone
    duration     INTERVAL           -- How long it lasts
);

INSERT INTO events (event_name, event_date, start_time, created_at, duration)
VALUES (
    'PostgreSQL Workshop',
    '2026-04-15',
    '09:00:00',
    NOW(),
    '3 hours 30 minutes'
);

-- Get the current date and time:
SELECT NOW();              -- 2026-03-19 14:30:00+05:30
SELECT CURRENT_DATE;      -- 2026-03-19
SELECT CURRENT_TIME;      -- 14:30:00+05:30

-- Calculate age (years, months, days between two dates):
SELECT AGE('2026-03-19', '1995-07-15');
-- Result: 30 years 8 mons 4 days

-- Extract parts of a date:
SELECT EXTRACT(YEAR FROM NOW());    -- 2026
SELECT EXTRACT(MONTH FROM NOW());   -- 3
SELECT EXTRACT(DOW FROM NOW());     -- Day of week (0=Sun, 6=Sat)

-- Date arithmetic with INTERVAL:
SELECT NOW() + INTERVAL '7 days';         -- One week from now
SELECT NOW() - INTERVAL '3 months';       -- Three months ago
SELECT NOW() + INTERVAL '1 year 6 months'; -- 18 months from now

SELECT
    name,
    birth_date,
    AGE(birth_date) AS age,
    EXTRACT(YEAR FROM AGE(birth_date)) AS years_old
FROM employees;

-- Result:
-- name  | birth_date | age                    | years_old
-- Alice | 1995-07-15 | 30 years 8 mons 4 days | 30
-- Bob   | 2000-01-01 | 26 years 2 mons 18 days| 26

CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(100),
    is_active  BOOLEAN DEFAULT true,
    is_admin   BOOLEAN DEFAULT false
);

INSERT INTO users (name, is_active, is_admin)
VALUES
    ('Alice', true,  false),
    ('Bob',   true,  true),
    ('Eve',   false, false);

-- Find active, non-admin users:
SELECT name FROM users WHERE is_active = true AND is_admin = false;
-- Result: Alice

-- Enable the UUID extension (needed once per database):
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE orders (
    id          UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    product     VARCHAR(200),
    quantity    INTEGER
);

INSERT INTO orders (product, quantity) VALUES ('Laptop', 1);
INSERT INTO orders (product, quantity) VALUES ('Mouse', 3);

SELECT * FROM orders;
-- id                                   | product | quantity
-- f47ac10b-58cc-4372-a567-0e02b2c3d479 | Laptop  | 1
-- 7c9e6679-7425-40de-944b-e07fc1f90ae7 | Mouse   | 3

-- Create a custom ENUM type:
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'banned');

CREATE TABLE accounts (
    id       SERIAL PRIMARY KEY,
    name     VARCHAR(100),
    status   user_status DEFAULT 'active'
);

INSERT INTO accounts (name, status) VALUES ('Alice', 'active');   -- ✅ Works
INSERT INTO accounts (name, status) VALUES ('Bob',   'banned');   -- ✅ Works
INSERT INTO accounts (name, status) VALUES ('Eve',   'deleted');  -- ❌ ERROR!
-- ERROR: invalid input value for enum user_status: "deleted"

-- Alternative: VARCHAR + CHECK (easier to modify):
CREATE TABLE accounts (
    status VARCHAR(20) CHECK (status IN ('active', 'inactive', 'banned'))
);

CREATE TABLE user_profiles (
    id       SERIAL PRIMARY KEY,
    name     VARCHAR(100),
    settings JSONB  -- Use JSONB (binary), not JSON — it's faster for queries
);

INSERT INTO user_profiles (name, settings) VALUES (
    'Alice',
    '{"theme": "dark", "language": "en", "notifications": true}'
);

-- Query a specific key from the JSON:
SELECT name, settings->>'theme' AS theme FROM user_profiles;
-- Result: Alice | dark

CREATE TABLE customers (
    id     SERIAL PRIMARY KEY,
    name   VARCHAR(100) NOT NULL,   -- You MUST have a name
    email  VARCHAR(200) NOT NULL    -- You MUST have an email
);

INSERT INTO customers (name, email) VALUES ('Alice', 'alice@mail.com');  -- ✅
INSERT INTO customers (name, email) VALUES (NULL, 'bob@mail.com');      -- ❌ ERROR!
-- ERROR: null value in column "name" violates not-null constraint

CREATE TABLE users (
    id       SERIAL PRIMARY KEY,
    email    VARCHAR(200) UNIQUE NOT NULL,
    username VARCHAR(50)  UNIQUE NOT NULL
);

INSERT INTO users (email, username) VALUES ('alice@mail.com', 'alice99');  -- ✅
INSERT INTO users (email, username) VALUES ('alice@mail.com', 'bob42');    -- ❌ ERROR!
-- ERROR: duplicate key value violates unique constraint "users_email_key"

-- Single-column primary key (most common):
CREATE TABLE products (
    product_id   SERIAL PRIMARY KEY,
    name         VARCHAR(200)
);

-- Composite primary key (two columns together form the key):
CREATE TABLE order_items (
    order_id     INTEGER,
    product_id   INTEGER,
    quantity     INTEGER,
    PRIMARY KEY (order_id, product_id)  -- Together they must be unique
);

-- Parent table (the "referenced" table):
CREATE TABLE departments (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(100) NOT NULL
);

-- Child table (has the foreign key):
CREATE TABLE employees (
    id             SERIAL PRIMARY KEY,
    name           VARCHAR(100) NOT NULL,
    department_id  INTEGER REFERENCES departments(id)
);

INSERT INTO departments (name) VALUES ('Engineering');  -- id = 1
INSERT INTO departments (name) VALUES ('Marketing');    -- id = 2

INSERT INTO employees (name, department_id) VALUES ('Alice', 1);  -- ✅ dept 1 exists
INSERT INTO employees (name, department_id) VALUES ('Bob',   99); -- ❌ ERROR!
-- ERROR: insert or update violates foreign key constraint
-- Key (department_id)=(99) is not present in table "departments"

CREATE TABLE employees (
    id             SERIAL PRIMARY KEY,
    name           VARCHAR(100),
    department_id  INTEGER REFERENCES departments(id)
                   ON DELETE SET NULL       -- If dept is deleted, set to NULL
                   ON UPDATE CASCADE        -- If dept id changes, update here too
);

CREATE TABLE products (
    id       SERIAL PRIMARY KEY,
    name     VARCHAR(200) NOT NULL,
    price    NUMERIC(10,2) CHECK (price > 0),           -- No free or negative-priced items!
    quantity INTEGER CHECK (quantity >= 0),                -- Can't have negative stock
    rating   SMALLINT CHECK (rating BETWEEN 1 AND 5)      -- 1 to 5 stars only
);

INSERT INTO products (name, price, quantity, rating) VALUES ('Laptop', 999.99, 50, 5);  -- ✅
INSERT INTO products (name, price, quantity, rating) VALUES ('Scam',   -10,   5,  3);  -- ❌ ERROR!
-- ERROR: new row violates check constraint "products_price_check"

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    status      VARCHAR(20) DEFAULT 'pending',   -- New orders start as 'pending'
    created_at  TIMESTAMPTZ DEFAULT NOW(),      -- Auto-stamp with current time
    priority    INTEGER DEFAULT 0                -- Default priority is 0 (normal)
);

-- Insert without specifying defaults — they fill in automatically:
INSERT INTO orders DEFAULT VALUES;
SELECT * FROM orders;
-- id | status  | created_at                    | priority
-- 1  | pending | 2026-03-19 14:30:00.123456+00 | 0

-- A complete e-commerce schema with all constraints:

CREATE TABLE categories (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE products (
    id           SERIAL PRIMARY KEY,
    name         VARCHAR(200) NOT NULL,
    description  TEXT,
    price        NUMERIC(10,2) NOT NULL CHECK (price > 0),
    stock        INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    category_id  INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    is_active    BOOLEAN DEFAULT true,
    created_at   TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE customers (
    id           SERIAL PRIMARY KEY,
    email        VARCHAR(254) UNIQUE NOT NULL,
    full_name    VARCHAR(200) NOT NULL,
    age          SMALLINT CHECK (age BETWEEN 13 AND 150),
    joined_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE orders (
    id           SERIAL PRIMARY KEY,
    customer_id  INTEGER NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    total        NUMERIC(12,2) NOT NULL CHECK (total >= 0),
    status       VARCHAR(20) DEFAULT 'pending'
                 CHECK (status IN ('pending','processing','shipped','delivered','cancelled')),
    ordered_at   TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
    order_id     INTEGER REFERENCES orders(id) ON DELETE CASCADE,
    product_id   INTEGER REFERENCES products(id) ON DELETE RESTRICT,
    quantity     INTEGER NOT NULL CHECK (quantity > 0),
    unit_price   NUMERIC(10,2) NOT NULL CHECK (unit_price > 0),
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE products (
    price NUMERIC(10,2) CONSTRAINT price_must_be_positive CHECK (price > 0)
);
-- Now the error says: violates constraint "price_must_be_positive"
-- Much clearer than "products_price_check"!

-- Method 1: CAST function (SQL standard)
SELECT CAST('42' AS INTEGER);           -- '42' (text) → 42 (number)
SELECT CAST(3.14159 AS INTEGER);       -- 3.14159 → 3 (truncated!)
SELECT CAST(NOW() AS DATE);            -- timestamp → just the date part

-- Method 2: :: operator (PostgreSQL shorthand — much more common)
SELECT '42'::INTEGER;                  -- Same as CAST('42' AS INTEGER)
SELECT 3.14159::INTEGER;              -- Same as CAST(3.14159 AS INTEGER)
SELECT NOW()::DATE;                   -- Same as CAST(NOW() AS DATE)

-- Practical examples:
SELECT '2026-03-19'::DATE;              -- Text → Date
SELECT 100::TEXT;                      -- Number → Text ('100')
SELECT 'true'::BOOLEAN;                -- Text → Boolean
SELECT 99.95::NUMERIC(5,1);           -- 99.95 → 100.0 (rounded to 1 decimal)

-- This works:
SELECT '123'::INTEGER;   -- ✅ 123

-- This fails:
SELECT 'hello'::INTEGER; -- ❌ ERROR: invalid input syntax for type integer: "hello"

-- This loses precision:
SELECT 99.999::INTEGER;  -- ⚠️ 100 (rounded up — the .999 is gone!)

-- Solution:
CREATE TABLE students (
    id              SERIAL PRIMARY KEY,
    full_name       VARCHAR(150) NOT NULL,
    email           VARCHAR(254) UNIQUE NOT NULL,
    age             SMALLINT CHECK (age BETWEEN 5 AND 120),
    gpa             NUMERIC(2,1) CHECK (gpa BETWEEN 0.0 AND 4.0),
    enrollment_date DATE DEFAULT CURRENT_DATE,
    is_active       BOOLEAN DEFAULT true
);

-- Solution:
CREATE TABLE authors (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(200) NOT NULL
);

CREATE TABLE books (
    id         SERIAL PRIMARY KEY,
    title      VARCHAR(300) NOT NULL,
    author_id  INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
    isbn       CHAR(13) UNIQUE,
    price      NUMERIC(8,2) CHECK (price >= 0),
    published  DATE
);

-- Solution:
SELECT '2026-12-25'::DATE;               -- 2026-12-25
SELECT 42::TEXT;                         -- '42'
SELECT NOW()::DATE;                      -- 2026-03-19
SELECT 3.14159::NUMERIC(5,2);            -- 3.14

-- BROKEN table — find the issues!
CREATE TABLE invoices (
    id          BIGINT,              -- Issue: No PRIMARY KEY, BIGINT overkill
    client_name CHAR(500),           -- Issue: CHAR wastes 500 bytes per row!
    amount      FLOAT,              -- Issue: FLOAT for money = rounding errors!
    phone       BIGINT,             -- Issue: Phone numbers aren't math!
    created_at  TIMESTAMP           -- Issue: No timezone info!
);

-- FIXED table:
CREATE TABLE invoices (
    id          SERIAL PRIMARY KEY,
    client_name VARCHAR(200) NOT NULL,
    amount      NUMERIC(12,2) NOT NULL CHECK (amount > 0),
    phone       VARCHAR(20),
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Solution:

-- Age in years:
SELECT name, EXTRACT(YEAR FROM AGE(birth_date)) AS age
FROM employees;

-- Tenure:
SELECT name, AGE(hire_date) AS tenure
FROM employees;

-- Birthday this month:
SELECT name, birth_date
FROM employees
WHERE EXTRACT(MONTH FROM birth_date) = EXTRACT(MONTH FROM CURRENT_DATE);

-- Probation end date (90 days after hire):
SELECT name, hire_date, hire_date + INTERVAL '90 days' AS probation_end
FROM employees;