Terminal
Terminal/Skill
Guides Redshift SQL, COPY and UNLOAD, system views and operations, correcting PostgreSQL habits that do not work on Redshift.
By Amazon Web Services
Works with
npx skills add aws/agent-toolkit-for-aws --skill redshift-guideRedshift 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.
Works best with the AWS MCP server — it runs the AWS CLI and Redshift Data API calls below in a sandboxed, audit-logged environment. All guidance here is plain AWS CLI and SQL and works without it.
Establish this before answering — APIs, system tables, and capabilities differ. Take it
from the question when it says which one; ask when it does not. SELECT version()
does not identify it.
--workgroup-name; the user says "workgroup"/"Serverless".--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 |
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(day, -30, GETDATE()), DATEDIFF(day, start, end).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.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).TOP N and LIMIT N both work (TOP N PERCENT does not). A text column becomes VARCHAR(256) — use VARCHAR(max) or explicit length.CREATE TABLE ... USING ICEBERG (not STORED AS ICEBERG, not TABLE_FORMAT=ICEBERG).references/redshift-sql-metadata.md for requirements and limits.BLOCK: DROP DATABASE, DELETE without WHERE, publicly-accessible=true, GRANT ALL ON ALL WARN then confirm: RESIZE, RESTORE, VACUUM on large tables, ALTER PASSWORD, WLM config change Confirm: CREATE, GRANT specific, COPY, UNLOAD
Apply these defaults when generating anything that connects, loads, or exports. Details are in the reference files noted.
require_ssl
parameter and connect with sslmode=verify-full so the server certificate is checked.ENCRYPTED KMS_KEY_ID '<arn>' to UNLOAD — it writes query results to S3, outside
Redshift's own encryption. → references/redshift-sql-ddl-copy.mdSecretArn (Secrets Manager) or IAM Identity Center; DbUser
is acceptable because it issues temporary credentials. Never place database passwords in
code, environment variables, or SQL text. → references/redshift-sql-recipes-load-api.mdIAM_ROLE to the specific bucket and prefix
(s3:GetObject on arn:aws:s3:::<bucket>/<prefix>/*), not s3:* or a managed
full-access policy, and condition its trust policy on both aws:SourceArn (the
cluster/namespace ARN) and aws:SourceAccount — SourceArn alone still allows another
resource in the account to assume it. Grant per-object privileges rather than
GRANT ALL ON ALL.redshift-data:* API calls but not the SQL executed;
enable Redshift audit logging (useractivitylog, connectionlog, userlog) for that.
Both capture query text and user activity, so encrypt every destination in use:
the CloudWatch Logs group (aws logs associate-kms-key), the CloudTrail trail
(SSE-KMS), and the audit-log S3 bucket (SSE-S3 — audit logging to S3 supports only
S3-managed keys, not KMS). Serverless only supports sending audit logs to CloudWatch.PubliclyAccessible=false and connect over a VPC
endpoint. Do not open port 5439 to 0.0.0.0/0 or ::/0 — scope inbound rules to
specific CIDRs or to a referencing security group.sys_load_error_detail can
echo fragments of rejected rows, so treat statement IDs and load-error output as
sensitive.MANDATORY: When a question matches a row below, you MUST load and read the referenced file BEFORE answering.
Ask whether the target is provisioned or Serverless before giving troubleshooting steps — unless the question already says which one, in which case use that and do not re-confirm.
| User Intent | Route To |
|---|---|
| "CREATE TABLE", "DISTKEY/SORTKEY", "ENCODE", "IDENTITY", "COPY", "UNLOAD", "IAM_ROLE", "Iceberg table" | references/redshift-sql-ddl-copy.md |
| "LISTAGG", "DATEADD/DATEDIFF", "NVL/DECODE", "type mapping", "text type", "VARBYTE", "recursive CTE" | references/redshift-sql-functions-types.md |
| "QUALIFY", "PIVOT/UNPIVOT", "MERGE", "TOP N", "SUBSTR error", "UNIQUE/PK not enforced", "trailing blanks", "leader-node function", "JSON", "SUPER", "PartiQL", "nested/semi-structured data" | references/redshift-sql-extensions-semantics.md |
| "system view", "SVV_/SYS_", "SHOW commands", "STL vs SYS", "list tables", "distkey/sortkey lookup", "datashare discovery", "2-part vs 3-part", "permission denied", "GRANT", "privileges", "relation/table does not exist" | references/redshift-sql-metadata.md |
| "how do I write SQL", "PostgreSQL vs Redshift", "which SQL reference", general dialect question | references/redshift-sql-syntax.md (index of the 6 SQL references + PostgreSQL-vs-Redshift failure table) |
| "COPY failed", "load error", "Data API poll", "async query", "Data API throttle" | references/redshift-sql-recipes-load-api.md |
| "materialized view", "MV refresh", "AUTO REFRESH", "stale view" | references/redshift-sql-materialized-views.md |
| General Redshift question not matching above | Answer directly from general knowledge |
| Aurora, RDS, DynamoDB, Athena (non-Redshift) | REFUSE. State this skill is for Amazon Redshift only. Do not provide guidance for other database services. |
→ Load references/redshift-sql-recipes-load-api.md before answering ANY Data API, COPY-error, or async-query question. It carries the bounded poll loop, the HasResultSet and ResourceNotFoundException handling, the per-target parameters, and the auth options.
Data API calls are async by default — use long polling (--wait-time-seconds, 1–30)
rather than blind sleeps, and keep a bounded loop for work that can exceed 30s.
Serverless takes --workgroup-name, provisioned takes --cluster-identifier.