POSTGRESQL: auto-increment for a varchar field

I'm moving from MongoDB to PostgreSQL and wondering how I can implement the same concept as MongoDB to uniquely identify each raw MongoId.

After migration, existing unique fields in our database are saved as a symbol. I am looking for minimal changes to the source code.

So if any way exists in postgresql to generate auto-incrementing unique id for every insert into table.

-1


source to share


2 answers


The closest thing to MongoDB ObjectId in PostgreSQL is the uuid

type
. Note that ObjectId is only 12 bytes while UUIDs are 128 bits (16 bytes).

You can convert your existing IDs by adding (or adding) f.ex. '00000000'

to them.

alter table some_table
    alter id_column
    type uuid
    using (id_column || '00000000')::uuid;

      



Although it would be better if you could do this by migrating schema + data. If you cannot do this during migration, you need to update your ids (while they are still varchar

s: this way the referenced columns will propagate this change), dump foreign keys, do alter type

, and then re-foreign keys.

You can create different UUIDs (for column defaults) with uuid-ossp

module
.

create extension "uuid-ossp";

alter table some_table
    alter id_column
    set default uuid_generate_v4();

      

+1


source


Use default sequence for column:

create sequence some_id_sequence 
  start with 100000
  owned by some_table.id_column;

      

start with

must be greater than your current maximum number.

Then use this default sequence for your column:



alter table some_table
   alter id_column set default nextval('some_id_sequence')::text;

      


Better solution would be to change the column to an integer column. Storing numbers in a column text

(or varchar

) is a really bad idea.

0


source







All Articles