Esto es lo que yo hago cuando necesito conocer algunas consultas para acceder al catálogo. Espero te sirva.
[root@postgresql ~]# su - postgres -c "psql -E test"
psql (9.2.1)
Type "help" for help.
test=# \dt
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign
table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | test | table | postgres
(1 row)
test=# \d test
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(test)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************
********* QUERY **********
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers,
c.relhasoids, '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE
c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE c.oid = '38057';
**************************
********* QUERY **********
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull, a.attnum,
(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <>
t.typcollation) AS attcollation,
NULL AS indexdef,
NULL AS attfdwoptions
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '38057' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
**************************
********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,
pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '38057' ORDER
BY inhseqno;
**************************
********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,
pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '38057' ORDER
BY c.oid::pg_catalog.regclass::pg_catalog.text;
**************************
Table "public.test"
Column | Type | Modifiers
--------+---------+---------------------------------------------------
id | integer | not null default nextval('test_id_seq'::regclass)
cadena | text |
test=#
From: pgsql-es-ayuda-ow...@postgresql.org
[mailto:pgsql-es-ayuda-ow...@postgresql.org] On Behalf Of Arcel Labrada Batista
Sent: Wednesday, March 27, 2013 4:24 PM
Cc: pgsql-es-ayuda@postgresql.org
Subject: [pgsql-es-ayuda] consultas útiles al catalogo
buenas tardes,
necesito algún documento con consultas utiles al catalogo de postgres para
tenerlo, y por si no existe necesito de manera un poco mas urgente una consulta
que me devuelva algo como lo siguiente
nombre_tabla columnas
mitabla_1 col1t1,col2t1,col3t1
mitabla_2 col1t2,col2t2,col3t2
donde col1t1,col2t1,col3t1 son las columnas de mitabla_1, gracias de antemano
<http://www.uci.cu/> Description: Image removed by sender.
<http://www.uci.cu/> Description: Image removed by sender.
http://www.uci.cu
<<~WRD000.jpg>>
