intro
Let’s learn everything you need to know about the PostgreSQL oid data type, the numeric object identifier used by PostgreSQL databases.
If you have ever worked with MongoDB, you are probably familiar with the concept of object identifiers (OIDs). But did you know that PostgreSQL supports OIDs as well? In PostgreSQL, OIDs are not hashed strings like in MongoDB, but they serve the same purpose of uniquely identifying objects within the database.
In this guide, we will walk you through how the PostgreSQL oid data type works and where it plays a key role. Let’s dive in!
What Is OID in PostgreSQL?
In PostgreSQL, the oid data type represents globally unique object identifiers across the entire Postgres server. These "object identifiers" (commonly referred to as “OIDs” in IT jargon, which is why the data type was called with that name) are used internally by PostgreSQL and serve as primary keys for various system catalog tables, such as pg_attribute:
In this case, the attrelid column in the pg_attribute system table is of type oid, as shown in its SQL DDL definition retrieved via the DbVisualizer client. Now, take a look at the data in pg_attribute:
You will notice that the value in attrelid is not unique across rows. Shouldn’t OIDs be unique across the entire server?
The reason is that attrelid is a sort of foreign key reference to the oid column in the pg_class table—where oid acts as the primary key. Because of that, multiple rows in system catalog tables can share the same attrelid value, since each row represents a different column of the same table.
Specifically, OIDs in PostgreSQL are implemented as unsigned 4-byte integers. This means that the OID counter can contain around 2³² - 1 (~4.29 billion) values. As a result, OIDs are not guaranteed to remain globally unique over the lifetime of a large database, or even within a single large table over time.
That limitation is one reason why OIDs are used primarily for internal purposes in PostgreSQL. Another limitation is that the oid data type only supports basic comparisons. However, it can be cast to an integer for use with standard operators. At the same time, you should be careful when doing so due to potential signed vs. unsigned interpretation issues.
The History of OIDs in PostgreSQL
In PostgreSQL, OIDs have evolved significantly over time. Initially, they were mandatory for all rows in user tables up until PostgreSQL 7.2, providing a unique identifier for each row.
From PostgreSQL 8.1, the default behavior changed, and OIDs were disabled by default for user tables. Prior to that, OIDs were included by default, offering the advantage of distinguishing otherwise identical rows, but they had limitations, including potential counter wraparound due to their 32-bit capacity.
Between PostgreSQL 8.1 and 11, users could still enable OIDs for specific tables using the WITH OIDS option or by setting the default_with_oids parameter. If enabled, OIDs were displayed after an INSERT and stored in the LASTOID variable in psql.
However, starting with PostgreSQL 12, system-generated OIDs are no longer supported for user tables, and they have become regular columns in system catalog tables. In PostgreSQL versions before 11, OIDs in system catalog tables were "hidden" and required explicit selection, as they were handled behind the scenes by the system.
Overall, the use of OIDs in user tables is considered basically deprecated and is generally avoided in modern applications. However, understanding how the PostgreSQL oid data type works is still important, as it continues to play a key role in nearly all system catalog tables.
PostgreSQL OID Data Type: Syntax and Usage
The oid data type in PostgreSQL works like any other built-in type. In particular, you can define an oid column in a CREATE TABLE statement like so:
1
CREATE TABLE example (
2
id oid
3
-- other columns...
4
);
Note that oid does not accept modifiers such as length or precision. When viewed in a PostgreSQL database client, an oid column typically appears as an unsigned integer:
As mentioned earlier, the oid type supports very few operations directly. However, PostgreSQL provides a set of useful alias types to simplify working with OIDs. Let's learn more about them!
Object Identifier Aliases in PostgreSQL
In short, the oid PostgreSQL data type is a 4-byte unsigned integer used internally to uniquely identify database objects like tables, functions, and data types in system catalogs.
To make working with OIDs more human-readable and convenient, Postgres provides a set of OID alias types—such as regclass, regtype, and regprocedure. These allow you to reference system objects by name, instead of manually retrieving their numeric OID values.
For example, consider the following query:
1
SELECT * FROM pg_attribute
2
WHERE attrelid = 'my_table'::regclass;
This is functionally equivalent to retrieving the numeric OID of my_table from the pg_class system table using a subquery:
1
SELECT * FROM pg_attribute
2
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'my_table');
As you can tell, the use of a PostgreSQL OID alias makes the query much easier to write and understand.
Each OID alias maps to a specific system catalog table and is used for a particular type of database object:
| Name | References | Description | Example |
|---|---|---|---|
oid | any | Numeric object identifier | 564182 |
regclass | pg_class | Relation (table, index, etc.) name | 'pg_type'::regclass |
regcollation | pg_collation | Collation name | '``POSIX``'``::::regcollation |
regconfig | pg_ts_config | Text search configuration | 'english'::regconfig |
regdictionary | pg_ts_dict | Text search dictionary | 'simple'::regdictionary |
regnamespace | pg_namespace | Namespace (schema) name | 'pg_catalog'::regnamespace |
regoper | pg_operator | Operator name (not overloaded) | '+'::regoper |
regoperator | pg_operator | Operator with argument types | '* (integer, integer)'::regoperator or '- (NONE, integer)'::regoperator |
regproc | pg_proc | Function name (not overloaded) | 'sum'::regproc |
regprocedure | pg_proc | Function with argument types | 'sum(int4)'::regprocedure |
regrole | pg_authid | Role or user name | 'admin'::regrole |
regtype | pg_type | Data type name | 'integer'::regtype |
OID aliases simplify OID usage, reducing the need for SQL subqueries and making your queries on system tables easier to write and read.
Conclusion
In this guide, you learned about the PostgreSQL OID data type, including how it works and where it is used in a Postgres environment. As demonstrated, DbVisualizer makes managing data visually and intuitively easy, whether you are working with user or system tables. Other key features include SQL formatting, ERD-style schema diagrams, and query optimization tools. Download DbVisualizer for free today!
FAQ
What is the difference between a PostgreSQL sequence ID and an OID?
A PostgreSQL sequence ID is an auto-incrementing number typically used as a primary key in tables. It is managed using SERIAL data type and is specific to a table. On the contrary, an OID is a 4-byte value generally used internally by PostgreSQL to uniquely identify database objects in system catalogs.
Is the OID mechanism in PostgreSQL deprecated?
The use of OIDs in PostgreSQL system catalogs is still fully supported and not deprecated. However, the automatic assignment of OIDs to rows in user-defined tables is deprecated and disabled by default starting with PostgreSQL 8.1.
Are system-generated OIDs still supported in PostgreSQL for user tables?
System-generated OIDs for user tables are no longer enabled by default in PostgreSQL. As of PostgreSQL 8.1, OIDs are no longer added to user tables by default. If you want to include them, you must explicitly specify it in the table definition. Yet, that practice is strongly discouraged, especially for primary keys. The reason is that OIDs are limited to a 4-byte space, allowing only about 4.3 billion unique values, making them unsuitable for large-scale or long-lived datasets.
Are the OIDs used in PostgreSQL the same as the ones used in MongoDB?
No, PostgreSQL and MongoDB use fundamentally different OID implementations. In MongoDB, an OID is a 12-byte hexadecimal string generated based on time, machine, and process ID, designed for globally unique identification. On the other hand, PostgreSQL’s OIDs are 4-byte integers assigned by the system to uniquely identify database objects like tables or functions internally.
Why use a visual database client?
A visual database client like DbVisualizer simplifies working with databases, including managing system views and catalog tables. It provides an intuitive interface for managing tables, exploring schema relationships, and writing/debugging queries with a powerful SQL editor. Features like autocomplete, ER diagrams, and data export tools set it apart. Try DbVisualizer Pro with a 21-day free trial!
