Как посмотреть таблицы и столбцы в PostgreSQL

Коротко

• В psql: \l — базы, \dn — схемы, \dt — таблицы, \d имя — столбцы и индексы таблицы.
• В любом клиенте: information_schema.tables — таблицы, information_schema.columns — столбцы и типы.
• Найти, в каких таблицах есть столбец: фильтр column_name like '%user%' по information_schema.columns.
• Точное число строк — count(*); быстрая оценка без чтения таблицы — n_live_tup из pg_stat_user_tables.
• Размер таблицы с индексами — pg_size_pretty(pg_total_relation_size('имя')).

Шпаргалка: psql и SQL

Команды psql короче, но работают только в psql. Запросы к служебным представлениям длиннее, зато их можно выполнить где угодно и дальше фильтровать, сортировать и соединять как обычные таблицы.

Список таблиц

Представление information_schema.tables описывает все таблицы и представления, к которым у вас есть доступ. Условие table_type = 'BASE TABLE' оставляет только таблицы, а исключение служебных схем убирает внутренние таблицы самого PostgreSQL — их сотни, и в списке они только мешают.

Если нужен список с именами схем, добавьте столбец table_schema. В PostgreSQL таблицы пользователя обычно лежат в схеме public, в песочнице на DuckDB — в схеме main.

select table_name
from information_schema.tables
where table_type = 'BASE TABLE'
  and table_schema not in ('pg_catalog', 'information_schema')
order by table_name;

Столбцы таблицы и их типы

information_schema.columns — одна строка на каждый столбец каждой таблицы. Порядок столбцов в таблице хранится в ordinal_position, по нему и сортируют. Типы движки называют по-разному: PostgreSQL пишет integer и numeric, DuckDB — INTEGER и DECIMAL(10,2), но смысл один.

Запрос ниже считает, сколько столбцов в каждой таблице. Это быстрый способ найти широкие таблицы, с которых стоит начать изучение, и таблицы-справочники из двух-трёх столбцов.

select table_name, count(*) as columns
from information_schema.columns
where table_schema not in ('pg_catalog', 'information_schema')
group by table_name
order by table_name;

Найти столбец по имени

Самый полезный запрос первой недели: в каких таблицах есть идентификатор пользователя, сумма заказа или дата. Фильтр по column_name с like находит все таблицы, где встречается нужное слово. Так быстро собирается карта связей: таблицы с user_id соединяются с users.

select table_name, column_name
from information_schema.columns
where column_name like '%user%'
  and table_schema not in ('pg_catalog', 'information_schema')
order by table_name, column_name;

Сколько строк в таблицах

Точный ответ даёт только count(*): он читает таблицу целиком. На учебной базе это мгновенно, на таблице событий в миллиарды строк — минуты нагрузки на рабочий сервер. Для пяти небольших таблиц удобно собрать счётчики в один результат через UNION ALL.

select 'users' as table_name, count(*) as row_count from users
union all select 'events', count(*) from events
union all select 'payments', count(*) from payments
union all select 'subscriptions', count(*) from subscriptions
union all select 'experiment_exposures', count(*) from experiment_exposures
order by row_count desc;

Быстрая оценка числа строк и размер таблиц

Для больших таблиц PostgreSQL хранит оценку числа строк в статистике: n_live_tup в представлении pg_stat_user_tables. Она обновляется при сборе статистики и обычно близка к точному числу, а запрос к ней мгновенный. На копии учебной базы после analyze оценка совпала с count(*) до строки.

В том же запросе удобно смотреть размер: pg_total_relation_size считает таблицу вместе с индексами и служебными данными, а pg_size_pretty переводит байты в килобайты и гигабайты.

select
  relname                                        as table_name,
  n_live_tup                                     as rows_estimate,
  pg_size_pretty(pg_total_relation_size(relid))  as total_size
from pg_stat_user_tables
order by pg_total_relation_size(relid) desc;

• Размер всей базы: select pg_size_pretty(pg_database_size(current_database())); — у копии учебной базы 11 MB.
• Только данные без индексов: pg_relation_size('events').
• Только индексы: pg_indexes_size('events').

Схемы и права

В рабочей базе таблицы часто разложены по схемам: raw — сырые выгрузки, staging — очищенные, mart — витрины для отчётов. Список схем — \dn в psql или information_schema.schemata. Обращаться к таблице не из public можно по полному имени схема.таблица или добавив схему в путь поиска: set search_path to mart, public;.

Если таблица видна в списке, а запрос к ней падает с «permission denied», у вашего пользователя нет права на чтение. information_schema показывает только объекты, к которым у вас есть хоть какой-то доступ, а pg_catalog — все. Поэтому таблица может найтись в pg_tables и не найтись в information_schema.tables.

Связи между таблицами: внешние ключи

Если в базе объявлены внешние ключи, по ним можно восстановить, какая таблица на какую ссылается. Запрос собирает три служебных представления: ограничения, столбцы ограничений и столбцы, на которые они ссылаются. Результат — готовый список соединений: из какой таблицы и столбца в какую таблицу и столбец.

В учебной копии внешних ключей нет, поэтому запрос проверен на временном ключе payments.user_id → users.user_id внутри транзакции с откатом. В аналитических хранилищах ключи часто не объявляют вовсе: данные приходят из разных систем, и проверять их при каждой загрузке дорого. Тогда связи ищут по именам столбцов, как в разделе выше, и проверяют пересечением значений.

select
  tc.table_name    as from_table,
  kcu.column_name  as from_column,
  ccu.table_name   as to_table,
  ccu.column_name  as to_column
from information_schema.table_constraints tc
join information_schema.key_column_usage kcu
  on kcu.constraint_name = tc.constraint_name
 and kcu.table_schema    = tc.table_schema
join information_schema.constraint_column_usage ccu
  on ccu.constraint_name = tc.constraint_name
 and ccu.table_schema    = tc.table_schema
where tc.constraint_type = 'FOREIGN KEY'
order by from_table, from_column;

Порядок осмотра незнакомой базы

Когда доступ только выдан, удобно пройти базу в одном и том же порядке. Это занимает час, а экономит неделю вопросов «а где у нас лежат возвраты».

• Схемы: какие есть и что значат их имена (raw, staging, mart).
• Таблицы каждой схемы с размером и оценкой числа строк: самые большие обычно и есть журналы событий и заказов.
• Столбцы крупных таблиц: где идентификаторы, где даты, где суммы, в каком они типе.
• Связи: внешние ключи, а если их нет — общие имена столбцов вроде user_id и order_id.
• Диапазон дат в главных таблицах: min и max по столбцу времени показывают, с какого числа есть данные и насколько они свежие.
• Заметки: запишите, что узнали, в комментарии к таблицам или в общий документ команды.

Описание таблиц и столбцов

Хорошие команды подписывают таблицы и столбцы комментариями: что значит статус 3 и в каком поясе время. В psql комментарии видны в \dt+ и \d+ имя, а запросом — через функцию col_description. Если комментариев нет, ваши заметки по первой неделе — хороший повод их добавить командой comment on column.

select
  a.attname                              as column_name,
  format_type(a.atttypid, a.atttypmod)   as data_type,
  col_description(a.attrelid, a.attnum)  as description
from pg_attribute a
where a.attrelid = 'payments'::regclass
  and a.attnum > 0
  and not a.attisdropped
order by a.attnum;

Частые вопросы

Запросы к information_schema из статьи работают в песочнице: попробуйте найти все столбцы с датами в учебной базе.