How to Get Column Names in PostgreSQL
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 "accounts"
.
Syntax:
\d+ accounts
Output:
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.
Query:
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'accounts';
Output:
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.
Query:
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 "schema-table"
.