Pro · Lesson 4 of 9

SQL queries

Redshift speaks a PostgreSQL-like SQL, with unenforced keys, SUPER values, and SYS catalog views.

Picture it this way

Redshift SQL looks like PostgreSQL from across the room. Up close, some furniture is missing and some is bolted to the floor. You can write SELECT, JOIN, and window functions. You should not assume every PostgreSQL feature arrived with them.

Write the SQL you can defend

Create tables in a schema you name, not only in public. Prefer explicit column lists. Primary keys and foreign keys are not enforced the way they are in PostgreSQL. The planner may use them as hints. They will not stop a bad load. Put the real checks in the pipeline that loads the table.

The SUPER type holds semi-structured values, with PartiQL paths. Use it for a payload you cannot flatten yet. Do not use it as an excuse to skip a model. Late-binding views resolve tables at query time, which is useful when a table is rebuilt by dropping and renaming. They can also hide a missing column until the query runs.

CREATE SCHEMA analytics;

CREATE TABLE analytics.orders (
    order_id BIGINT,
    ordered_at DATE,
    status VARCHAR(20),
    amount NUMERIC(12, 2)
);

SELECT DATE_TRUNC('month', ordered_at) AS month_start,
       SUM(amount) AS revenue
FROM analytics.orders
WHERE status = 'paid'
GROUP BY 1
ORDER BY 1;

Catalog views, old and current

Older tutorials query STL_ and SVL_ views. Current monitoring prefers the SYS_ views, including SYS_QUERY_HISTORY. Use the SYS views for new dashboards. Keep the STL queries only when you are reading an old runbook.

SELECT query_id, query_text, status, elapsed_time / 1000000.0 AS seconds
FROM sys_query_history
WHERE start_time > DATEADD(hour, -2, GETDATE())
ORDER BY start_time DESC
LIMIT 20;

Functions differ at the edges. GETDATE() is the usual clock. Some PostgreSQL-only extensions are absent. When a query from a PostgreSQL course fails, check the Redshift SQL reference before you assume the cluster is broken.

From a table to the catalog

Write familiar SQL, then use system views to learn about your own tables.

TablesRows in a schema
SQLPostgreSQL-style, plus analytics
System catalogSize, skew and sort state

Step 1 of 3Your data sits in tables inside schemas, just as in PostgreSQL.

SELECT * FROM public.sales LIMIT 10;

TablesRows in a schema
SQLPostgreSQL-style, plus analytics
System catalogSize, skew and sort state

Step 2 of 3Redshift SQL looks like PostgreSQL and adds analytic features such as window functions.

SELECT region, amount,
  RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rnk
FROM sales;

TablesRows in a schema
SQLPostgreSQL-style, plus analytics
System catalogSize, skew and sort state

Step 3 of 3System views describe the tables themselves: how big they are, how unevenly they are spread, and how much is unsorted.

SELECT "table", size, skew_rows, unsorted FROM svv_table_info;

Practice

Create a schema, a two-column table, and one grouped query. Then find that query in SYS_QUERY_HISTORY. If the view is empty, widen the time filter. You are looking at the warehouse's own log, not at a file on your laptop.

Knowledge check

Does a primary key stop duplicate loads?

Which view should a new monitoring query use?

What is SUPER for?

Official sources

Checked against vendor documentation in October 2026. Screens and plan names still change, so confirm the control in your own instance.