How to get a list column names and datatypes of a table in PostgreSQL?

How to List Column Names and Data Types in PostgreSQL

Learn various SQL queries and commands to retrieve detailed schema information, including column names and their corresponding data types, for any table in a PostgreSQL database.

Understanding the structure of your database tables is fundamental for effective data management and application development. In PostgreSQL, there are several powerful ways to inspect table schemas, allowing you to retrieve column names, their data types, and other crucial metadata. This article will guide you through the most common and efficient methods, from simple SQL queries to using PostgreSQL's built-in catalog views.

Using \d Command in psql

For quick interactive inspection within the psql command-line client, the \d command (describe) is incredibly useful. It provides a concise summary of a table's structure, including column names, types, and modifiers like NOT NULL.

\d your_table_name;

Using the \d command in psql to describe a table.

Querying the information_schema

The information_schema is a standard SQL feature that provides a set of views containing metadata about the objects in your database. It's a portable way to query schema information across different SQL-compliant databases, though its implementation details might vary slightly. To get column names and data types, you'll typically query the information_schema.columns view.

SELECT
    column_name,
    data_type,
    character_maximum_length
FROM
    information_schema.columns
WHERE
    table_schema = 'public' AND table_name = 'your_table_name';

Retrieving column details using information_schema.columns.

Querying PostgreSQL System Catalogs

PostgreSQL maintains its own set of system catalog tables that store all database metadata. These catalogs offer the most comprehensive and detailed information about your database objects. While information_schema is more portable, directly querying system catalogs like pg_catalog.pg_attribute and pg_catalog.pg_class can sometimes provide more specific PostgreSQL-centric details or better performance for very large schemas. This method requires joining several catalog tables.

SELECT
    a.attname AS column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type
FROM
    pg_catalog.pg_attribute a
JOIN
    pg_catalog.pg_class c ON a.attrelid = c.oid
JOIN
    pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE
    n.nspname = 'public' AND c.relname = 'your_table_name' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY
    a.attnum;

Using PostgreSQL system catalogs to get column names and data types.

erDiagram
    "pg_catalog.pg_class" ||--o{ "pg_catalog.pg_attribute" : "has attributes"
    "pg_catalog.pg_namespace" ||--o{ "pg_catalog.pg_class" : "contains tables"
    "pg_catalog.pg_class" { 
        oid relid PK
        text relname
        oid relnamespace FK
    }
    "pg_catalog.pg_attribute" { 
        oid attrelid FK
        text attname
        oid atttypid
        int atttypmod
        int attnum
        boolean attisdropped
    }
    "pg_catalog.pg_namespace" { 
        oid oid PK
        text nspname
    }

Simplified ER Diagram of PostgreSQL System Catalogs for Schema Information

Choosing the Right Method

The best method depends on your specific needs:

• \d command: Ideal for quick, interactive checks within psql.
• information_schema.columns: Recommended for programmatic access and when portability across SQL databases is a concern. It's generally easier to read and understand.
• pg_catalog views: Use when you need the most detailed, PostgreSQL-specific metadata, or when information_schema doesn't provide sufficient detail for a particular use case. It offers the most granular control but comes with increased complexity and reduced portability.