Describe Table Structure in PostgreSQL

How to Describe Table Structure in PostgreSQL?

In PostgreSQL, there’s no direct equivalent to Oracle’s DESCRIBE TABLE command, which provides detailed information about a table's columns and data types. However, you can achieve similar results using the \d command in psql or by querying the information_schema.columns table. Here’s how to view table structure in PostgreSQL.

1. Using \d Command in psql

The \d command in PostgreSQL’s interactive terminal, psql, displays information about tables, including column names, data types, and other details.

Syntax:

\d table_name

Example Code:

-- Connect to the database in the psql command line
\c database_name
-- Describe the structure of a specific table
\d table_name

Explanation:

• \c database_name: Connect to the target database (replace database_name with the name of your database).
• \d table_name: Displays detailed information about table_name, including column names, data types, and constraints.

2. Using SQL Query on information_schema.columns

Another way to get information about a table’s columns is by querying information_schema.columns, which provides metadata for columns across tables in the database.

Syntax:

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'table_name';

Example Code:

-- Select details of columns in a specific table
SELECT column_name,                -- Column name
       data_type,                  -- Data type of the column
       is_nullable,                -- Whether the column allows NULLs
       column_default              -- Default value for the column
FROM information_schema.columns    -- From the 'columns' schema
WHERE table_name = 'table_name';   -- Specify the table name here

Explanation:

• column_name: Retrieves each column’s name.
• data_type: Shows the data type of each column.
• is_nullable: Indicates whether the column allows NULL values.
• column_default: Displays the default value, if any, for the column.
• information_schema.columns: Stores metadata for columns in all tables.
• WHERE table_name = 'table_name': Filters results to only show information for the specified table.

3. Using pg_catalog.pg_attribute and pg_catalog.pg_class

You can also query PostgreSQL system catalogs to retrieve similar table information. This method involves using pg_catalog.pg_attribute and pg_catalog.pg_class to get column details.

Syntax:

SELECT a.attname AS column_name,
       pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,
       a.attnotnull AS is_nullable
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
WHERE c.relname = 'table_name'
  AND a.attnum > 0
  AND NOT a.attisdropped;

Example Code:

-- Query columns from system catalogs
SELECT a.attname AS column_name,                     -- Column name
       pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type, -- Data type
       a.attnotnull AS is_nullable                   -- NULLability of column
FROM pg_catalog.pg_attribute a                       -- 'pg_attribute' catalog for columns
JOIN pg_catalog.pg_class c ON a.attrelid = c.oid     -- Join 'pg_class' to get table details
WHERE c.relname = 'table_name'                       -- Specify the table name
  AND a.attnum > 0                                   -- Filter to real columns only
  AND NOT a.attisdropped;                            -- Exclude dropped columns

Explanation:

• attname: Column name in the table.
• pg_catalog.format_type(a.atttypid, a.atttypmod): Retrieves data type information.
• attnotnull: Indicates if the column is set to NOT NULL.
• pg_catalog.pg_attribute: Stores information about table columns.
• pg_catalog.pg_class: Contains metadata about tables and other objects.
• The WHERE clause filters to get details only for the specified table’s active columns.