List all Schemas/Tables/Columns in Redshift & Postgres

This is a post containing a query that’ll bring back all schemas, tables, and columns in Redshift or Postgres databases.

I’m combining the Redshift and Postgres database blog categories here… Redshift was forked from Postgres and is similar in a lot of ways – one feature difference that Redshift has is column-level access control, which is a good example of why this script would be useful. You’d want to review which columns contain sensitive data before applying restrictions, so you can run this and export as CSV first.

I mention the Redshift feature of applying column-level access control. In Postgres, I believe the only way of achieving this would be by creating and giving select access to views that do not contain the columns you wish to hide.

---

Script to Show all Schemas, Tables & Columns

The script below returns all schemas, tables, & columns within RedShift or Postgres.

select
	table_schema,
	table_name,
	ordinal_position as position,
	column_name,
	data_type,
	case when character_maximum_length is not null
		then character_maximum_length
		else numeric_precision end as max_length,
	is_nullable,
	column_default as default_value
from information_schema.columns
order by table_schema, table_name, ordinal_position;

Filter out system schemas (information_schema & pg_catalog) with a WHERE clause if you want to see schema & tables created by users.

---