# Source: https://docs.oxla.com/system-catalogs/catalogs/pg_attribute.md

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.oxla.com/llms.txt
> Use this file to discover all available pages before exploring further.

# pg_attribute

## Overview

The `pg_attribute` stores information about table columns. It mimics the [pg\_attribute](https://www.postgresql.org/docs/current/catalog-pg-attribute.html) PostgreSQL system catalog.

## Columns

<Note>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</Note>

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:

```sql  theme={null}
SELECT attrelid, attname, atttypid, attnum
FROM pg_attribute;
```

```sql  theme={null}
 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)
```

<Note>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</Note>

### Filtering Columns

1. Create a new table:

```sql  theme={null}
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:

```sql  theme={null}
SELECT oid, relname FROM pg_class WHERE relname = 'books';
```

3. It will return the **books** table with its OID:

```sql  theme={null}
 oid  | relname 
------+---------
 1009 | books
```

4. To filter columns, we need to utilize the `pg_attribute` with `WHERE` clause:

```sql  theme={null}
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:

```sql  theme={null}
 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`):

```sql  theme={null}
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:

```sql  theme={null}
 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
```