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,

CREATE TABLE daily_metrics (
    id          BIGGENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id   BIGINT      NOT NULL,
    metric      TEXT        NOT NULL,
    day         DATE        NOT NULL,
    count       BIGINT      NOT NULL DEFAULT 0,

-- 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 idempotency_keys (
    request_key         text        NOT NULL,
    endpoint            text        NOT NULL,
    request_fingerprint text        NOT NULL,
    status              text        NOT NULL DEFAULT 'in_progress'
                                    CHECK (status IN ('in_progress', 'completed')),

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

-- VACUUM all tables in database
VACUUM;