Querying the Catalog Tables
In general, you can join catalog tables and views (relations whose names begin with
PG_) to Amazon Redshift tables and views.
The catalog tables use a number of data types that Amazon Redshift does not support. The following data types are supported when queries join catalog tables to Amazon Redshift tables:
For example, the PG_STATS table cannot be queried in a join with an Amazon Redshift table because of unsupported functions.
The following catalog tables and views provide useful information that can be joined to information in Amazon Redshift tables. Some of these tables allow only partial access because of data type and function restrictions. When you query the partially accessible tables, select or reference their columns carefully.
The following tables are completely accessible and contain no unsupported types or functions:
The following tables are partially accessible and contain some unsupported types, functions, and truncated text columns. Values in text columns are truncated to varchar(256) values.
The catalog tables that are not listed here are either inaccessible or unlikely to be useful to Amazon Redshift administrators. However, you can query any catalog table or view openly if your query does not involve a join to an Amazon Redshift table.
You can use the OID columns in the Postgres catalog tables as joining columns. For
example, the join condition
pg_database.oid = stv_tbl_perm.db_id matches
the internal database object ID for each PG_DATABASE row with the visible DB_ID column
in the STV_TBL_PERM table. The OID columns are internal primary keys that are not
visible when you select from the table. The catalog views do not have OID