Cloud

pg_attribute

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

Columns

The following columns are available for querying in pg_attribute:

Column Type Description

attrelid

int

This column represents the OID of the table (See pg_class)

attname

text

This column represents the column name as specified in CREATE TABLE

atttypid

int

This column represents the OID of the column type (See pg_type)

attnum

int

This column represents the column index (1-based)

attlen

int

This column represents the byte size of the value (-1 for varying length types)

attnotnull

bool

This column represents the not-null constraint. true if the column was declared as NOT NULL

attcacheoff

int

Unused.

atttypmod

int

Unused.

attndims

int

Unused.

attbyval

bool

Unused.

attalign

text

Unused.

attstorage

text

Unused.

attcompression

text

Unused.

atthasdef

bool

Unused.

atthasmissing

bool

Unused.

attidentity

text

Unused.

attgenerated

text

Unused.

attisdropped

bool

Unused.

attislocal

bool

Unused.

attinhcount

int

Unused.

attstattarget

int

Unused.

attcollation

int

Unused.

attacl

text

Unused.

attoptions

text

Unused.

attfdwoptions

text

Unused.

attmissingval

text

Unused.

Examples

Retrieve 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

Filter columns

  1. Create a new table:

    CREATE TABLE books (
    book_id int,
    title text,
    author text,
    genre text,
    publication_year int
);

  2. 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';

  3. This returns the books table with its OID:

     oid  | relname
------+---------
 1009 | books

  4. To filter columns, use pg_attribute with a WHERE clause:

    SELECT attrelid, attname, atttypid, attnum
FROM pg_attribute
WHERE attrelid = 1009;

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

Join 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;

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