> ## 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
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:
```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)
```
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:
```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
```