PostgreSQL "DESCRIBE TABLE"

Mastering PostgreSQL: Describing Table Structures

Learn how to effectively inspect and understand the schema of your PostgreSQL tables using various commands and tools.

When working with PostgreSQL, understanding the structure of your tables is fundamental for database management, development, and debugging. Unlike some other database systems that might have a single, intuitive DESCRIBE TABLE command, PostgreSQL offers several powerful methods to achieve the same goal, each with its own advantages. This article will guide you through the most common and effective ways to describe a table in PostgreSQL, from the psql command-line interface to SQL queries against the information schema.

Using the psql Meta-Commands

The psql command-line client is an indispensable tool for PostgreSQL users. It provides a rich set of meta-commands (commands starting with a backslash \) that simplify database interaction. For describing tables, psql offers two primary commands: \d and \d+.

\d table_name;

Basic \d command in psql

The \d table_name command provides a concise summary of a table's structure, including column names, their data types, and any default values or NOT NULL constraints. It's quick and perfect for a general overview.

\d+ table_name;

Detailed \d+ command in psql

For a more comprehensive view, \d+ table_name extends the output to include additional details such as storage parameters, column descriptions, and table size. This is particularly useful when you need to delve deeper into the physical characteristics and metadata of a table.

Querying the Information Schema

For programmatic access or when you need to retrieve table metadata within a SQL query, PostgreSQL's Information Schema is the go-to resource. The Information Schema is a set of SQL-standard views that provide information about the objects defined in the current database. It's a powerful way to query schema details directly.

SELECT
    column_name,
    data_type,
    character_maximum_length,
    is_nullable,
    column_default
FROM
    information_schema.columns
WHERE
    table_schema = 'public' AND table_name = 'your_table_name'
ORDER BY
    ordinal_position;

Querying column details from information_schema.columns

This SQL query directly accesses the information_schema.columns view to retrieve detailed information about each column in a specified table. You can customize the SELECT clause to fetch exactly the information you need, such as udt_name for the underlying data type or numeric_precision for numeric columns.

erDiagram
    "information_schema.columns" {
        varchar table_catalog
        varchar table_schema
        varchar table_name
        varchar column_name
        integer ordinal_position
        varchar column_default
        varchar is_nullable
        varchar data_type
        integer character_maximum_length
        integer numeric_precision
        integer numeric_scale
        varchar udt_name
    }
    "your_table" ||--o{ "information_schema.columns" : "has columns"
    "information_schema.columns" ||--o{ "pg_catalog.pg_type" : "references data type"
    "information_schema.columns" ||--o{ "pg_catalog.pg_class" : "references table"

Simplified ER Diagram of Information Schema relationships for columns

Inspecting System Catalogs (pg_catalog)

For the most granular and PostgreSQL-specific details, you can query the system catalogs directly. These are the underlying tables that the Information Schema views are built upon. While more complex, they offer unparalleled access to database metadata.

SELECT
    a.attname AS column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,
    a.attnotnull AS not_null,
    (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) AS default_value
FROM
    pg_catalog.pg_attribute a
WHERE
    a.attrelid = 'your_table_name'::regclass
    AND a.attnum > 0
    AND NOT a.attisdropped
ORDER BY
    a.attnum;

Querying column details from pg_catalog.pg_attribute

This query directly accesses pg_attribute, which stores information about table columns. It uses pg_catalog.format_type to get human-readable data types and a subquery to fetch default values from pg_attrdef. This method is often used by tools and advanced users who need precise control over metadata retrieval.