sql

-- Enable pg_stat_statements extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all

-- Product.active.touch_all(:cache_synced_at) for catalog 42
UPDATE "products"
SET "updated_at" = '2024-05-01 12:00:00.123456',
    "cache_synced_at" = '2024-05-01 12:00:00.123456'
WHERE "products"."catalog_id" = 42
  AND "products"."status" = 'active';

ALTER TABLE documents ADD COLUMN version BIGINT NOT NULL DEFAULT 0;

-- Basic CTE
WITH high_value_customers AS (
  SELECT
    user_id,
    SUM(total) as lifetime_value
  FROM orders

-- Basic query debugging with EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- Output shows query plan:
-- Seq Scan on users (cost=0.00..25.00 rows=1 width=100)
--   Filter: (email = 'test@example.com'::text)

CREATE TABLE daily_events (
    id          BIGSERIAL PRIMARY KEY,
    category    TEXT        NOT NULL,
    item_id     BIGINT      NOT NULL,
    score       NUMERIC(10,2) NOT NULL DEFAULT 0,
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT now()

-- Import CSV with COPY (fastest method)
COPY users (username, email, age, created_at)
FROM '/path/to/users.csv'
WITH (
  FORMAT csv,
  HEADER true,

INSERT INTO snip_counters (snip_id, views)
VALUES ($1, 1)
ON CONFLICT (snip_id)
DO UPDATE SET views = snip_counters.views + 1;

-- Install postgres_fdw extension
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Create foreign server
CREATE SERVER remote_db
  FOREIGN DATA WRAPPER postgres_fdw

-- INNER JOIN: Only matching rows
SELECT
  users.name,
  orders.order_number,
  orders.total
FROM users

CREATE TABLE IF NOT EXISTS idempotency_keys (
  id BIGSERIAL PRIMARY KEY,
  key TEXT NOT NULL UNIQUE,
  request_hash TEXT NOT NULL,
  response_json JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

-- Basic VACUUM (reclaims dead tuple space)
VACUUM users;

-- VACUUM all tables in database
VACUUM;