🐘 PostgreSQL Lesson 02 — Tables & Data Types

1. PostgreSQL Data Types

2. CREATE TABLE

-- Users table
CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(255) NOT NULL UNIQUE,
  password   VARCHAR(255) NOT NULL,
  role       VARCHAR(20)  NOT NULL DEFAULT 'user',
  active     BOOLEAN      NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- Posts table with foreign key
CREATE TABLE posts (
  id         SERIAL PRIMARY KEY,
  user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title      VARCHAR(255) NOT NULL,
  body       TEXT,
  tags       TEXT[],          -- PostgreSQL ARRAY!
  metadata   JSONB,           -- flexible JSON storage
  published  BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tags table (many-to-many)
CREATE TABLE tags (
  id   SERIAL PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE post_tags (
  post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
  tag_id  INTEGER REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)   -- composite primary key
);

3. Constraints

-- Column constraints
CREATE TABLE products (
  id         SERIAL PRIMARY KEY,
  name       VARCHAR(200) NOT NULL,
  price      NUMERIC(10,2) NOT NULL CHECK (price >= 0),
  stock      INTEGER DEFAULT 0 CHECK (stock >= 0),
  sku        VARCHAR(50) UNIQUE,
  category   VARCHAR(100),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Table-level constraint (multiple columns)
CREATE TABLE order_items (
  order_id   INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  quantity   INTEGER NOT NULL CHECK (quantity > 0),
  price      NUMERIC(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

-- ALTER TABLE — add/remove after creation
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users ALTER COLUMN name SET NOT NULL;
ALTER TABLE users ADD CONSTRAINT users_email_check CHECK (email LIKE '%@%');
ALTER TABLE users RENAME COLUMN old_name TO new_name;

4. UUID Primary Key (Modern Pattern)

-- Enable uuid extension (PostgreSQL 13+: gen_random_uuid() built-in)
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- UUID primary key
CREATE TABLE users (
  id         UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMPTZ  DEFAULT NOW()
);

-- Insert: UUID auto-generated
INSERT INTO users (name, email) VALUES ('Ko Ko', 'ko@example.com');

-- Result: id = '550e8400-e29b-41d4-a716-446655440000'

-- SERIAL vs UUID:
-- SERIAL: 1, 2, 3... (sequential, easy to guess → security risk)
-- UUID:   random 128-bit → can't predict next ID → more secure

5. JSONB — PostgreSQL Power Feature

-- JSONB = binary JSON, indexed, fast operators
CREATE TABLE products (
  id       SERIAL PRIMARY KEY,
  name     VARCHAR(200),
  metadata JSONB    -- flexible attributes
);

-- Insert with JSON
INSERT INTO products (name, metadata) VALUES
('Laptop', '{"brand":"Dell","specs":{"ram":16,"storage":512},"tags":["gaming","work"]}'),
('Phone',  '{"brand":"Samsung","color":"black","warranty":2}');

-- Query JSON
SELECT metadata->>'brand'           FROM products;   -- text
SELECT metadata->'specs'->>'ram'    FROM products;   -- nested
SELECT metadata->'specs'->'ram'     FROM products;   -- JSONB value
SELECT * FROM products WHERE metadata->>'brand' = 'Dell';
SELECT * FROM products WHERE metadata @> '{"brand":"Dell"}';  -- contains

-- Update JSON field
UPDATE products SET metadata = metadata || '{"stock":100}'::jsonb WHERE id = 1;

-- Array type
CREATE TABLE articles (
  id   SERIAL PRIMARY KEY,
  title VARCHAR(200),
  tags  TEXT[]
);
INSERT INTO articles (title, tags) VALUES ('SCSS Guide', ARRAY['css','frontend','scss']);

-- Query arrays
SELECT * FROM articles WHERE 'css' = ANY(tags);
SELECT * FROM articles WHERE tags @> ARRAY['css','scss'];