¿Cómo obtener la consulta de atributos de columna del nombre de la tabla usando PostgreSQL?

Solución:

Aquí hay una consulta en el catálogo del sistema que debería buscar todo lo que necesita (con un campo de clave primaria adicional incluido de forma gratuita).

SELECT DISTINCT
    a.attnum as num,
    a.attname as name,
    format_type(a.atttypid, a.atttypmod) as typ,
    a.attnotnull as notnull, 
    com.description as comment,
    coalesce(i.indisprimary,false) as primary_key,
    def.adsrc as default
FROM pg_attribute a 
JOIN pg_class pgc ON pgc.oid = a.attrelid
LEFT JOIN pg_index i ON 
    (pgc.oid = i.indrelid AND i.indkey[0] = a.attnum)
LEFT JOIN pg_description com on 
    (pgc.oid = com.objoid AND a.attnum = com.objsubid)
LEFT JOIN pg_attrdef def ON 
    (a.attrelid = def.adrelid AND a.attnum = def.adnum)
WHERE a.attnum > 0 AND pgc.oid = a.attrelid
AND pg_table_is_visible(pgc.oid)
AND NOT a.attisdropped
AND pgc.relname="TABLE_NAME"  -- Your table name here
ORDER BY a.attnum;

Lo que devolvería resultados como:

 num |    name     |             typ             | notnull |       comment       | primary_key 
-----+-------------+-----------------------------+---------+---------------------+-------------
   1 | id          | integer                     | t       | a primary key thing | t
   2 | ref         | text                        | f       |                     | f
   3 | created     | timestamp without time zone | t       |                     | f
   4 | modified    | timestamp without time zone | t       |                     | f
   5 | name        | text                        | t       |                     | f

• num: el número de columna
• name: el nombre de la columna
• typ: el tipo de datos
• notnull: es la columna definida como NOT NULL
• comentario: Cualquiera COMMENT definido para la columna
• clave_primaria: es la columna definida como PRIMARY KEY
• predeterminado: el comando utilizado para el valor predeterminado

Basado en la respuesta de @Chris:

SELECT a.attnum
      ,a.attname                            AS name
      ,format_type(a.atttypid, a.atttypmod) AS typ
      ,a.attnotnull                         AS notnull
      ,coalesce(p.indisprimary, FALSE)      AS primary_key
      ,f.adsrc                              AS default_val
      ,d.description                        AS col_comment
FROM   pg_attribute    a 
LEFT   JOIN pg_index   p ON p.indrelid = a.attrelid AND a.attnum = ANY(p.indkey)
LEFT   JOIN pg_description d ON d.objoid  = a.attrelid AND d.objsubid = a.attnum
LEFT   JOIN pg_attrdef f ON f.adrelid = a.attrelid  AND f.adnum = a.attnum
WHERE  a.attnum > 0
AND    NOT a.attisdropped
AND    a.attrelid = 'schema.tbl'::regclass  -- table may be schema-qualified
ORDER  BY a.attnum;

Pero:

Los nombres de las tablas son no es único en una base de datos y, por lo tanto, tampoco en el catálogo del sistema. Puede que tenga que calificar el nombre mediante un esquema.
Usar a.attrelid = 'tbl'::regclass como condición. De esta manera puedes pasar myschema.mytbl como nombre y desambigua. Entonces no es necesario unirse a pg_class en absoluto en este caso.
Además, la visibilidad se comprueba automáticamente para regclass y no hay necesidad de pg_table_is_visible().

A la clave principal puede abarcar varias columnas. Me ocupo de esto uniéndome a pg_index sobre a.attnum = ANY(p.indkey).
indkey es de tipo int2vecor, que es un caso especial de int2[], solo utilizado en los catálogos.

Encuentro psql -E útil para esta clase de problemas.

Compatibilidad

Una consulta especializada como esta puede fallar después de una actualización importante de la versión. Postgres no garantiza que las tablas de catálogo permanezcan estables. Es muy poco probable que los elementos básicos cambien, pero cuanto más compleja y especializada se vuelve su consulta, mayor es la posibilidad. En su lugar, podría utilizar el esquema de información, que está estandarizado, pero también es relativamente lento.

---

---

Utiliza Nuestro Buscador