How to get the names of all Postgresql database tables?

Asked

Viewed 12,845 times

4

How to get the names of all Postgresql database tables?

How to get the attributes(code, name, etc...) of a given table in the Postgresql database?

  • You are using psql or your intention is to use this information in a single query?

  • @Paths One query only

  • Okay... then Igor’s answer should solve your problem :)

2 answers

3


To get the name of all tables, use the command below:

SELECT table_name 
FROM information_schema.tables
WHERE table_schema='public'
AND table_type='BASE TABLE';

To get the attributes of a table, use the command below:

SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = '<NOME DA TABELA>';

1

One of the alternatives is to use the postgres catalogues. Catalog tables have a format more suitable to postgres as specific types of SGDB and possibility of using some functions.

select
  relnamespace::regnamespace as schema,
  relname as tabela,
  attname as coluna,
  format_type(atttypid,atttypmod) as tipo
from pg_class c
  inner join pg_attribute a
    on (c.oid=a.attrelid)
where attnum>0
  and relnamespace='public'::regnamespace --usar se necessário filtrar um schema
  and relname in ('clientes','tributacao'); --usar se necessário filtrar por tabela

https://www.postgresql.org/docs/current/static/catalog-pg-class.html https://www.postgresql.org/docs/current/static/catalog-pg-attribute.html https://www.postgresql.org/docs/current/static/functions-info.html

Browser other questions tagged

You are not signed in. Login or sign up in order to post.