How to Show a Table Schema in PostgreSQL

psql

\d is the fastest way to understand a table: columns with types and nullability, then indexes, check constraints, foreign keys, and any triggers. \d+ adds storage type, compression, and column comments. Qualify the name as \d archive.orders when the table is not in your search_path.

\d orders
\d+ orders

Plain SQL (works from any client)

information_schema.columns is standard SQL and works from any client, which makes it the right choice inside application code or a saved query. It covers columns only, not indexes or constraints, and reports character_maximum_length and numeric_precision as separate columns rather than as a formatted type string. Ordering by ordinal_position gives you the table's real column order.

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

Getting the full DDL, not just the columns

information_schema.columns gives you names and types, but not the constraints, indexes or defaults that make the table what it is. In psql, \d+ shows all of it at once. Outside psql there is no built-in equivalent of SHOW CREATE TABLE: pg_dump is the reliable way to get a definition you can replay.

pg_dump --schema-only --table=public.orders analytics

Common errors

SELECT schemaname, tablename FROM pg_tables WHERE tablename ILIKE '%order%';

SHOW search_path;
SELECT * FROM analytics."Orders" LIMIT 1;

Do more with PostgreSQL

• Export Postgres to Google Sheets on a schedule
• Postgres SQL editor in the browser