DESCRIBE TABLE PostgreSQL Alternatives
Tags: postgressql
Published: 2023-10-30 08:00:00

**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](https://www.dbvis.com/), top rated database management tool and SQL client.
The [PostgreSQL](https://www.postgresql.org/) 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:

<br />
![Running the MySQL DESCRIBE TABLE query in DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jv5k4bggis67h30lcv4v.png)
<figure><figcaption>Running the MySQL DESCRIBE TABLE query in DbVisualizer.</figcaption></figure>
<br />

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

<br />
![A zoom in on the MySQL DESCRIBE TABLE query result.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y9nz7aggs4aelqtak2c6.png)
<figure><figcaption>A zoom in on the MySQL DESCRIBE TABLE query result.</figcaption></figure>
<br />

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](https://www.postgresql.org/docs/current/app-psql.html), 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:

<br />
![The result of the "\d" command.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w62lhczxkcs2kg9khgso.png)
<figure><figcaption>The result of the "\d" command.</figcaption></figure>
<br />

While:

```
$ \d+ employee
```

Produces:

<br />
![The result of the "\d+" command.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/okp5erw5mr02jkyck0f2.png)
<figure><figcaption>The result of the "\d+" command.</figcaption></figure>
<br />

👍 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:

<br />
![Running the query in DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u6rkbprd3t20a97hxkp9.png)
<figure><figcaption>Running the query in DbVisualizer.</figcaption></figure>
<br />

This time, you would get:

<br />
![A zoom in on the query result.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s5of563ea2yuukrmol95.png)
<figure><figcaption>A zoom in on the query result.</figcaption></figure>
<br />

👍 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](https://www.dbvis.com/toprated/), and is used by NASA. Its name is DbVisualizer!

[Download DbVisualizer for free](https://www.dbvis.com/download/), 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:”

<br />
![Note the "Open in New Tab" option.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wzp1t03k7f6ne2de5un0.png)
<figure><figcaption>Note the "Open in New Tab" option.</figcaption></figure>
<br />

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

<br />
![Table description info in DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9wc48zlted1l9jmxmagr.png)
<figure><figcaption>Table description info in DbVisualizer.</figcaption></figure>
<br />

If that is not enough, you can find extra information about the table as triggers and partitions in the dropdown menu:
<br />
!["Triggers" and "Partitions" dropdowns.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yxk4n8c63x85ttr4u8ul.png)
<figure><figcaption>"Triggers" and "Partitions" dropdowns.</figcaption></figure>
<br />

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](https://www.dbvis.com/features/database-management/#explore-visualize), 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!](https://www.dbvis.com/download/)

## 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](https://www.dbvis.com/).

### 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.