Esempi di query di catalogo

Le query seguenti illustrano alcuni modi per eseguire query sulle tabelle di catalogo per ottenere informazioni utili su un database di Amazon Redshift.

Visualizza ID di tabella, database, schema e nome della tabella

La definizione di vista seguente unisce la tabella di sistema STV_TBL_PERM alle tabelle di catalogo di sistema PG_CLASS, PG_NAMESPACE e PG_DATABASE per restituire ID di tabella, nome del database, nome dello schema e nome della tabella.

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;

L'esempio seguente restituisce le informazioni per la tabella con ID 117855.

select * from tables_vw where table_id = 117855;

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

Visualizzazione di un elenco del numero di colonne per la tabella di Amazon Redshift

La query seguente unisce alcune tabelle di catalogo per scoprire quante colonne contiene ogni tabella Amazon Redshift. I nomi delle tabelle di Amazon Redshift sono archiviati sia in PG_TABLES che in STV_TBL_PERM. Quando possibile, utilizzare PG_TABLES per restituire i nomi delle tabelle di Amazon Redshift.

Questa query non usa tabelle di 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)

Elenca gli schemi e le tabelle in un database

La query seguente unisce STV_TBL_PERM ad alcune tabelle PG per restituire un elenco delle tabelle nel database TICKIT con i relativi nomi di schemi (colonna NSPNAME). La query restituisce anche il numero totale di righe in ogni tabella. (Questa query è utile quando più schemi nel sistema hanno gli stessi nomi di tabella.)

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)

Elenca ID di tabella, tipi di dati, nomi di colonna e nomi di tabella

La query seguente elenca alcune informazioni su ogni tabella utente e sulle relative colonne: ID di tabella, nome di tabella, nomi delle colonne e tipo di dati per ogni colonna:

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

Conteggio del numero di blocchi di dati per ogni colonna in una tabella

La seguente query unisce la tabella STV_BLOCKLIST a PG_CLASS per restituire informazioni sullo storage per le colonne della tabella 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)