Canonical replacement for OIDs in PostgreSQL 12 onwards

All we need is an easy explanation of the problem, so here it is.

I have an application which relies totally on PostgreSQL OIDs for its operation. It’s currently at PostgreSQL 9.6.1, but I’m looking to upgrade to 13. Is there a recommended/standard mechanism (using triggers?) to recreate the OID functionality in newer versions?

For background, the reason the application uses OIDs is because the app knows nothing about the database schema and is intended to be used on any random database schema, using reverse engineering to navigate. For this reason, a suggestion along the lines of "just add a primary key" won’t work for me. Currently the app is running against 6 separate databases, each with its own schema.

Additional background

Practically every application understands its database schema, for example a payroll application understands the employee table, which perhaps has a primary key of emp_id.

My app is different.

My app automatically generates a CRUD application for any random PostgreSQL database that it is pointed at. As such, it makes no assumptions about primary keys, table names, column names, etc etc. The only assumption it makes is that the database it has been pointed at has a unique OID for each row in each table. As things currently stand, I use this application against five separate and distinct databases, and have done so since around 2007.

So I get that, in the absence of a system generated OID, I probably need to create a user column, called OID defined as a sequence. The purpose of the question is to ask if there is a canonical solution for apps that use the pre-v12 OID. Assume, that the application cannot be changed, so any solution must be entirely implemented within the database.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can simply add a new column called oid that is automatically filled:

ALTER TABLE test ADD oid bigint GENERATED ALWAYS AS IDENTITY NOT NULL;

Using an identity column like this has the advantage that you get an error if you by mistake try to INSERT a value into the column manually.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply