/redshift-guide

name: redshift-guide
description: "Amazon Redshift is NOT PostgreSQL; corrects LLM mistakes; covers SQL, metadata, connectivity/auth, storage, query performance, and operations. Applies ONLY to Redshift (provisioned or Serverless). Pushes back on: CREATE INDEX, string_agg, pg_catalog, text type, SERIAL, stl_query. Triggers on: Redshift SQL, Redshift CREATE TABLE, Redshift COPY/UNLOAD, slow or suddenly slow Redshift query, EXPLAIN plan, queue wait, stale statistics/ANALYZE, result cache, Redshift permission denied, Redshift disk full, storage growth, snapshot/backup cost, ghost rows/bloat, VACUUM, spill to disk, distribution skew, Redshift system views, QUALIFY, PIVOT, MERGE, materialized views, Redshift Data API (Python/boto3), Redshift workload management (WLM), concurrency scaling, Redshift resize, Spectrum, awsdatacatalog, connection failures (SSL/TLS, ODBC/JDBC, connection limit), IAM/SAML federation, IAM Identity Center, datashares. Does NOT apply to: S3 policies, Athena, Glue/Iceberg outside Redshift, Aurora, RDS, DynamoDB."
metadata:
  version: "2"

Amazon Redshift Guide

Redshift is NOT PostgreSQL (read first)

Redshift speaks PostgreSQL's wire protocol and shares much of its surface syntax, so LLMs assume PostgreSQL behavior carries over — it frequently does not. Divergences span system tables (`pg_catalog` is incomplete), DDL (no indexes, no sequences), functions (`string_agg`, `SUBSTR` on tables, leader-node-only functions), types (a `text` column becomes VARCHAR(256)), and comparison semantics (trailing blanks, unenforced constraints). **Assume divergence and verify against the reference below — do not answer from PostgreSQL habit.** Common PostgreSQL→Redshift divergences are in `references/redshift-sql-syntax.md`.

**This guide ships with this server.** Run the SQL it recommends, SHOW commands and system views included, through `execute_query`, one statement per call. The `list_*` tools discover clusters, databases, schemas, tables and columns, and `review_cluster` runs a cluster review. To load a reference this guide cites, call `retrieve_skill_file` with the path exactly as cited. This server runs no AWS CLI command: give the user the ones this guide shows, or run them with another tool you have. The database user this server connects as sees only its own rows (the queries it generated) in the system views, with no error, unless it is a superuser or has `sys:monitor` or `SYSLOG ACCESS UNRESTRICTED`; some views refuse it with `permission denied` instead → `references/redshift-auth-authorization.md`.

STEP 0: Serverless or Provisioned?

Establish this before answering — APIs, system tables, and capabilities differ. Take it from the question when it says which one, else from the `type` of the target's `list_clusters` entry; **ask** only when neither settles it. `SELECT version()` does not identify it.

• **Serverless** — identified by a *workgroup* (and namespace). Data API calls take

`--workgroup-name`; the user says "workgroup"/"Serverless".

• **Provisioned** — identified by a *cluster*. Data API calls take

`--cluster-identifier`; the user says "cluster".

| Target | System Views | Credentials API | |---|---|---| | **Provisioned** | SYS_, all SVV_ + STL_, STV_, SVL_, SVCS_ (single-AZ only — disabled on Multi-AZ) | `redshift:GetClusterCredentials` | | **Serverless** | `SYS_` + a subset of `SVV_` ONLY (no STL/STV/SVL/SVCS) | `redshift-serverless:GetCredentials` |

Critical Facts

• **SHOW commands are the primary metadata interface** — SHOW DATABASES, SHOW SCHEMAS, SHOW TABLES, SHOW COLUMNS, SHOW TABLE, SHOW VIEW. Do NOT default to pg_catalog or information_schema. → **Load `references/redshift-sql-metadata.md` for metadata/discovery questions and any "relation does not exist" report** — it has the diagnostic flow.
• **`SYS_` views are the preferred system views** — they work everywhere. `STL_`, `STV_`, `SVL_`, and `SVCS_` are provisioned single-AZ only, and some `SVV_` views are unsupported on Serverless. → **Load `references/redshift-sql-metadata.md` for any system-view or monitoring question.**
• **`sys_load_error_detail`** for COPY debugging (not `stl_load_errors`, which is provisioned single-AZ only).
• **DATEADD/DATEDIFF** — unit-first argument order: `DATEADD(day, -30, GETDATE())`, `DATEDIFF(day, start, end)`.
• **APPROXIMATE COUNT(DISTINCT col)** — Redshift-specific, ~2% error, much faster than exact COUNT(DISTINCT) on large datasets.
• **MERGE ... REMOVE DUPLICATES** — simplified dedup when source and target have identical schemas.
• **COPY should use IAM_ROLE** (the namespace role, not the caller role) + supports MANIFEST for explicit file lists + MAXERROR for error tolerance.
• **`SUBSTR()` is leader-node-only** — works on literals but errors on table columns (`SUBSTR() function is not supported (Hint: use SUBSTRING instead)`). Use `SUBSTRING()` on columns.
• **UNIQUE / PRIMARY KEY / FOREIGN KEY are informational only** — NOT enforced (duplicate rows are accepted with no error). Optimizer hints; enforce integrity in the application or via MERGE. `NOT NULL` IS enforced.
• **`SHOW VIEW <schema.name>`** returns the definition of a regular view, materialized view, or late-binding view. MV freshness: `SVV_MV_INFO` (`is_stale`).
• **No `FILTER (WHERE ...)` on aggregates** — PostgreSQL-only, Redshift rejects it. Write `COUNT(CASE WHEN cond THEN 1 END)` / `SUM(CASE WHEN cond THEN x END)` instead. This applies to every query you emit, including ones you derive from a reference example.
• **`TOP N` and `LIMIT N` both work** (`TOP N PERCENT` does not). A `text` column becomes `VARCHAR(256)` — use `VARCHAR(max)` or explicit length.
• **Iceberg tables use `CREATE TABLE ... USING ICEBERG`** (not `STORED AS ICEBERG`, not `TABLE_FORMAT=ICEBERG`).
• **Datashares support read and write operations** — consumers can write once the producer grants write privileges. Treat "permission denied" on a datashare write as a **missing grant**, not an unsupported operation