Obter nomes de colunas e tipos de dados de uma consulta, tabela ou exibição

information_schemavs. catálogos do sistema

Já discutimos isso muitas vezes. O esquema de informação serve a certos propósitos. Os catálogos do sistema são a fonte real de todas as informações.

O esquema de informações fornece visualizações padronizadas que ajudam na portabilidade - principalmente nas principais versões do Postgres, pois a portabilidade em diferentes plataformas RDBMS normalmente é uma ilusão, uma vez que suas consultas são sofisticadas o suficiente para pesquisar catálogos de sistemas. Notavelmente, o Oracle ainda não oferece suporte ao esquema de informações.

As visualizações no esquema de informações devem passar por muitos obstáculos para obter um formato compatível com o padrão. Isso os torna lentos, às vezes muito lentos. Compare planos e desempenho para estes objetos básicos:

EXPLAIN ANALYZE SELECT * from information_schema.columns;
EXPLAIN ANALYZE SELECT * from pg_catalog.pg_attribute;

A diferença é notável.

Seu exemplo

Para o seu exemplo SELECT * from tbl, compare as duas consultas abaixo para esta tabela simples:

CREATE TEMP TABLE foo(
   a numeric(12,3)
 , b timestamp(0)
);

Usando pg_attribute:

SELECT attname, format_type(atttypid, atttypmod) AS type
FROM   pg_attribute
WHERE  attrelid = 'foo'::regclass
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum;

format_type()retorna o tipo completo com todos os modificadores:

attname | type
--------+-------------------------------
a       | numeric(12,3)
b       | timestamp(0) without time zone

Observe também que a conversão para regclassresolve o nome da tabela de acordo com o arquivo search_path. Ele gera uma exceção se o nome não for válido. Ver:

• ERRO: não foi possível encontrar o tipo de matriz para o tipo de dados information_schema.sql_identifier

Usando information_schema.columns:

SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'foo'
ORDER  BY ordinal_position;

As informações são padronizadas, mas incompletas :

column_name | data_type
------------+----------------------------
a           | numeric
b           | timestamp without time zone

Para obter informações completas sobre o tipo de dados, você precisa considerar todas essas colunas adicionalmente:

character_maximum_length
character_octet_length
numeric_precision
numeric_precision_radix
numeric_scale
datetime_precision
interval_type
interval_precision

Respostas relacionadas:

• Como verificar se uma tabela existe em um determinado esquema
• Listar todas as colunas de uma tabela especificada

Lista de prós e contras

Os maiores profissionais (IMO) em negrito.

Visualizações do esquema de informações

• muitas vezes mais simples (depende)
• lento
• pré-processados, que podem ou não atender às suas necessidades
• seletivo (os usuários veem apenas os objetos para os quais têm privilégios)
• em conformidade com um padrão SQL (que é implementado por alguns dos principais RDBMS)
• principalmente portátil nas principais versões do Postgres
• não requer muito conhecimento específico sobre Postgres
• os identificadores são descritivos, longos e às vezes estranhos

Catálogos do sistema

• muitas vezes mais complexo (depende), mais próximo da fonte
• velozes
• completo (colunas do sistema como oidincluídas)
• não está em conformidade com um padrão SQL
• menos portátil nas principais versões do Postgres (mas o básico não vai mudar)
• requerem conhecimentos mais específicos sobre o Postgres
• identificadores são concisos, menos descritivos, mas convenientemente curtos

Consulta arbitrária

Para obter a mesma lista de nomes e tipos de coluna de uma consulta, você pode usar um truque simples: CREATEuma tabela temporária da saída da consulta e, em seguida, use as mesmas técnicas acima.

Você pode anexar LIMIT 0, pois não precisa de dados reais:

CREATE TEMP TABLE tmp123 AS
SELECT 1::numeric, now()
LIMIT  0;

Para obter o tipo de dados de colunas individuais, você também pode usar a função pg_typeof():

SELECT pg_typeof(1);

Você pode usar o psqlcliente de linha de comando.

\dtirá mostrar uma lista de tabelas

\dvirá mostrar uma lista de visualizações

\d [object_name]irá descrever o esquema da tabela ou visão

Não tenho certeza de como você descreveria uma consulta.

Mais informações: https://manikandanmv.wordpress.com/tag/basic-psql-commands/

Se você tem acesso ao pg_catalog e usa o PgAdmin3, eu recomendo uma solução que encontrei no blog Valentine's Tech ( http://tech.valgog.com/2011/02/pgadmin-iii-macros-get-table-fields. html ). É uma macro PgAdmin3 que pode ser acessada com um atalho para exibir a definição de um nome de tabela selecionado.

select quote_ident(nspname) || '.' || quote_ident(relname) as table_name, 
       quote_ident(attname) as field_name, 
       format_type(atttypid,atttypmod) as field_type, 
       case when attnotnull then ' NOT NULL' else '' end as null_constraint,
       case when atthasdef then 'DEFAULT ' || 
                                ( select pg_get_expr(adbin, attrelid) 
                                    from pg_attrdef 
                                   where adrelid = attrelid and adnum = attnum )::text else ''
       end as dafault_value,
       case when nullif(confrelid, 0) is not null
            then confrelid::regclass::text || '( ' || 
                 array_to_string( ARRAY( select quote_ident( fa.attname ) 
                                           from pg_attribute as fa 
                                          where fa.attnum = ANY ( confkey ) 
                                            and fa.attrelid = confrelid
                                          order by fa.attnum 
                                        ), ','
                                 ) || ' )'
            else '' end as references_to
  from pg_attribute 
       left outer join pg_constraint on conrelid = attrelid 
                                    and attnum = conkey[1] 
                                    and array_upper( conkey, 1 ) = 1,
       pg_class, 
       pg_namespace
 where pg_class.oid = attrelid
   and pg_namespace.oid = relnamespace
   and pg_class.oid = btrim( '$SELECTION$' )::regclass::oid
   and attnum > 0
   and not attisdropped
 order by attrelid, attnum;

Funciona como um encanto e extremamente útil.

Use as information_schemavisualizações , elas são padrão SQL e contêm as informações que você deseja.

Você também pode acessar diretamente pg_class, pg_attribute, etc, mas isso é não-portável e muitas vezes mais complicado; você pode precisar de funções auxiliares comooidvectortypes , pg_get_function_arguments, etc para algumas coisas.

Se você quiser ver como psqlexecuta algo como \dt, execute psql -E- ele imprimirá a consulta. No entanto, geralmente é melhor usar o information_schemase ele atender às suas necessidades.

Isso pode ser muito simples, mas pgAdmin4 mostra os tipos de campo nos resultados de saída. As outras soluções acima são provavelmente mais elegantes, mas quando preciso de uma resposta rápida, acho que a GUI de consulta do pgAdmin4 funciona muito bem. Tentar descobrir o tipo de campo de um campo calculado retornado por uma exibição ou função pode ser complicado.