PostgreSQL Table Details
Is there a postgreSQL query that shows all important details of a certain table?
The columns, the types and the constraints?
Hello Leandro 👋
Yes. In PostgreSQL there are several ways to inspect a table.
Option 1: Using psql (easiest)
If you're inside the PostgreSQL command-line client (psql):
SQL
\d your_table_name
For even more details:
SQL
\d+ your_table_name
This shows:
Columns
Data types
Nullable / NOT NULL
Default values
Primary keys
Foreign keys
Unique constraints
Indexes
Storage information (\d+)
Example:
SQL
\d customers
Option 2: Query information_schema.columns
If you want a SQL query:
SQL
SELECT
column_name,
data_type,
is_nullable,
Printed using ChatGPT to PDF, powered by PDFCrowd HTML to PDF API. 1/3
column_default
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'customers'
ORDER BY ordinal_position;
Output:
column_name data_type is_nullable column_default
id integer NO nextval(...)
name text NO null
email text YES null
Option 3: Show Constraints
To see primary keys, foreign keys, unique constraints, and check constraints:
SQL
SELECT
tc.constraint_name,
tc.constraint_type,
kcu.column_name
FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
WHERE tc.table_schema = 'public'
AND tc.table_name = 'customers';
Option 4: One Query with Columns + Constraints
A more complete query:
SQL
SELECT
c.column_name,
c.data_type,
c.is_nullable,
c.column_default,
tc.constraint_type
FROM information_schema.columns c
LEFT JOIN information_schema.key_column_usage kcu
ON c.table_name = kcu.table_name
AND c.column_name = kcu.column_name
LEFT JOIN information_schema.table_constraints tc
ON kcu.constraint_name = tc.constraint_name
WHERE c.table_schema = 'public'
AND c.table_name = 'customers'
ORDER BY c.ordinal_position;
Option 5: Generate the Original DDL
If your goal is to understand everything about a table (similar to SQL Server's sp_help), I usually prefer:
SQL
pg_dump -s -t customers your_database
Printed using ChatGPT to PDF, powered by PDFCrowd HTML to PDF API. 2/3
or from tools such as pgAdmin:
SQL
SELECT pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'customers'::regclass;
This gets you very close to the original CREATE TABLE definition.
My recommendation
For daily work:
SQL
\d+ customers
For documentation or data engineering work:
SQL
SELECT *
FROM information_schema.columns
WHERE table_name = 'customers';
and
SQL
SELECT *
FROM information_schema.table_constraints
WHERE table_name = 'customers';
Together they provide nearly all metadata you usually need. 😊
Ansel
Printed using ChatGPT to PDF, powered by PDFCrowd HTML to PDF API. 3/3