How to automatically generate Oids on postgres versions over 12

user_14446647 0
Viewed 583

I am currently in the process of migrating my team's database from postgres 10 to postgres 13. My team is designing a java based application that is built on postgres so we don't want to migrate the database but just edit the code such that our program is compatible with postgres 13. Is there any command to automatically generate oids? Here is our current code which is compatible with postgres 10 but not 13

        String createTable = "CREATE TABLE IF NOT EXISTS tableorders (" +
                "tablename    TEXT NOT NULL," +
                "ordername TEXT NOT NULL," +
                "rowIdxTable TEXT, " +
                "colIdxTable TEXT, " +
                "PRIMARY KEY (tablename, ordername)" +
                "UNIQUE (oid))" +
        //postgres13 does not support this line
                "WITH oids";
                ") ";

So what I wish to do is find a way to automatically generate an identification for each node that is unique on postgres and eliminate the oid which is making our program incompatible with postgres 13

ddl postgresql
1 Answers

You should use an identity column of type bigint called oid, and your program will keep working like before.

If you want database-wide uniqueness, that is something that the recently removed oids did not provide. These numbers were taken from a 4 byte unsigned counter that wrapped around after 2^32 (although PostgreSQL avoided duplicate numbers within a single table if the column had a unique index).

Database-wide uniqueness of an oid column can easily be achieved by using a single sequence to provide values for all such columns.

user_64643080
Related