Exemplos de consultas de catálogo

As consultas a seguir mostram algumas das maneiras pelas quais você pode consultar as tabelas do catálogo para obter informações úteis sobre um banco de dados do Amazon Redshift.

ID da tabela de visualização, banco de dados, esquema e nome da tabela

A definição a seguir une a tabela de sistema STV_TBL_PERM às tabelas de catálogos de sistema PG_CLASS, PG_NAMESPACE e PG_DATABASE para retornar o ID da tabela, o nome do banco de dados, o nome do esquema e o nome da tabela.

create view tables_vw as
select distinct(id) table_id
,trim(datname)   db_name
,trim(nspname)   schema_name
,trim(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 = relnamespace
join pg_database on pg_database.oid = stv_tbl_perm.db_id;

O exemplo a seguir retorna as informações do ID da tabela 117855.

select * from tables_vw where table_id = 117855;

table_id | db_name   | schema_name | table_name
---------+-----------+-------------+-----------
  117855 |       dev | public      | customer  

Listar o número de colunas por tabela do Amazon Redshift

A consulta a seguir une algumas tabelas de catálogo para descobrir quantas colunas cada tabela do Amazon Redshift contém. Os nomes de tabela do Amazon Redshift são armazenados em PG_TABLES e STV_TBL_PERM; sempre que possível, use PG_TABLES para retornar nomes de tabela do Amazon Redshift.

Essa consulta não envolve tabelas do 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)

Listar os esquemas e as tabelas em um banco de dados

A consulta a seguir une STV_TBL_PERM a algumas tabelas PG para retornar uma lista de tabelas no banco de dados TICKIT e os nomes de esquema (coluna NSPNAME). A consulta também retorna o número total de linhas em cada tabela. (Essa consulta é útil quando vários esquemas no sistema têm os mesmos nomes de tabela.)

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)

IDs de tabela de listas, tipos de dados, nomes de colunas e nomes de tabelas

A consulta a seguir lista algumas informações sobre cada tabela de usuários e as colunas: ID da tabela, o nome da tabela, os nomes de coluna e o tipo de dados de cada coluna:

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
...

Contar o número de blocos de dados de cada coluna em uma tabela

A consulta a seguir une a tabela STV_BLOCKLIST a PG_CLASS para retornar informações de armazenamento das colunas na tabela 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)