pg_class

Overview

The pg_class stores information about tables and indexes in the database. It contains exactly one row per table (or index) created in the database. It mimics the pg_class 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_class are applicable to every type of relation.

The following columns are available for querying in pg_class:

Column	Type	Description
oid	int	This column represents the table/index object ID (OID) generated by oxla
relname	text	This column represents the table/index name as specified by the user during creation
relnamespace	int	This column represents the OID of the namespace the relation resides in
relhasindex	bool	Returns true if the table has any indexes
relkind	text	This column represents the type of relation: r for tables and i for indexes
reltype	int	Unused
reloftype	int	Unused
relowner	int	Unused
relam	int	Unused
relfilenode	int	Unused
reltablespace	int	Unused
relpages	int	Unused
reltuples	float	Unused
relallvisible	int	Unused
reltoastrelid	int	Unused
relisshared	bool	Unused
relpersistence	text	Unused
relnatts	int	Unused
relchecks	int	Unused
relhasrules	bool	Unused
relhastriggers	bool	Unused
relhassubclass	bool	Unused
relrowsecurity	bool	Unused
relforcerowsecurity	bool	Unused
relispopulated	bool	Unused
relreplident	text	Unused
relispartition	bool	Unused
relrewrite	int	Unused
relfrozenxid	int	Unused
relacl	text	Unused
reloptions	text	Unused
relminmxid	text	Unused
relpartbound	text	Unused

Example

1. Create a table and define its schema:

CREATE TABLE customer_orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    total_amount INT
);

2. Create an index on the customer_orders table for the customer_id column:

CREATE INDEX idx_customer_id ON customer_orders (customer_id);

3. Query the pg_class catalog to retrieve information about the customer_orders table and the index you’ve just created:

SELECT oid, relname, relkind, relhasindex, relnamespace 
FROM pg_class 
WHERE relname IN ('customer_orders', 'idx_customer_id');

4. The query will return information about the customer_orders table and the index:

 oid  |     relname     | relkind | relhasindex | relnamespace 
------+-----------------+---------+-------------+--------------
 1013 | idx_customer_id | i       | f           |            0
 1012 | customer_orders | r       | t           |            0
(2 rows)