Ejemplos de consultas de catálogo

En las siguientes consultas, se muestran algunas de las formas en las que puede consultar las tablas de catálogos para obtener información útil sobre una base de datos de Amazon Redshift.

Vista del ID de la tabla, el nombre de la base de datos, los nombre del esquema y de la tabla

La siguiente definición de vista combina la tabla de sistema STV_TBL_PERM con las tablas de catálogos de sistemas PG_CLASS, PG_NAMESPACE y PG_DATABASE para devolver el ID de la tabla, el nombre de la base de datos y los nombres del esquema y de la tabla.

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;

En el siguiente ejemplo se devuelve la información para el ID de tabla 117855.

select * from tables_vw where table_id = 117855;

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

Cómo mostrar el número de columnas por tabla de Amazon Redshift

La siguiente consulta combina algunas tablas de catálogos para averiguar cuántas columnas contiene cada tabla de Amazon Redshift. Los nombres de tabla de Amazon Redshift se almacenan en PG_TABLES y STV_TBL_PERM; siempre que sea posible, utilice PG_TABLES para devolver nombres de tablas de Amazon Redshift.

Esta consulta no incluye tablas de 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)

Cómo mostrar los esquemas y las tablas en una base de datos

La siguiente consulta combina STV_TBL_PERM con algunas tablas PG para devolver una lista de tablas en la base de datos TICKIT y sus nombres de esquema (columna NSPNAME). La consulta también devuelve el número total de filas en cada tabla. (Esta consulta es útil cuando varios esquemas en su sistema tienen los mismos nombres de tablas).

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)

Cómo mostrar los ID de tablas, los tipos de datos y los nombres de las columnas y de las tablas

En la siguiente consulta se muestra información acerca de cada tabla de usuario y sus columnas: el ID de tabla, el nombre de la tabla, los nombres de sus columnas y el tipo de datos de cada columna:

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

Cómo contar el número de bloques de datos por cada columna en una tabla

La siguiente consulta combina la tabla STV_BLOCKLIST a PG_CLASS para devolver información de almacenamiento de las columnas en la tabla 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)