NeuroAgent

How to Use psql DESCRIBE TABLE in PostgreSQL

Learn how to perform Oracle's DESCRIBE TABLE equivalent in PostgreSQL using psql commands. Explore \d, \d+, and information_schema queries for complete table structure analysis.

#database-administration#information-schema#oracle-migration#postgresql-commands#postgresql-metadata#psql-describe#table-structure
Question

How do you perform the equivalent of Oracle’s DESCRIBE TABLE in PostgreSQL with psql command?

NeuroAgent

The equivalent of Oracle’s DESCRIBE TABLE in PostgreSQL with psql can be achieved using the \d command, which displays table columns, types, indexes, constraints, and foreign keys. For more detailed information including storage parameters and statistics, you can use \d+ or query the information_schema tables with SQL commands like SELECT * FROM information_schema.columns WHERE table_name = 'your_table'.

Contents

Basic DESCRIBE Equivalent Using \d Command

The most direct equivalent to Oracle’s DESCRIBE TABLE in PostgreSQL psql is the \d command. This command displays the table structure including column names, data types, collations, and storage parameters.

To use it, simply type:

bash
\d table_name

For example:

bash
\d employees

This will output:

  • Column names with their data types and collations
  • Indexes on the table
  • Check constraints
  • Foreign key constraints
  • Primary key information

Important: The \d command is specific to psql and won’t work in other PostgreSQL clients or application code.

Detailed Table Information with \d+

For more comprehensive information similar to Oracle’s detailed DESCRIBE, use the \d+ (or \dd+) command. This includes additional details like:

  • Storage parameters
  • Statistics
  • Compression information
  • Tablespace details
bash
\d+ table_name

The output will include all the information from \d plus:

  • Storage: toast_tuple_target, fillfactor, etc.
  • Statistics: Number of rows, last analyzed date
  • Identity sequence information for auto-incrementing columns

SQL-Based Approaches Using Information Schema

For programmatic use or application integration, you can query the standard information schema views. This approach is more portable and can be used in any PostgreSQL client.

Get Basic Column Information

sql
SELECT 
    column_name,
    data_type,
    character_maximum_length,
    is_nullable,
    column_default
FROM information_schema.columns 
WHERE table_name = 'your_table'
ORDER BY ordinal_position;

Get Complete Table Structure

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 = 'your_table'
) 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 = 'your_table'
) fk ON c.column_name = fk.column_name
WHERE c.table_name = 'your_table'
ORDER BY c.ordinal_position;

Alternative Methods Using System Catalogs

For PostgreSQL-specific information, you can query the system catalogs directly. These provide more detailed PostgreSQL-specific metadata.

Get Column Information from 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 = 'your_table'::regclass
    AND a.attnum > 0
    AND NOT a.attisdropped
ORDER BY a.attnum;

Get Complete Table Information

sql
-- Get table and column details
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 = 'your_table'
ORDER BY c.ordinal_position, i.relname;

Complete Table Structure with Column Details

For a comprehensive view that closely matches Oracle’s DESCRIBE, you can create a more detailed query:

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 = 'your_table'
) 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 = 'your_table'
) fk ON c.column_name = fk.column_name
WHERE c.table_name = 'your_table'
ORDER BY c.ordinal_position;

Practical Examples and Use Cases

Example 1: Basic DESCRIBE Equivalent

bash
# Connect to PostgreSQL database
psql -h localhost -U username -d database_name

# Describe a table
\d employees

Example 2: Get Detailed Information

bash
# Get detailed table information including storage parameters
\d+ employees

Example 3: Programmatic Query for Application Use

sql
-- Create a reusable view for table descriptions
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;

-- Query for specific table
SELECT * FROM table_description 
WHERE table_name = 'employees';

Example 4: Shell Script for Automated Table Description

bash
#!/bin/bash

# Usage: describe_table.sh <database> <table>
DB=$1
TABLE=$2

echo "Table: $TABLE"
echo "Columns:"
psql -d "$DB" -c "\d $TABLE" | grep -E "Column|Type|Collation|Storage|Stats|Description" || echo "No columns found"

echo -e "\nDetailed Information:"
psql -d "$DB" -c "\d+ $TABLE" | head -20

echo -e "\nSQL Query for Structure:"
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;"

Example 5: Cross-Database Compatibility Script

sql
-- Function to get table description compatible with 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;

-- Usage
SELECT * FROM describe_table('employees');

Sources

  1. PostgreSQL psql Documentation - Meta Commands
  2. PostgreSQL Information Schema Documentation
  3. PostgreSQL System Catalogs Documentation
  4. PostgreSQL Column Information Queries
  5. psql Command Reference

Conclusion

To perform the equivalent of Oracle’s DESCRIBE TABLE in PostgreSQL with psql, you have several effective options:

  • Use \d table_name for basic table structure information
  • Use \d+ table_name for detailed information including storage parameters and statistics
  • Query information_schema.tables and information_schema.columns for SQL-based approaches
  • Use system catalogs like pg_attribute for PostgreSQL-specific metadata

The choice depends on your needs: quick interactive exploration with psql commands, or programmatic access via SQL queries. For Oracle users transitioning to PostgreSQL, the \d command provides the most familiar experience, while information_schema queries offer the most portable solution for application integration.

Remember that PostgreSQL’s SHOW command can also be useful for specific configuration details, and you can create custom functions or views to tailor the output exactly to your preferred format from Oracle’s DESCRIBE.

How to get detailed column information including constraints and indexes in PostgreSQL?What's the difference between \d and \d+ commands in PostgreSQL psql?How to export table structure to SQL DDL in PostgreSQL?How to compare table structures between two PostgreSQL databases?How to automate table description tasks in PostgreSQL using shell scripts?How to create a custom DESCRIBE function in PostgreSQL for Oracle users?
Ask NeuroAgent