目录查询示例
以下查询说明了用来查询目录表以获取有关 Amazon Redshift 数据库的有用信息的几种方法。
查看表 ID、数据库、架构和表名称
以下视图定义将 STV_TBL_PERM 系统表与 PG_CLASS、PG_NAMESPACE 和 PG_DATABASE 系统目录表联接以返回表 ID、数据库名称、schema 名称和表名称。
create view tables_vw as select distinct(stv_tbl_perm.id) table_id ,trim(pg_database.datname) db_name ,trim(pg_namespace.nspname) schema_name ,trim(pg_class.relname) table_name from stv_tbl_perm join pg_class on pg_class.oid = stv_tbl_perm.id join pg_namespace on pg_namespace.oid = pg_class.relnamespace join pg_database on pg_database.oid = stv_tbl_perm.db_id;
以下示例返回 ID 为 117855 的表的信息。
select * from tables_vw where table_id = 117855;
table_id | db_name | schema_name | table_name ---------+-----------+-------------+----------- 117855 | dev | public | customer
列出每个 Amazon Redshift 表的列数
以下查询联接了一些目录表,以了解每个 Amazon Redshift 表包含多少列。Amazon Redshift 表名称存储在 PG_TABLES 和 STV_TBL_PERM 中;如果可能,请使用 PG_TABLES 返回 Amazon Redshift 表名称。
此查询不涉及任何 Amazon Redshift 表。
select nspname, relname, max(attnum) as num_cols from pg_attribute a, pg_namespace n, pg_class c where n.oid = c.relnamespace and a.attrelid = c.oid and c.relname not like '%pkey' and n.nspname not like 'pg%' and n.nspname not like 'information%' group by 1, 2 order by 1, 2; nspname | relname | num_cols --------+----------+---------- public | category | 4 public | date | 8 public | event | 6 public | listing | 8 public | sales | 10 public | users | 18 public | venue | 5 (7 rows)
列出数据库中的架构和表
以下查询将 STV_TBL_PERM 联接到一些 PG 表以返回 TICKIT 数据库中的表及其 schema 名称(NSPNAME 列)的列表。该查询还返回每个表中的总行数。(在系统中的多个 schemas 具有相同的表名称时,此查询很有用。)
select datname, nspname, relname, sum(rows) as rows from pg_class, pg_namespace, pg_database, stv_tbl_perm where pg_namespace.oid = relnamespace and pg_class.oid = stv_tbl_perm.id and pg_database.oid = stv_tbl_perm.db_id and datname ='tickit' group by datname, nspname, relname order by datname, nspname, relname; datname | nspname | relname | rows --------+---------+----------+-------- tickit | public | category | 11 tickit | public | date | 365 tickit | public | event | 8798 tickit | public | listing | 192497 tickit | public | sales | 172456 tickit | public | users | 49990 tickit | public | venue | 202 (7 rows)
列出表 ID、数据类型、列名称和表名称
以下查询列出有关每个用户表及其列的一些信息:表 ID、表名称、表列名称和每个列的数据类型:
select distinct attrelid, rtrim(name), attname, typname from pg_attribute a, pg_type t, stv_tbl_perm p where t.oid=a.atttypid and a.attrelid=p.id and a.attrelid between 100100 and 110000 and typname not in('oid','xid','tid','cid') order by a.attrelid asc, typname, attname; attrelid | rtrim | attname | typname ---------+----------+----------------+----------- 100133 | users | likebroadway | bool 100133 | users | likeclassical | bool 100133 | users | likeconcerts | bool ... 100137 | venue | venuestate | bpchar 100137 | venue | venueid | int2 100137 | venue | venueseats | int4 100137 | venue | venuecity | varchar ...
计算表中的每个列的数据块数
以下查询将 STV_BLOCKLIST 表联接到 PG_CLASS 以返回 SALES 表中列的存储信息。
select col, count(*) from stv_blocklist s, pg_class p where s.tbl=p.oid and relname='sales' group by col order by col; col | count ----+------- 0 | 4 1 | 4 2 | 4 3 | 4 4 | 4 5 | 4 6 | 4 7 | 4 8 | 4 9 | 8 10 | 4 12 | 4 13 | 8 (13 rows)