Exemples de requêtes de catalogue

Les requêtes suivantes illustrent quelques-unes des manières dont vous pouvez interroger les tables du catalogue pour obtenir des informations utiles sur une base de données Amazon Redshift.

Afficher l'ID de la table, la base de données, le schéma et le nom de la table

La définition de vue suivante joint la table système STV_TBL_PERM aux tables catalogue système PG_CLASS, PG_NAMESPACE et PG_DATABASE pour renvoyer l'ID de la table, le nom de la base de données, le nom du schéma et le nom de la table.

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;

L'exemple suivant renvoie les informations concernant l'ID de table 117855.

select * from tables_vw where table_id = 117855;

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

Répertorier le nombre de colonnes par table Amazon Redshift

La requête suivante joint certaines tables de catalogue pour connaître le nombre de colonnes que contient chaque table Amazon Redshift. Les noms de table Amazon Redshift sont stockés à la fois dans PG_TABLES et STV_TBL_PERM ; lorsque cela est possible, utilisez PG_TABLES pour renvoyer les noms de table Amazon Redshift.

Cette requête n'implique pas de table 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)

Répertorier les schémas et les tables dans une base de données

La requête suivante joint STV_TBL_PERM à quelques tables PG pour renvoyer une liste de tables de la base de données TICKIT et leurs noms de schéma (colonne NSPNAME). La requête renvoie également le nombre total de lignes de chaque table. (Cette requête est utile lorsque plusieurs schémas de votre système portent des noms de tables identiques.)

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)

Répertorier les ID de tables, les types de données, les noms de colonnes et les noms de tables

La requête suivante répertorie quelques informations sur chaque table d'utilisateur et ses colonnes : l'ID de la table, le nom de la table, les noms de ses colonnes et le type de données de chaque colonne :

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

Compter le nombre de blocs de données de chaque colonne dans une table

La requête suivante joint la table STV_BLOCKLIST à PG_CLASS pour renvoyer des informations de stockage sur les colonnes de la table 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)