See the different ways that PostgreSQL provides to describe a table as you would with the DESCRIBE statement in MySQL.

Tools used in this tutorial

DbVisualizer, top rated database management tool and SQL client.
The PostgreSQL database

If you are a MySQL user, you may be familiar with the DESCRIBE statement. That is a synonym of EXPLAIN that gives you information about table structure or query execution plans. Unfortunately, PostgreSQL does not support the DESCRIBE statement. However, you can achieve the same result in other ways.

Let's explore all the DESCRIBE TABLE PostgreSQL alternatives through examples and find out which one is the best.

DESCRIBE TABLE in PostgreSQL

In PostgreSQL, the DESCRIBE statement does not exist. But what does DESCRIBE do in other DBMSs? Time to find out!

Consider the SQL query below:

1 DESCRIBE <table_name>

For example, you could run it against a user table:

That would produce a description of the columns within the table:

As you can see, the DESCRIBE statement returns a complete description of the columns of the table, including data types, nullability, primary key constraints, and default values.

How to Describe a Table in PostgreSQL

DESCRIBE is a useful feature and even though PostgreSQL does not support it directly, there are three working DESCRIBE TABLE PostgreSQL alternatives. Let’s see them all, digging into their pros and cons!

1. DESCRIBE TABLE in PostgreSQL Using the Command Line

This approach involves using a psql, the terminal-based PostgreSQL front-end.

First, open the terminal and connect to your PostgreSQL server with:

$ psql -U

Replace with the username of the account you want to log in with. psql will then ask you for the user's password. Type it in and press Enter to connect to the database.

Security tip: Since other users can observe executed commands by looking at the CLI command history, you may prefer to avoid typing a password for security reasons by:

• Setting the password in the PGPASSWORD environment variable
• Specifying the password to the .pgpass file

Now that you are logged in, connect to your target database.

$ \c database_name

Replace with the name of the database you want to work with.

Next, type one of the commands below to describe a table in psql:

\d: Returns all columns, their data types, the tablespace, and any special attributes such as NOT NULL and defaults, as well as associated indexes, constraints, rules, and triggers.
\d+: Same as above but with more information, including comments associated with the columns.
Consider the command below:

$ \d employee

This would return:

While:

$ \d+ employee

Produces:

👍 Pros:

• A lot of information returned, including indexes and foreign key constraints
  👎 Cons:

• CLI might scare non-experienced users

• Requires several steps

• Results limited by the visualization capabilities of the CLI

2. DESCRIBE TABLE in PostgreSQL Using a Query

A simple DESCRIBE TABLE PostgreSQL equivalent approach involves running a query against information_schema.columns catalog. This view contains information about all columns of any table in the database.

You can use it to describe a table with the query below:

1 SELECT
2    column_name,
3    data_type
4 FROM
5    information_schema.columns
6 WHERE
7    table_name = '<table_name>';

Replace with the name of the table you want to describe.

Let’s run the query on the employee table:

This time, you would get:

👍 Pros:

• Straightforward, requires a single query
  👎 Cons:

• No information about indexes and foreign key constraints

• Limited column information compared to \d+

3. Describe a Table in a Database Client

The easiest way to describe a table in PostgreSQL is by adopting a database client. There are several options on the market but only one supports all PostgreSQL major features, has top user reviews, and is used by NASA. Its name is DbVisualizer!

Download DbVisualizer for free, follow the installation wizard, and set up a PostgreSQL connection.

Now, describing a table becomes a piece of cake. In the dropdown menu on the right, select the database you want to deal with, find the table you are interested in exploring, right-click on it, and select “Open in New Tab:”

This will open a section with various tabs where you can get visual information about the table details, columns, indexes, foreign keys, and more.

Et voilà! The PostgreSQL DESCRIBE TABLE alternatives have no more secrets!

👍 Pros:

• In-depth, visual information
• Viable even by non-technical users
• Takes only a few clicks

👎 Cons:

• Setting up the client might take some time

Conclusion

Here, you saw everything you should know about describing tables in PostgreSQL. Specifically, you understood what DESCRIBE is in MySQL, saw that PostgreSQL does not support it, and took a look at the PostgreSQL DESCRIBE TABLE alternatives.

As shown above, getting information about a table and its columns, indexes, and foreign key constraints becomes easier with a database client such as DbVisualizer. In particular, DbVisualizer allows you to visually explore a table, from its structure to its data, with just a few clicks. Also, it gives you access to a graph view where you can see the table in an ER-like schema. Try DbVisualizer for free today!

FAQ

Why is the MySQL DESCRIBE table command not present in PostgreSQL?

The MySQL DESCRIBE command is not present in PostgreSQL because the latter DBMS is SQL standards compliant, and DESCRIBE TABLE is not part of the ANSI SQL specification.

What does the DESCRIBE command do in PostgreSQL?

The PostgreSQL DESCRIBE command returns information about a prepared statement or result set. The syntax to use it is:

1 DESCRIBE [ OUTPUT ] <prepared_statement_name> USING [ SQL ] DESCRIPTOR <descriptor_name>

Note that DESCRIBE is specified in the SQL standard.

What is the easiest PostgreSQL describe table approach?

The best way to get complete information about a table in PostgreSQL is to explore it in a fully-featured database client like DbVisualizer.

How to deal with the “did not find any relation named” error?

The "did not find any relation named" error occurs when \d or \d+ cannot find the specified table. To address that issue, you have to check the table name and may need to wrap it with double quotes.

How to list databases in PostgreSQL?

psql is a powerful tool, and in addition to \d to describe a table, it also offers the \l command to get the list of available databases. Check out our article to learn more about how to list databases in Postgres.

About the author

Antonello Zanini is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.