See the different ways that PostgreSQL provides to describe a table as you would with the DESCRIBE statement in MySQL.
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:
For example, you could run it against a
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:
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:
Now that you are logged in, connect to your target database.
Replace with the name of the database you want to work with.
Next, type one of the commands below to describe a table in
Consider the command below:
This would return:
2. DESCRIBE TABLE in PostgreSQL Using a Query
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:
table_name = '<table_name>';
Replace with the name of the table you want to describe.
Let’s run the query on the
This time, you would get:
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.
If that is not enough, you can find extra information about the table as triggers and partitions in the dropdown menu:
Et voilà! The PostgreSQL
DESCRIBE TABLE alternatives have no more secrets!
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!
Why is the MySQL DESCRIBE table command not present in PostgreSQL?
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?
DESCRIBE command returns information about a prepared statement or result set. The syntax to use it is:
DESCRIBE [ OUTPUT ] <prepared_statement_name> USING [ SQL ] DESCRIPTOR <descriptor_name>
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+ 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.