How to View Schema in Redshift (PostgreSQL)

How do I view tables and columns in an Amazon Redshift schema?

The command shows tables, columns, and data types in an Amazon Redshift schema by querying catalog views such as information_schema.tables and pg_table_def.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

How do I list all tables in a Redshift schema?

Run a SELECT on information_schema.tables filtering by table_schema.This view is ANSI-standard, always up to date, and works even when compression encodings change.

Query using information_schema.tables

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name;

Query using pg_table_def

SELECT tablename FROM pg_table_def WHERE schemaname = 'public' GROUP BY tablename; This works on external & temporary tables, but excludes views.

How can I view columns and data types for one table?

Query information_schema.columns or pg_table_def, passing both schema and table name.The rows include column name, data type, encoding, and sort key flags.

Example

SELECT column_name, data_type FROM information_schema.columns WHERE table_schema='public' AND table_name='orders';

When should I use pg_table_def over INFORMATION_SCHEMA?

Use pg_table_def when you need encoding, diststyle, or sortkey details. Use information_schema for portable SQL and faster metadata scans.

Best practices for inspecting schemas

1. Always filter by table_schema to avoid scanning thousands of system tables.
2. Order results alphabetically for readability.
3. Cache frequent metadata queries in your IDE or script.
4.Grant USAGE on schema to analysts instead of full SELECT on every table.

Common mistakes to avoid

Ignoring case sensitivity

Redshift folds un-quoted identifiers to lower-case. Quoting mixed-case table names forces exact matches. Fix by using lower-case or quoting consistently.

Querying pg_table_def on very large clusters

pg_table_def scans every node and can be slow. Filter by schemaname and tablename or switch to information_schema for faster performance.

FAQ

Does SHOW TABLES work in Redshift?

No.Use \dt in psql or the SELECT statements above.

Can I see schemas across databases?

You must connect to each database individually; catalog views do not cross database boundaries.

How do I restrict schema visibility to roles?

Revoke USAGE on the schema and grant it only to specific roles. Tables inside become invisible without USAGE.

Why How to View Schema in Redshift (PostgreSQL) is important

How to View Schema in Redshift (PostgreSQL) Example Usage


-- Find total columns and their data types for the Orders table
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'orders'
ORDER BY ordinal_position;

How to View Schema in Redshift (PostgreSQL) Syntax


-- List tables in a specific schema
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

-- List columns for Customers table
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'customers';

-- Include Redshift-specific details
SELECT tablename, "column", type, encoding, distkey, sortkey
FROM pg_table_def
WHERE schemaname = 'public' AND tablename = 'orders';

Common Mistakes

Querying pg_table_def without filters: This scans every node and slows down on large clusters. Always filter by schemaname and tablename or switch to information_schema.
Quoting some identifiers but not others: Mixed quoting makes names case-sensitive and causes "relation not found" errors. Adopt a consistent lower-case, unquoted style.