PostgreSQL – Describe a Table

PostgreSQL does not provide a ‘DESCRIBE’ command like Oracle. You can however describe a table in Postgres either by querying the information_schema or using the psql tool.

Run the below sql command to list all the columns and their data types of a particular table where ‘schemaname‘ is the name of the schema and ‘tablename‘ is the name of your table.

SELECT table_name, column_name, data_type, column_default, is_nullable 
FROM information_schema.columns
WHERE table_schema='schemaname' and table_name = 'tablename';

To describe a table using psql tool, simply run the command ‘\d tablename’ as shown below where ‘schemaname‘ is the name of the schema and ‘tablename‘ is the name of the table. Here I’m connected to ‘mydb‘ database.

mydb=# \d schemaname.tablename

Below command gives additional details about the table.

mydb=# \d+ schemaname.tablename
See also  PostgreSQL - Composite Primary Key