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

sql

database-management

postgresql

sql-queries

byAnton Shumikhin·Nov 8, 2024

Get the column names and data types from a PostgreSQL table in a jiffy:

SELECT column_name, udt_name 
FROM information_schema.columns 
WHERE table_name = 'your_table';

Replace 'your_table' with your actual table name. The udt_name field gives a less ambiguous data type representation than data_type.

Retrieving more column details

To unravel extra details about your columns, such as the exact data type or their order:

SELECT
    column_name, 
    data_type, 
    ordinal_position
FROM information_schema.columns
WHERE table_name = 'your_table' 
    AND table_schema = 'your_schema' -- (🕴️secret agent mode on! For targeting right table in schema-genre databases!)
ORDER BY ordinal_position;

psql Meta-Command for quick column details

For psql terminal lovers, use this one-liner:

\d+ your_table

This will reveal column details, including data types, in a neat table straight inside your terminal.

Delving into catalog tables for column details

For the curious geeks wishing to delve directly into PostgreSQL's system catalogs:

SELECT 
    attname AS column_name, 
    format_type(atttypid, atttypmod) AS data_type
FROM 
    pg_attribute
JOIN 
    pg_class ON pg_class.oid = pg_attribute.attrelid
JOIN 
    pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE 
    pg_class.relname = 'your_table'
    AND pg_namespace.nspname = 'your_schema' -- (👨‍🔬 Dr. Schema ensuring we're targeting the correct "lab" 😉)
    AND attnum > 0 -- No room for poor columns in the list
    AND NOT attisdropped; -- 💔 No leftover of dropped columns. Clean slate!