This article will discuss how to get the column name with the
\d+ command in PostgreSQL.
Get the Column Name With
\d+ Command in PostgreSQL
A column has multiple properties. PostgreSQL has some built-in functionality that can show the column details of a table.
In my system, there’s the following table in the database.
postgres=# \dt List of relations Schema | Name | Type | Owner --------+----------+-------+---------- public | account | table | postgres public | accounts | table | postgres public | bank | table | postgres public | logger | table | postgres public | product | table | postgres public | purchase | table | postgres public | randoms | table | postgres public | students | table | postgres public | times | table | postgres public | wishlist | table | postgres (10 rows) postgres=#
I want to see all the column names, types, and details for the table
Also, this command shows all the constraints related to this table.
SQL Query to Get the Column Name
Before writing the query, we need to know which table store this information. The
"information_schema.columns" hold the column information for the table, and it has many fields.
The full list is seen here.
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'accounts';
column_name | data_type -------------+----------------------------- user_id | integer postcode | integer age | integer height | integer timestamp | timestamp without time zone username | character varying password | character varying email | character varying contact | character varying (9 rows)
Also, you can provide an additional check. Because here, I’m using the PSQL shell. I’m connected to a specific database by default, and the query runs over that schema.
The updated SQL statement will look like the following.
SELECT column_name, data_type, FROM information_schema.columns WHERE table_name = 'table_name' AND table_catalog = 'database_name' AND table_schema = 'schema_name'
Here’s the official documentation link for the