Describe Table in PostgreSQL – psql, SQL Metadata, and Examples | DbSchema

PostgreSQL does not have a MySQL-style DESCRIBE TABLE SQL command. Instead, you usually inspect a table with psql meta-commands such as \d and \d+, or with metadata queries against information_schema and pg_catalog.

Table of Contents

1. What “describe table” means in PostgreSQL
2. Version notes
3. Describe a table in psql
4. Query columns with information_schema
5. Inspect constraints and indexes with pg_catalog
6. Common column types you will see in output
7. Describe all tables in a schema
8. Inspect table structure visually in DbSchema
9. FAQ
10. Conclusion

What “describe table” means in PostgreSQL

When developers say "describe a table in PostgreSQL," they usually want to see:

• column names and data types
• nullability and default values
• primary keys, foreign keys, and check constraints
• indexes and storage details
• comments or generated column information

The exact method depends on whether you are in psql, an application query tool, or a visual client such as DbSchema.

Version notes

• \d and \d+ are psql meta-commands, so they do not run as SQL over JDBC.
• information_schema.columns is portable and works well across PostgreSQL versions.
• pg_catalog is PostgreSQL-specific and exposes richer details such as exact constraint definitions and index metadata.
• Identity and generated-column metadata are easier to inspect in current PostgreSQL versions because more information is exposed directly in system views.

Describe a table in psql

Connect to your PostgreSQL database in psql:

psql -U your_username -d your_database

Then use \d followed by the table name:

\d public.orders

This prints column names, data types, and modifiers such as NOT NULL and DEFAULT.

For more detail, use:

\d+ public.orders

Typical \d+ output includes columns, indexes, constraints, and storage details:

                                                        Table "public.orders"
   Column    |           Type           | Collation | Nullable |               Default
-------------+--------------------------+-----------+----------+--------------------------------------
 order_id    | bigint                   |           | not null | generated always as identity
 customer_id | bigint                   |           | not null |
 status      | character varying(20)    |           | not null | 'new'::character varying
 total       | numeric(12,2)            |           | not null |
 created_at  | timestamp with time zone |           | not null | CURRENT_TIMESTAMP
Indexes:
    "orders_pkey" PRIMARY KEY, btree (order_id)
    "orders_status_idx" btree (status)
Foreign-key constraints:
    "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
Access method: heap

Useful variants:

• \d schema.table_name for a schema-qualified object
• \d+ table_name for richer output
• \dt to list tables before picking one to describe

If you are browsing lots of objects, Show Tables in PostgreSQL and Essential PostgreSQL Commands are good companion pages.

Query columns with information_schema

For programmatic access or when psql is not available, query information_schema.columns:

SELECT column_name,
       data_type,
       character_maximum_length,
       numeric_precision,
       numeric_scale,
       is_nullable,
       column_default,
       is_identity,
       is_generated
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'orders'
ORDER BY ordinal_position;

Use this query when you need metadata in scripts, reporting, or application tooling.

Inspect constraints and indexes with pg_catalog

List constraints

SELECT conname AS constraint_name,
       contype AS constraint_type,
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'public.orders'::regclass
ORDER BY conname;

Constraint type codes:

• p = primary key
• f = foreign key
• u = unique
• c = check

List indexes

SELECT indexname,
       indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'orders'
ORDER BY indexname;

Get the table comment

SELECT obj_description('public.orders'::regclass) AS table_comment;

These PostgreSQL-native queries are often more useful than information_schema when you need exact definitions.

Common column types you will see in output

If the type list makes you realize the table definition needs changes, continue with Create Table in PostgreSQL or review related keys in Foreign Keys in PostgreSQL.

Describe all tables in a schema

To list all tables first, use:

\dt+ public.*

For an SQL-based overview, you can query all base tables and count their columns:

SELECT t.table_name,
       COUNT(c.column_name) AS column_count
FROM information_schema.tables t
JOIN information_schema.columns c
  ON c.table_schema = t.table_schema
 AND c.table_name = t.table_name
WHERE t.table_schema = 'public'
  AND t.table_type = 'BASE TABLE'
GROUP BY t.table_name
ORDER BY t.table_name;

That is a good first step before documenting a schema or reverse-engineering it in DbSchema.

Inspect table structure visually in DbSchema

DbSchema lets you inspect PostgreSQL tables without remembering every metadata query. Connect via the PostgreSQL JDBC driver, reverse-engineer the schema, and click any table to see its columns, defaults, indexes, and relationships.

DbSchema is useful when you want to:

• browse columns, constraints, and foreign keys in one place
• compare table structure across environments
• document the schema as interactive HTML
• move from inspection to editing without switching tools

See connect to database, schema documentation, and diagram for the full workflow.

FAQ

Does PostgreSQL have a DESCRIBE TABLE command?

No. Use \d table_name or \d+ table_name in psql, or query metadata tables in SQL.

How do I see defaults and generated values?

Use \d+ in psql, or select column_default, is_identity, and is_generated from information_schema.columns.

How do I describe a table without psql?

Use SQL queries against information_schema.columns, pg_constraint, and pg_indexes, or inspect the table in DbSchema.

How do I list all tables before describing one?

Use \dt or \dt+ in psql, or query information_schema.tables. The full walkthrough is in Show Tables in PostgreSQL.

Can DbSchema show the same information visually?

Yes. DbSchema reverse-engineers the PostgreSQL schema through JDBC and shows table columns, relationships, indexes, and documentation in one interface.

Conclusion

To describe a table in PostgreSQL, use \d and \d+ for quick inspection in psql, or query information_schema and pg_catalog when you need SQL-based metadata. Those methods cover columns, defaults, constraints, indexes, and comments.

If you prefer not to memorize every command, DbSchema can inspect the same PostgreSQL structure visually, document it, and keep the schema model connected through JDBC.