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:
ID INT PRIMARY KEY,
name TEXT
);ID INT PRIMARY KEY, name TEXT);
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:
COLUMN | TYPE | Collation | NULLABLE | DEFAULT
--------+---------+-----------+----------+---------
id | INTEGER | | NOT NULL |
name | text | | |
Indexes:
"some_tbl_pkey" PRIMARY KEY, btree (id)
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:
COLUMN | TYPE | Collation | NULLABLE | DEFAULT | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | INTEGER | | NOT NULL | | plain | |
name | text | | | | extended | |
Indexes:
"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 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:
FROM information_schema.COLUMNS WHERE TABLE_NAME = 'some_tbl';
This query should return something like the following:
-------------+-----------
id | INTEGER
name | text
(2 ROWS)
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”
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started