pg_attribute

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:

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.

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

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. It will return the books table with its OID:

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

4. To filter columns, we need to utilize the pg_attribute with 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

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;

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