The PostgreSQL Describe Table Statement

Introduction to the PostgreSQL DESCRIBE TABLE statement

In MySQL, the DESCRIBE statement is used to get detailed information on a table or column. However, it’s important to know that this SQL statement is not available within the psql command-line interface for Postgres. Even though there’s no specific PostgreSQL DESCRIBE TABLE command, there are still some easy ways to get the information you need about a Postgres table. In this article, we’ll show you alternatives to the DESCRIBE TABLE SQL statement that can be used with psql.

Prerequisites to using PostgreSQL

Before we look at any SQL statements, there are a few important prerequisites that need to be in place for this tutorial. Make sure that both PostgreSQL and the psql command-line interface are installed on your machine. You can use the psql -V command to find out what version of PostgreSQL is installed.

Connect to a Postgres database using psql

We use the following syntax to access a PostgreSQL database using the psql command-line interface:

If prompted, input your password– you should then have access to the database and its tables.

Next, type the following command to enter a specific database:

If you find that you’re unable to connect to a Postgres database due to an authentication failure, try the following command to connect to Postgres using the postgres role:

Create a PostgreSQL database and table

If you don’t have a database set up yet, you’ll need to create one for this tutorial. You can use the following command to create a database in psql:

After creating the database, you’ll be able to connect to it using the command \c followed by the database name.

Now that we’ve set up our database, let’s create a PostgreSQL table that we can use to test our alternatives to the DESCRIBE statement. The following SQL command allows us to create a table in psql with two columns:

Using the DESCRIBE TABLE SQL statement in psql

The DESCRIBE statement isn’t a valid SQL statement in the psql command-line interface; however, there are other ways to have Postgres return information on the columns in a table.

Use the ‘d’ command in psql to describe a Postgres table

We can use the \d or \d+ commands, followed by the table name, to query and retrieve information on the columns of a table.

When you execute the above command, you’ll get results that look like the following:

Using the \d+ some_tbl command will extend the \d command, providing additional information on its Default, Storage, Stats Target and Description columns.

The output from the command will look like this:

Use a PostgreSQL table’s information_schema to describe the table

Another simple way to get information about a table is to use a SELECT statement to query the COLUMNS attribute in a Postgres table’s information_schema.

Shown below is an example of how we can get data from information_schema.COLUMNS for the PostgreSQL table that we created earlier:

This query should return something like the following:

We can see that our SELECT statement found and returned the column_name and data_type attributes for the specified PostgreSQL table.

Conclusion to the PostgreSQL DESCRIBE TABLE statement

When you’re looking for detailed information about a given column or table, your first instinct may be to use the DESCRIBE TABLE command, especially if you’re accustomed to using that command in MySQL. However, this command isn’t a valid SQL statement in PostgreSQL and cannot be used in the psql command-line interface. Fortunately, there are a few simple PostgreSQL commands that can be used in place of DESCRIBE TABLE. In this article, we looked at these PostgreSQL DESCRIBE TABLE alternatives; with our examples to guide you, you’ll be able to retrieve the information you need about your own PostgreSQL tables. Please include the meta keywords. “postgresql describe table”