2025-06-06 Web Development
PostgreSQL Query Schema
By O. Wolfson
In PostgreSQL, you can retrieve the schema details of a specific table by querying the information_schema.columns
view or by using the pg_catalog.pg_attribute
table. Here’s an example SQL query using information_schema.columns
:
sqlSELECT
column_name,
data_type,
character_maximum_length,
is_nullable,
column_default
FROM
information_schema.columns
WHERE
table_name = 'your_table_name'
ORDER BY
ordinal_position;
This query will provide details such as column names, data types, maximum length, whether the column allows NULL values, and any default values.
Be sure to replace 'your_table_name'
with the name of the table you want to inspect.
Select all tables in the database.
sqlSELECT
table_name,
column_name,
data_type,
is_nullable,
column_default
FROM
information_schema.columns
WHERE
table_schema = 'public'
ORDER BY
table_name,
ordinal_position;