PostgreSQL Describe Table

Summary: in this tutorial, you will learn how to use the psql tool and information_schema to describe tables in PostgreSQL.

If you have been using MySQL, you typically use the DESCRIBE statement to find the information on a table.

PostgreSQL does not support the DESCRIBE statement. However, you can query the information on columns of a table in a couple of ways.

1) PostgreSQL DESCRIBE TABLE using psql

First, connect to PostgreSQL server using the psql tool:

$ psql -U postgres -W
Code language: Shell Session (shell)

Second, enter the password for the postgres user:

Password:
...
postgres=#
Code language: Shell Session (shell)

Third, switch to the database that you want to work with e.g., dvdrental

postgres=# \c dvdrental
Password for user postgres:
You are now connected to database "dvdrental" as user "postgres".
Code language: Shell Session (shell)

Finally, issue the command \d table_name or \d+ table_name to describe a table. The following example shows the information of the city table:

The command issued a lot of information on the structure of the city table. In addition, it also returned indexes, foreign key constraints, and triggers.

2) PostgreSQL DESCRIBE TABLE using information_schema

The information_schema.columns catalog contains the information on columns of all tables.

To get information on columns of a table, you query the information_schema.columns catalog. For example:

SELECT 
   table_name, 
   column_name, 
   data_type 
FROM 
   information_schema.columns
WHERE 
   table_name = 'city';
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to return information on a structure of a table using the psql tool and information_schema.