Get Column Names in PostgreSQL

  1. Get the Column Name With \d+ Command in PostgreSQL
  2. SQL Query to Get the Column Name

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

Write for us
DelftStack articles are written by software geeks like you. If you also would like to contribute to DelftStack by writing paid articles, you can check the write for us page.

Related Article - PostgreSQL Column

  • Concatenate Columns in PostgreSQL Using Select
  • Single Query to Rename and Change Column Type in PostgreSQL