Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (5k points)

I tried creating some PostgreSQL table but I got stuck on this:




I went through the documentation provided by postgres and I know the concept of object identifier from OOP but I could not get it properly. Why are these kinds of identifiers useful in a database?

1 Answer

0 votes
by (10.2k points)

Whenever you want to have a built-in id for every row, it is provided by OID which is contained in the system column, It is very helpful for the tables we do not have a primary key etc. For instance, if we have a table with two identical rows and want to delete the oldest of the two, we can do that by using an oid column. 

We can implement OIDs using 4-byte unsigned integers. They are not unique–the OID counter will wrap around at 2³²-1. OID are also used to identify data types (see /usr/include/postgresql/server/catalog/pg_type_d.h).

If I tell you my experiences, the feature is mostly unused in most postgres-backed applications. In PostgreSQL 8.1 default_with_oids is off by default; in prior versions of PostgreSQL, it was on by default.

OIDs used in user tables are considered deprecated, so maximum installations should leave this variable disabled. Applications that require OIDs for a particular table must specify WITH OIDS when creating the table. We can enable these variables for compatibility with old applications that have a different approach.

If you want to get more insights into SQL, checkout this SQL Course from Intellipaat.

Browse Categories