НейроАгент

Как использовать psql DESCRIBE TABLE в PostgreSQL

Узнайте, как выполнить аналог команды Oracle DESCRIBE TABLE в PostgreSQL с помощью команд psql. Изучите \d, \d+ и запросы к information_schema для полного анализа структуры таблицы.

#database-administration#information-schema#oracle-migration#postgresql-commands#postgresql-metadata#psql-describe#table-structure
Вопрос

Как выполнить аналог команды DESCRIBE TABLE Oracle в PostgreSQL с помощью утилиты psql?

НейроАгент

Эквивалент команды Oracle DESCRIBE TABLE в PostgreSQL с использованием psql можно выполнить с помощью команды \d, которая отображает столбцы таблицы, типы данных, индексы, ограничения и внешние ключи. Для получения более подробной информации, включая параметры хранения и статистику, можно использовать команду \d+ или выполнить запрос к таблицам information_schema с помощью SQL-команд, таких как SELECT * FROM information_schema.columns WHERE table_name = 'ваша_таблица'.

Содержание

Базовый эквивалент DESCRIBE с использованием команды \d

Наиболее прямой эквивалент команды Oracle DESCRIBE TABLE в PostgreSQL psql — это команда \d. Эта команда отображает структуру таблицы, включая имена столбцов, типы данных, правила сортировки (collation) и параметры хранения.

Для использования просто введите:

bash
\d имя_таблицы

Например:

bash
\d employees

Это выведет:

  • Имена столбцов с их типами данных и правилами сортировки
  • Индексы таблицы
  • Ограничения CHECK
  • Ограничения внешнего ключа
  • Информацию о первичном ключе

Важно: Команда \d специфична для psql и не будет работать в других клиентах PostgreSQL или в коде приложения.

Подробная информация о таблице с \d+

Для получения более comprehensive информации, аналогичной подробному DESCRIBE в Oracle, используйте команду \d+ (или \dd+). Это включает дополнительные детали, такие как:

  • Параметры хранения
  • Статистику
  • Информацию о сжатии
  • Детали табличного пространства
bash
\d+ имя_таблицы

В выводе будет содержаться вся информация из \d плюс:

  • Хранение: toast_tuple_target, fillfactor и т.д.
  • Статистика: количество строк, дата последнего анализа
  • Информация о последовательности идентификаторов для столбцов с автоинкрементом

SQL-подходы с использованием Information Schema

Для программного использования или интеграции с приложением можно запрашивать стандартные представления information schema. Этот подход более переносим и может использоваться в любом клиенте PostgreSQL.

Получение базовой информации о столбцах

sql
SELECT 
    column_name,
    data_type,
    character_maximum_length,
    is_nullable,
    column_default
FROM information_schema.columns 
WHERE table_name = 'ваша_таблица'
ORDER BY ordinal_position;

Получение полной структуры таблицы

sql
SELECT 
    c.column_name,
    c.data_type,
    c.character_maximum_length,
    c.numeric_precision,
    c.numeric_scale,
    c.is_nullable,
    c.column_default,
    c.ordinal_position,
    CASE WHEN pk.column_name IS NOT NULL THEN 'YES' ELSE 'NO' END as is_pk,
    CASE WHEN fk.column_name IS NOT NULL THEN 'YES' ELSE 'NO' END as is_fk,
    fk.foreign_table_name,
    fk.foreign_column_name
FROM information_schema.columns c
LEFT JOIN (
    SELECT ku.column_name
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage ku
        ON tc.constraint_name = ku.constraint_name
        AND tc.table_schema = ku.table_schema
    WHERE tc.constraint_type = 'PRIMARY KEY'
        AND tc.table_name = 'ваша_таблица'
) pk ON c.column_name = pk.column_name
LEFT JOIN (
    SELECT 
        kcu.column_name,
        ccu.table_name AS foreign_table_name,
        ccu.column_name AS foreign_column_name
    FROM information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
        ON tc.constraint_name = kcu.constraint_name
        AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage AS ccu
        ON ccu.constraint_name = tc.constraint_name
        AND ccu.table_schema = tc.table_schema
    WHERE tc.constraint_type = 'FOREIGN KEY'
        AND tc.table_name = 'ваша_таблица'
) fk ON c.column_name = fk.column_name
WHERE c.table_name = 'ваша_таблица'
ORDER BY c.ordinal_position;

Альтернативные методы с использованием системных каталогов

Для получения специфичной для PostgreSQL информации можно напрямую запрашивать системные каталоги. Они предоставляют более подробную метаданные, специфичные для PostgreSQL.

Получение информации о столбцах из pg_catalog

sql
SELECT 
    a.attname as column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) as data_type,
    CASE WHEN a.attnotnull THEN 'NO' ELSE 'YES' END as is_nullable,
    pg_catalog.pg_get_expr(d.adbin, d.adrelid) as default_value
FROM pg_catalog.pg_attribute a
LEFT JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum)
WHERE a.attrelid = 'ваша_таблица'::regclass
    AND a.attnum > 0
    AND NOT a.attisdropped
ORDER BY a.attnum;

Получение полной информации о таблице

sql
-- Получение деталей таблицы и столбцов
SELECT 
    t.tablename,
    c.column_name,
    c.data_type,
    c.column_default,
    c.is_nullable,
    CASE WHEN pk.column_name IS NOT NULL THEN 'YES' ELSE 'NO' END as is_primary_key,
    CASE WHEN fk.column_name IS NOT NULL THEN 'YES' ELSE 'NO' END as is_foreign_key,
    fk.foreign_table_name,
    fk.foreign_column_name,
    i.relname as index_name,
    CASE WHEN i.indisprimary THEN 'PRIMARY' 
         WHEN i.indisunique THEN 'UNIQUE' 
         ELSE 'INDEX' END as index_type
FROM pg_tables t
JOIN information_schema.columns c ON t.tablename = c.table_name
LEFT JOIN pg_index i ON i.indrelid = t.schemaname||'.'||t.tablename::regclass
LEFT JOIN (
    SELECT ku.column_name, ku.table_name
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage ku
        ON tc.constraint_name = ku.constraint_name
        AND tc.table_schema = ku.table_schema
    WHERE tc.constraint_type = 'PRIMARY KEY'
) pk ON c.table_name = pk.table_name AND c.column_name = pk.column_name
LEFT JOIN (
    SELECT 
        kcu.column_name,
        tc.table_name,
        ccu.table_name AS foreign_table_name,
        ccu.column_name AS foreign_column_name
    FROM information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
        ON tc.constraint_name = kcu.constraint_name
        AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage AS ccu
        ON ccu.constraint_name = tc.constraint_name
        AND ccu.table_schema = tc.table_schema
    WHERE tc.constraint_type = 'FOREIGN KEY'
) fk ON c.table_name = fk.table_name AND c.column_name = fk.column_name
WHERE t.tablename = 'ваша_таблица'
ORDER BY c.ordinal_position, i.relname;

Полная структура таблицы с деталями столбцов

Для получения comprehensive представления, closely соответствующего Oracle DESCRIBE, можно создать более подробный запрос:

sql
SELECT 
    c.column_name,
    c.data_type,
    CASE 
        WHEN c.character_maximum_length IS NOT NULL 
        THEN c.data_type || '(' || c.character_maximum_length || ')'
        WHEN c.numeric_precision IS NOT NULL AND c.numeric_scale IS NOT NULL
        THEN c.data_type || '(' || c.numeric_precision || ',' || c.numeric_scale || ')'
        WHEN c.numeric_precision IS NOT NULL
        THEN c.data_type || '(' || c.numeric_precision || ')'
        ELSE c.data_type
    END as full_data_type,
    c.is_nullable,
    c.column_default,
    CASE WHEN pk.column_name IS NOT NULL THEN 'YES' ELSE 'NO' END as is_primary_key,
    CASE WHEN fk.column_name IS NOT NULL THEN 'YES' ELSE 'NO' END as is_foreign_key,
    fk.foreign_table_name,
    fk.foreign_column_name,
    CASE WHEN c.character_set_name IS NOT NULL THEN c.character_set_name ELSE 'N/A' END as character_set,
    CASE WHEN c.collation_name IS NOT NULL THEN c.collation_name ELSE 'N/A' END as collation
FROM information_schema.columns c
LEFT JOIN (
    SELECT ku.column_name
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage ku
        ON tc.constraint_name = ku.constraint_name
        AND tc.table_schema = ku.table_schema
    WHERE tc.constraint_type = 'PRIMARY KEY'
        AND tc.table_name = 'ваша_таблица'
) pk ON c.column_name = pk.column_name
LEFT JOIN (
    SELECT 
        kcu.column_name,
        ccu.table_name AS foreign_table_name,
        ccu.column_name AS foreign_column_name
    FROM information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
        ON tc.constraint_name = kcu.constraint_name
        AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage AS ccu
        ON ccu.constraint_name = tc.constraint_name
        AND ccu.table_schema = tc.table_schema
    WHERE tc.constraint_type = 'FOREIGN KEY'
        AND tc.table_name = 'ваша_таблица'
) fk ON c.column_name = fk.column_name
WHERE c.table_name = 'ваша_таблица'
ORDER BY c.ordinal_position;

Практические примеры и варианты использования

Пример 1: Базовый эквивалент DESCRIBE

bash
# Подключение к базе данных PostgreSQL
psql -h localhost -U username -d database_name

# Описание таблицы
\d employees

Пример 2: Получение подробной информации

bash
# Получение подробной информации о таблице, включая параметры хранения
\d+ employees

Пример 3: Программный запрос для использования в приложении

sql
-- Создание повторно используемого представления для описания таблиц
CREATE OR REPLACE VIEW table_description AS
SELECT 
    tc.table_schema,
    tc.table_name,
    kcu.column_name,
    c.data_type,
    c.character_maximum_length,
    c.numeric_precision,
    c.numeric_scale,
    c.is_nullable,
    c.column_default,
    CASE WHEN pk.column_name IS NOT NULL THEN 'YES' ELSE 'NO' END as is_primary_key,
    CASE WHEN fk.column_name IS NOT NULL THEN 'YES' ELSE 'NO' END as is_foreign_key,
    fk.foreign_table_name,
    fk.foreign_column_name
FROM information_schema.tables tc
JOIN information_schema.columns c ON tc.table_name = c.table_name AND tc.table_schema = c.table_schema
LEFT JOIN (
    SELECT ku.table_schema, ku.table_name, ku.column_name
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage ku
        ON tc.constraint_name = ku.constraint_name
        AND tc.table_schema = ku.table_schema
    WHERE tc.constraint_type = 'PRIMARY KEY'
) pk ON c.table_schema = pk.table_schema AND c.table_name = pk.table_name AND c.column_name = pk.column_name
LEFT JOIN (
    SELECT 
        kcu.table_schema,
        kcu.table_name,
        kcu.column_name,
        ccu.table_name AS foreign_table_name,
        ccu.column_name AS foreign_column_name
    FROM information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
        ON tc.constraint_name = kcu.constraint_name
        AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage AS ccu
        ON ccu.constraint_name = tc.constraint_name
        AND ccu.table_schema = tc.table_schema
    WHERE tc.constraint_type = 'FOREIGN KEY'
) fk ON c.table_schema = fk.table_schema AND c.table_name = fk.table_name AND c.column_name = fk.column_name
WHERE tc.table_type = 'BASE TABLE'
ORDER BY tc.table_schema, tc.table_name, c.ordinal_position;

-- Запрос для конкретной таблицы
SELECT * FROM table_description 
WHERE table_name = 'employees';

Пример 4: Shell-скрипт для автоматического описания таблицы

bash
#!/bin/bash

# Использование: describe_table.sh <база_данных> <таблица>
DB=$1
TABLE=$2

echo "Таблица: $TABLE"
echo "Столбцы:"
psql -d "$DB" -c "\d $TABLE" | grep -E "Column|Type|Collation|Storage|Stats|Description" || echo "Столбцы не найдены"

echo -e "\nПодробная информация:"
psql -d "$DB" -c "\d+ $TABLE" | head -20

echo -e "\nSQL-запрос для структуры:"
psql -d "$DB" -c "SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name = '$TABLE' ORDER BY ordinal_position;"

Пример 5: Скрипт для кросс-совместимости баз данных

sql
-- Функция для получения описания таблицы, совместимая с Oracle DESCRIBE
CREATE OR REPLACE FUNCTION describe_table(p_table_name TEXT)
RETURNS TABLE(
    column_name TEXT,
    data_type TEXT,
    nullable VARCHAR(3),
    default_value TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        column_name,
        data_type,
        CASE WHEN is_nullable = 'YES' THEN 'NULL' ELSE 'NOT NULL' END,
        column_default
    FROM information_schema.columns 
    WHERE table_name = p_table_name
    ORDER BY ordinal_position;
END;
$$ LANGUAGE plpgsql;

-- Использование
SELECT * FROM describe_table('employees');

Источники

  1. Документация psql PostgreSQL - Мета-команды
  2. Документация Information Schema PostgreSQL
  3. Документация системных каталогов PostgreSQL
  4. Запросы для получения информации о столбцах PostgreSQL
  5. Справочник по командам psql

Заключение

Для выполнения эквивалента команды Oracle DESCRIBE TABLE в PostgreSQL с использованием psql у вас есть несколько эффективных вариантов:

  • Используйте \d имя_таблицы для получения базовой информации о структуре таблицы
  • Используйте \d+ имя_таблицы для получения подробной информации, включая параметры хранения и статистику
  • Запрашивайте information_schema.tables и information_schema.columns для SQL-подходов
  • Используйте системные каталоги, такие как pg_attribute, для получения специфичной для PostgreSQL метаданных

Выбор зависит от ваших потребностей: быстрое интерактивное исследование с помощью команд psql или программный доступ через SQL-запросы. Для пользователей Oracle, переходящих на PostgreSQL, команда \d предоставляет наиболее знакомый опыт, в то время как запросы к information_schema предлагают наиболее переносимое решение для интеграции с приложением.

Помните, что команда SHOW в PostgreSQL также может быть полезна для получения конкретных деталей конфигурации, и вы можете создавать пользовательские функции или представления для точной настройки вывода в предпочитаемом формате из Oracle DESCRIBE.

Как получить подробную информацию о столбцах, включая ограничения и индексы в PostgreSQL?В чем разница между командами \d и \d+ в PostgreSQL psql?Как экспортировать структуру таблицы в SQL DDL в PostgreSQL?Как сравнить структуры таблиц между двумя базами данных PostgreSQL?Как автоматизировать задачи описания таблиц в PostgreSQL с помощью shell-скриптов?Как создать пользовательскую функцию DESCRIBE в PostgreSQL для пользователей Oracle?
Спросить у NeuroAgent