intro
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:
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:
Now that you are logged in, connect to your target database.
1
\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:
Consider the command below:
$
\d employee
This would return:
While:
$
\d+ employee
Produces:
👍 Pros:
👎 Cons:
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:
👎 Cons:
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!
👍 Pros:
👎 Cons:
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.
