​
Overview

The pg_attribute stores information about table columns. It mimics the pg_attribute PostgreSQL system catalog.

​
Columns

This table is designed for compatibility with tools that require PostgreSQL system tables, so it mostly has dummy data. Please note that not all columns in pg_attribute are applicable to every type of relation

The following columns are available for querying in pg_attribute:

ColumnTypeDescription
attrelidintThis column represents the OID of the table (See pg_class)
attnametextThis column represents the column name as specified in CREATE TABLE
atttypidintThis column represents the OID of the column type (See pg_type)
attnumintThis column represents the column index (1-based)
attlenintThis column represents the byte size of the value (-1 for varying length types)
attnotnullboolThis column represents the not-null constraint. true if the column was declared as NOT NULL
attcacheoffintUnused.
atttypmodintUnused.
attndimsintUnused.
attbyvalboolUnused.
attaligntextUnused.
attstoragetextUnused.
attcompressiontextUnused.
atthasdefboolUnused.
atthasmissingboolUnused.
attidentitytextUnused.
attgeneratedtextUnused.
attisdroppedboolUnused.
attislocalboolUnused.
attinhcountintUnused.
attstattargetintUnused.
attcollationintUnused.
attacltextUnused.
attoptionstextUnused.
attfdwoptionstextUnused.
attmissingvaltextUnused.

​
Example

​
Retrieving Column Information for All Tables

  1. This example queries the pg_attribute to retrieve information about all columns across all tables in the database:
SELECT attrelid, attname, atttypid, attnum
FROM pg_attribute;

 attrelid |       attname       | atttypid | attnum 
----------+---------------------+----------+--------
      100 | oid                 |       23 |      1
      100 | nspname             |       25 |      2
      100 | nspowner            |       23 |      3
      100 | nspacl              |       25 |      4
      101 | indexrelid          |       23 |      1
      101 | indrelid            |       23 |      2
      101 | indnatts            |       23 |      3
      101 | indnkeyatts         |       23 |      4
      101 | indisunique         |       16 |      5
      101 | indnullsnotdistinct |       16 |      6
      101 | indisprimary        |       16 |      7
      101 | indisexclusion      |       16 |      8
      101 | indimmediate        |       16 |      9
      101 | indisclustered      |       16 |     10
      101 | indisvalid          |       16 |     11
      101 | indcheckxmin        |       16 |     12
      101 | indisready          |       16 |     13
      101 | indislive           |       16 |     14
      101 | indisreplident      |       16 |     15
      101 | indkey              |       23 |     16
      101 | indcollation        |       23 |     17
      101 | indclass            |       23 |     18
      101 | indoption           |       23 |     19
      101 | indexprs            |       23 |     20
      101 | indpred             |       23 |     21
(25 rows)
If your database has many tables, the result could be quite long. You can manage the output by filtering specific criteria using WHERE clauses or by limiting the number of rows with LIMIT clauses

​
Filtering Columns

  1. Create a new table:
CREATE TABLE books (
    book_id int,
    title text,
    author text,
    genre text,
    publication_year int
);
  1. To get the OID (Object Identifier) of the books table, run the pg_class.oid query:
SELECT oid, relname FROM pg_class WHERE relname = 'books';
  1. It will return the books table with its OID:
 oid  | relname 
------+---------
 1009 | books
  1. To filter columns, we need to utilize the pg_attribute with WHERE clause:
SELECT attrelid, attname, atttypid, attnum
FROM pg_attribute
WHERE attrelid = 1009;
  1. The output should provide you with columns of the books table that you’ve just created:
 attrelid |     attname      | atttypid | attnum 
----------+------------------+----------+--------
     1009 | book_id          |       23 |      1
     1009 | title            |       25 |      2
     1009 | author           |       25 |      3
     1009 | genre            |       25 |      4
     1009 | publication_year |       23 |      5

​
Joining pg_attribute with pg_class for Table and Column Names

  1. In this example, a join operation with pg_class is performed to include the name of the table (relname):
SELECT attrelid, relname, attname, atttypid, attnum
FROM pg_attribute
JOIN pg_class ON attrelid = oid;
  1. You will receive both table and column details in a single query result:
 attrelid |     relname      |    attname    | atttypid | attnum 
----------+------------------+---------------+----------+--------
     1000 | client           | client_id     |       23 |      1
     1000 | client           | client_name   |       25 |      2
     1000 | client           | client_origin |       25 |      3
     1001 | distance_table   | distance      |       23 |      1
     1001 | distance_table   | unit          |       25 |      2
     1002 | weight           | kilo          |       23 |      1
     1002 | weight           | gram          |       23 |      2
     1003 | product          | id            |       23 |      1
     1003 | product          | product       |       25 |      2
     1003 | product          | category      |       25 |      3
     1003 | product          | price         |       23 |      4
     1004 | salary           | empid         |       23 |      1
     1004 | salary           | empname       |       25 |      2
     1004 | salary           | empdept       |       25 |      3
     1004 | salary           | empaddress    |       25 |      4
     1004 | salary           | empsalary     |       23 |      5
     1005 | customer         | cust_id       |       23 |      1
     1005 | customer         | cust_name     |       25 |      2
     1006 | personal_details | id            |       23 |      1
     1006 | personal_details | first_name    |       25 |      2
     1006 | personal_details | last_name     |       25 |      3
     1006 | personal_details | gender        |       25 |      4