Redshift Cheat Sheet

CREATE TABLE orders (
  id BIGINT IDENTITY(1,1),
  customer_id BIGINT NOT NULL,
  total DECIMAL(10,2),
  created_at TIMESTAMP DEFAULT GETDATE()
)
DISTKEY (customer_id)
SORTKEY (created_at);

CREATE TABLE orders_backup AS SELECT * FROM orders;

ALTER TABLE orders RENAME TO customer_orders;

ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'new';

ALTER TABLE orders RENAME COLUMN total TO amount;

ALTER TABLE orders DROP COLUMN status;

ALTER TABLE orders ALTER COLUMN status TYPE VARCHAR(64);  -- wider only

TRUNCATE TABLE orders;

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position;

SELECT ddl FROM admin.v_generate_tbl_ddl WHERE tablename = 'orders';

INSERT INTO orders (customer_id, total)
VALUES (1, 49.90), (2, 12.00);

UPDATE orders SET status = 'shipped' WHERE id = 42;

UPDATE orders
SET total = t.amount
FROM order_totals t
WHERE t.order_id = orders.id;

DELETE FROM orders WHERE created_at < DATEADD(year, -2, GETDATE());

BEGIN;
DELETE FROM orders USING orders_staging s WHERE orders.id = s.id;
INSERT INTO orders SELECT * FROM orders_staging;
END;

CREATE TABLE orders_dedup AS
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_at DESC) AS rn
  FROM orders
) WHERE rn = 1;

COPY orders
FROM 's3://my-bucket/orders/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoad'
FORMAT AS CSV IGNOREHEADER 1;

UNLOAD ('SELECT * FROM orders')
TO 's3://my-bucket/export/orders_'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftUnload'
FORMAT AS PARQUET;

SELECT query, filename, line_number, colname, err_reason
FROM stl_load_errors ORDER BY starttime DESC LIMIT 10;

CREATE OR REPLACE VIEW recent_orders AS
SELECT * FROM orders WHERE created_at > DATEADD(day, -30, GETDATE());

CREATE MATERIALIZED VIEW daily_totals AS
SELECT DATE_TRUNC('day', created_at) AS day, SUM(total) AS total
FROM orders GROUP BY 1;

REFRESH MATERIALIZED VIEW daily_totals;

SELECT pg_get_viewdef('recent_orders'::regclass, true);

SELECT CURRENT_DATE, GETDATE(), SYSDATE;

SELECT DATEADD(day, 7, created_at)                AS due,
       DATEDIFF(hour, created_at, delivered_at)   AS hours
FROM orders;

SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;

SELECT DATEADD(day, -n, CURRENT_DATE) AS day
FROM (SELECT ROW_NUMBER() OVER () - 1 AS n FROM stl_scan LIMIT 90);

SELECT SPLIT_PART(email, '@', 2) AS domain FROM customers;

SELECT customer_id, LISTAGG(sku, ', ') WITHIN GROUP (ORDER BY sku)
FROM order_items GROUP BY customer_id;

CREATE SCHEMA IF NOT EXISTS staging;

SELECT schemaname, tablename FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

GRANT USAGE ON SCHEMA public TO analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analyst;

SELECT "table", size AS mb, pct_used, skew_rows
FROM svv_table_info ORDER BY size DESC;

VACUUM DELETE ONLY orders;
VACUUM FULL orders;

ANALYZE orders;

SELECT pid, user_name, starttime, query FROM stv_recents WHERE status = 'Running';

CANCEL 12345;                 -- by pid
SELECT pg_terminate_backend(12345);

SELECT version(), current_database(), current_user;