SQL Postgres OIDs What are they and why are they useful

Question

I am looking at some PostgreSQL table creation and I stumbled upon this   CREATE TABLE         WITH   OIDS   FALSE      I read the documentation provided by postgres and I know the concept of object identifier from OOP but still I do not grasp    why such identifier would be useful in a database  to make  queries shorter  when should it be used

User · Accepted Answer

OIDs basically give you a built-in id for every row, contained in a system column (as opposed to a user-space column). That's handy for tables where you don't have a primary key, have duplicate rows, etc. For example, if you have a table with two identical rows, and you want to delete the oldest of the two, you could do that using the oid column.

OIDs are implemented using 4-byte unsigned integers. They are not unique–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).

In my experience, the feature is generally unused in most postgres-backed applications (probably in part because they're non-standard), and their use is essentially deprecated:

In PostgreSQL 8.1 default_with_oids is off by default; in prior versions of PostgreSQL, it was on by default.

The use of OIDs in user tables is considered deprecated, so most installations should leave this variable disabled. Applications that require OIDs for a particular table should specify WITH OIDS when creating the table. This variable can be enabled for compatibility with old applications that do not follow this behavior.

User · Answer

If you still use OID  it would be better to remove the dependency on it  because in recent versions of Postgres it is no longer supported  This can stop  temporarily until you solve it  your migration from version 10 to 12 for example  See also  https   dev to rafaelbernard postgresql-pgupgrade-from-10-to-12-566i

User · Answer

OIDs being phased out  The core team responsible for Postgres is gradually phasing out OIDs   Postgres 12 removes special behavior of OID columns  The use of OID as an optional system column on your tables is now removed from Postgres 12  You can no longer use    CREATE TABLE     WITH OIDS command default with oids  boolean  compatibility setting   The data type OID remains in Postgres 12  You can explicitly create a column of the type OID    After migrating to Postgres 12  any optionally-defined system column oid will no longer be invisible by default  Performing a SELECT   will now include this column  Note that this extra    surprise    column may break na  vely written SQL code

User · Answer

OID s are still in use for Postgres with large objects  though some people would argue large objects are not generally useful anyway    They are also used extensively by system tables   They are used for instance by TOAST which stores larger than 8KB BYTEA s  etc   off to a separate storage area  transparently  which is used by default by all tables   Their direct use associated with  normal  user tables is basically deprecated      The oid type is currently implemented as an unsigned four-byte integer  Therefore  it is not large enough to provide database-wide uniqueness in large databases  or even in large individual tables  So  using a user-created table s OID column as a primary key is discouraged  OIDs are best used only for references to system tables    Apparently the OID sequence  does  wrap if it exceeds 4B 6   So in essence it s a global counter that can wrap   If it does wrap  some slowdown may start occurring when it s used and  searched  for unique values  etc   See also https   wiki postgresql org wiki FAQ What is an OID 3F

User · Answer

To remove all OIDs from your database tables  you can use this Linux script   First  login as PostgreSQL superuser   sudo su postgres   Now run this script  changing YOUR DATABASE NAME with you database name   for tbl in  psql -qAt -c  select schemaname           tablename from pg tables WHERE schemaname  lt  gt   pg catalog  AND schemaname  lt  gt   information schema    YOUR DATABASE NAME    do  psql -c  alter table  tbl SET WITHOUT OIDS  YOUR DATABASE NAME   done   I used this script to remove all my OIDs  since Npgsql 3 0 doesn t work with this  and it isn t important to PostgreSQL anymore

[sql] SQL, Postgres OIDs, What are they and why are they useful?

Examples related to sql

Examples related to database

Examples related to performance

Examples related to postgresql