POSTGRESQL

DESCRIBE TABLE PostgreSQL Alternatives

intro

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

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
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:

Copy
        
1 DESCRIBE <table_name>

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

Running the MySQL DESCRIBE TABLE query in DbVisualizer.
Running the MySQL DESCRIBE TABLE query in DbVisualizer.

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

MySQL DESCRIBE TABLE query result.
A zoom in on the MySQL DESCRIBE TABLE query result.

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:

Copy
        
$ 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.

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

  • \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:

Copy
        
$ \d employee

This would return:

The result of the d command.
The result of the "\d" command.

While:

Copy
        
$ \d+ employee

Produces:

The result of the d+ command.
The result of the "d+" command.

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

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

Running the query in DbVisualizer.
Running the query in DbVisualizer.

This time, you would get:

The query result.
A zoom in on the query result.

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

Note the Open in New Tab option.
Note the "Open in New Tab" option.

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

Table description info in DbVisualizer.
Table description info in DbVisualizer.

If that is not enough, you can find extra information about the table as triggers and partitions in the dropdown menu:

Triggers and Partitions dropdowns.
"Triggers" and "Partitions" dropdowns.

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:

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

Dbvis download link img
About the author
Antonello Zanini

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

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

A Guide To the SQL DECLARE Statement

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SERVER 7 min 2024-12-12
title

SQL Upsert: Inserting a Record If It Does Not Exist

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-12-10
title

How to Find Elements In an Array: PostgreSQL Guide

author Antonello Zanini tags POSTGRESQL 3 min 2024-12-05
title

How and Why to Add an Index in SQL

author Lukas Vileikis tags MySQL POSTGRESQL Search SQL SQL SERVER 8 min 2024-11-28
title

Postgres Create Array of Numbers: Complete Guide

author Lukas Vileikis tags POSTGRESQL SQL 7 min 2024-11-21
title

List of SQL Reserved Words in All Major Databases

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 16 min 2024-11-20
title

SQL Variable: What It Is and How To Use It

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-11-18
title

Postgres DATEDIFF Equivalent Functions: Calculating Date and Time Differences

author Lukas Vileikis tags POSTGRESQL 6 min 2024-11-04
title

CREATE SCHEMA PostgreSQL Statement: What, Why & When to Use

author Lukas Vileikis tags POSTGRESQL 4 min 2024-10-30
title

SQL EXPLAIN: The Definitive Tool to Optimize Queries

author Antonello Zanini tags MySQL POSTGRESQL SQL 12 min 2024-10-29

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗