How to Get Column Names in PostgreSQL

Shihab Sikder Feb 02, 2024
  1. Get the Column Name With \d+ Command in PostgreSQL
  2. SQL Query to Get the Column Name
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:

Get the Column Name With d+ Command in PostgreSQL

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".

Shihab Sikder avatar Shihab Sikder avatar

I'm Shihab Sikder, a professional Backend Developer with experience in problem-solving and content writing. Building secure, scalable, and reliable backend architecture is my motive. I'm working with two companies as a part-time backend engineer.

LinkedIn Website

Related Article - PostgreSQL Column