SQL Data Types Reference: Complete Guide

-- Fixed-length string (pads with spaces)
CREATE TABLE users (
  country_code CHAR(2),        -- Always 2 characters: 'US', 'UK'
  username VARCHAR(50),        -- Variable, up to 50 chars
  bio TEXT                     -- Unlimited length text
);

-- VARCHAR vs CHAR: VARCHAR saves space, CHAR is faster for fixed-length
INSERT INTO users VALUES ('US', 'john_doe', 'Software developer');

-- String functions work with all text types
SELECT username, LENGTH(username), UPPER(username)
FROM users;

-- Integer types: SMALLINT, INTEGER, BIGINT
CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,     -- Auto-incrementing integer
  quantity SMALLINT,                 -- -32,768 to 32,767
  views_count INTEGER,               -- -2 billion to 2 billion
  total_sales BIGINT                 -- Very large numbers
);

-- Decimal types: DECIMAL, NUMERIC (exact), FLOAT, REAL (approximate)
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  subtotal DECIMAL(10, 2),          -- 10 digits total, 2 after decimal
  tax_rate NUMERIC(5, 4),           -- 0.0825 (8.25%)
  weight FLOAT                       -- Approximate for scientific data
);

-- DECIMAL for money (exact), FLOAT for measurements (approximate)
INSERT INTO orders VALUES (1, 199.99, 0.0825, 2.456);

-- Date and time types
CREATE TABLE events (
  event_id INTEGER PRIMARY KEY,
  event_date DATE,                   -- 2025-01-10 (no time)
  start_time TIME,                   -- 14:30:00 (no date)
  created_at TIMESTAMP,              -- 2025-01-10 14:30:00
  updated_at TIMESTAMPTZ             -- With timezone (PostgreSQL)
);

-- Insert date/time values
INSERT INTO events VALUES (
  1,
  '2025-06-15',
  '14:30:00',
  '2025-01-10 14:30:00',
  '2025-01-10 14:30:00-05:00'
);

-- Extract components and perform date math
SELECT
  event_date,
  EXTRACT(YEAR FROM event_date) as year,
  event_date + INTERVAL '7 days' as one_week_later
FROM events;

-- Boolean type
CREATE TABLE users (
  user_id INTEGER PRIMARY KEY,
  email VARCHAR(100),
  is_active BOOLEAN DEFAULT TRUE,
  is_verified BOOLEAN DEFAULT FALSE,
  is_admin BOOLEAN DEFAULT FALSE
);

-- Boolean values: TRUE, FALSE, NULL
INSERT INTO users VALUES (1, 'user@example.com', TRUE, FALSE, FALSE);

-- Query with boolean conditions
SELECT email, is_active
FROM users
WHERE is_active = TRUE AND is_verified = FALSE;

-- MySQL: Use TINYINT(1) or BIT(1) as boolean substitute
CREATE TABLE settings (
  setting_id INT PRIMARY KEY,
  enabled TINYINT(1) DEFAULT 1    -- 0 = false, 1 = true
);

-- PostgreSQL: JSON and JSONB types
CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  name VARCHAR(100),
  specifications JSONB,             -- Binary JSON (faster, indexable)
  metadata JSON                     -- Text JSON
);

-- Insert JSON data
INSERT INTO products VALUES (
  1,
  'Laptop',
  '{"brand": "Dell", "ram": "16GB", "storage": "512GB SSD"}',
  '{"tags": ["electronics", "computers"], "rating": 4.5}'
);

-- Query JSON fields
SELECT name, specifications->>'brand' as brand
FROM products
WHERE specifications->>'ram' = '16GB';

-- MySQL: Use JSON type (available in MySQL 5.7+)
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_data JSON
);

-- Binary types for files and encrypted data
CREATE TABLE attachments (
  attachment_id INTEGER PRIMARY KEY,
  filename VARCHAR(255),
  file_data BYTEA,                  -- PostgreSQL: binary data
  file_size INTEGER,
  mime_type VARCHAR(100)
);

-- MySQL: Use BLOB types
CREATE TABLE files (
  file_id INT PRIMARY KEY,
  file_name VARCHAR(255),
  file_content BLOB,                -- Up to 65KB
  large_file MEDIUMBLOB             -- Up to 16MB
);

-- Store encrypted passwords (use hashing, not encryption)
CREATE TABLE users (
  user_id INTEGER PRIMARY KEY,
  username VARCHAR(50),
  password_hash BYTEA               -- bcrypt/argon2 hash
);

-- Note: Storing files in database is often not recommended
-- Consider storing file paths and using object storage (S3, etc.)

-- UUID for globally unique identifiers
CREATE TABLE sessions (
  session_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id INTEGER,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ENUM for predefined values
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered');
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  status order_status DEFAULT 'pending'
);

-- PostgreSQL: Array types
CREATE TABLE articles (
  article_id INTEGER PRIMARY KEY,
  title VARCHAR(200),
  tags TEXT[]                       -- Array of text values
);

INSERT INTO articles VALUES (1, 'SQL Guide', ARRAY['sql', 'database', 'tutorial']);

-- Query array data
SELECT title FROM articles WHERE 'sql' = ANY(tags);