How to Get OID of PostgreSQL Table

1. Introduction

In PostgreSQL, an OID (Object Identifier) is a 32-bit number used internally to uniquely identify various database objects, such as tables and functions. While system tables contain OIDs by default, user-defined tables no longer support OIDs as row identifiers since PostgreSQL version 12, where the WITH OIDS option was completely removed.

However, each table still has its own OID stored in system catalogs, which we can query for metadata or administrative purposes. OIDs were once used to identify rows in user tables, but this approach has been deprecated. Nowadays, primary keys are preferred for uniquely identifying rows.

In this tutorial, we’ll learn several methods to get the OID of a PostgreSQL table. We’ll use the simple university database in all demonstration examples.

2. Understanding PostgreSQL OID

An OID is a 4-byte (32-bit) special number that PostgreSQL uses to uniquely identify its internal objects. The OID value ranges from 0 to 4294967295, where 0 is reserved and considered an invalid OID. While it was once used in user-created tables, this is no longer the case in newer versions of PostgreSQL. Instead, it’s mostly used behind the scenes by the database itself.

To make working with OIDs easier, PostgreSQL also provides special types like regclass and regtype, which let us refer to objects by name rather than by their numeric OID. So, while OIDs are important for the system, most users don’t need to deal with them directly in their everyday work.

3. Using pg_class System Catalog

In PostgreSQL, pg_class is a system table that stores basic information about tables, indexes, views, and sequences. It tracks their names, types, owners, size, and storage details. PostgreSQL manages the database using this table internally, but advanced users, like database admins, can also check it to understand data organization and database performance. For everyday use, it’s better to look at simpler views like information_schema.

To get the OID of a PostgreSQL table, we can query the pg_class system catalog and filter it using the table name:

SELECT oid, relname
FROM pg_class
WHERE relname = 'student';

In this example, we fetched the OID of the student table using the pg_class system catalog:

 oid    |  relname
------- + ------------
975253  |  student
(1 row)

The pg_class catalog retrieves 975253 as the OID of the targeted table named student.

4. Using pg_catalog.pg_class With pg_catalog.pg_namespace

If we want to find a table’s OID in PostgreSQL, we can use the system catalogs pg_class and pg_namespace. The pg_class catalog stores information about all tables and other objects in the database, while pg_namespace keeps details about the schemas. We can use these catalogs to easily find the OID of a table, even if there are multiple tables with the same name across different schemas:

SELECT c.oid, n.nspname AS schema_name, c.relname AS table_name
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'student' AND n.nspname = 'public';

The pg_class and pg_namespace catalogs retrieve details about tables and their schemas, including OID:

 oid    |  schema_name  |  table_name
------- + ------------- + ------------
975253  |   public      |  student
(1 row)

This method retrieves the OID of a specific table within a particular schema, even if other tables with the same name exist in different schemas.

5. Using pg_table_is_visible() and pg_class

We can also find the OID of a PostgreSQL table by using the system catalogs pg_class and the pg_table_is_visible() function. The pg_class catalog contains details about all tables, and pg_table_is_visible() helps us check if a table is visible in our current search path. By combining these, we can find the OID of a table, even when multiple schemas might have tables with the same name:

SELECT c.oid, c.relname AS table_name
FROM pg_class c
WHERE c.relname = 'student' AND pg_table_is_visible(c.oid);

This method retrieves the OID only if the table is visible in the current search path:

 oid    |  table_name
------- + ------------
975253  |  student
(1 row)

6. Using regclass Cast

PostgreSQL offers a simple and effective way to get the OID of a table using the regclass data type. By using regclass, we don’t have to manually query system catalog tables like pg_class or pg_namespace. Instead, we can directly specify the table name (including its schema, if needed) to retrieve the OID:

SELECT 'public.student'::regclass::oid;

It returns the OID of the student table in the public schema:

 oid    
--------
975253  
(1 row)

The regclass cast is a cleaner and simpler method, especially when working with tables across different schemas

7. Listing OIDs of All Tables

PostgreSQL lets us list the OIDs of all tables using the pg_class system catalog. It stores metadata about all database objects, including tables, indexes, views, etc. The relkind column in pg_class distinguishes between different types of objects. For regular, ordinary tables, the relkind value is ‘r‘. We can query pg_class and filter by relkind = ‘r’ to list the OIDs of only regular tables:

SELECT c.oid, n.nspname AS schema_name, c.relname AS table_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r';

This query returns the OIDs of all tables along with their respective schema and table names:

 oid    |  schema_name  |  table_name
------- + ------------- + ------------
975253  |   public      |  student
975248  |   public      |  department
975258  |   public      |  course
  2619  |   pg_catalog  |  pg_statistics
  1247  |   pg_catalog  |  pg_type
  3118  |   pg_catalog  |  pg_foreign_table
  1260  |   pg_catalog  |  pg_authid
  3429  |   pg_catalog  |  pg_statistic_ext_data
  1418  |   pg_catalog  |  pg_user_mapping
  6100  |   pg_catalog  |  pg_subscription
-- More --

8. Migrating from OID-Dependent Implementations

As PostgreSQL no longer supports OIDs by default, starting from version 12, we need to update our databases to see if they still rely on OIDs. Here’s how we can safely move away from OID-based implementations:

8.1. Check Where OIDs Are Being Used

First, we should identify which tables in our database are still using OIDs. We can run a simple query (as discussed in sections 3 to 7) to list those tables. This helps us see how much of our database depends on OIDs.

8.2. Add Primary Keys Instead of OIDs

If a table uses OIDs but doesn’t have a proper primary key, we can add one by creating an identity column that auto-generates unique values for each row:

ALTER TABLE table_name
ADD COLUMN id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY;

8.3. Update Code

After changing the tables, we also need to review our application code. We should replace OIDs with the new primary key values throughout the code, which keeps everything running smoothly after the changes.

When we upgrade PostgreSQL to version 12 or higher, pg_upgrade might fail if we still have OID-based tables. In such cases, we can use logical replication to move data to the new version. This lets us sync data between the old and new servers without running into upgrade errors.

9. Backup and Replication Considerations

By default, pg_dump doesn’t archive OIDs. In PostgreSQL 11 and earlier, if we relied on OIDs in our data, we had to use the –oids flag to include them in the dump. Otherwise, relationships between objects that depend on OIDs could be lost during backup and restore operations.

Starting from PostgreSQL 12, OIDs in user tables and the –oids flag have been removed entirely.

Since PostgreSQL 10, logical replication doesn’t support OIDs. So if our replication setup depends on row-level OIDs, we need to switch to using primary keys or other unique identifiers instead.

10. Conclusion

In this article, we explored several methods for retrieving the OID of a table in PostgreSQL. OID is an important internal identifier used by the system to track database objects. We covered methods like querying the pg_class system catalog, using pg_catalog.pg_namespace, and applying the regclass cast. Additionally, we learned how to list the OIDs of all tables using the pg_class system catalog.

OID was once used in user-created tables but is now mainly used internally by the database. PostgreSQL provides types like regclass and regtype to refer to objects by name instead of OID. Most users don’t need to work with OIDs directly in daily tasks.