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
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
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
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:
ID INT PRIMARY KEY,
);ID INT PRIMARY KEY, name TEXT);
Using the DESCRIBE TABLE SQL statement in psql
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+ 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:
COLUMN | TYPE | Collation | NULLABLE | DEFAULT
id | INTEGER | | NOT NULL |
name | text | | |
"some_tbl_pkey" PRIMARY KEY, btree (id)
\d+ some_tbl command will extend the
\d command, providing additional information on its
Stats Target and
The output from the command will look like this:
COLUMN | TYPE | Collation | NULLABLE | DEFAULT | Storage | Stats target | Description
id | INTEGER | | NOT NULL | | plain | |
name | text | | | | extended | |
"some_tbl_pkey" PRIMARY KEY, btree (id)
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
Shown below is an example of how we can get data from
information_schema.COLUMNS for the PostgreSQL table that we created earlier:
FROM information_schema.COLUMNS WHERE TABLE_NAME = 'some_tbl';
This query should return something like the following:
id | INTEGER
name | text
We can see that our
SELECT statement found and returned the
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”
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started