CODEWITHSUNDEEP
postgresql
jisna
jisna

Reputation: 2444

POSTGRESQL:autoincrement for varchar type field

I'm switching from MongoDB to PostgreSQL and was wondering how I can implement the same concept as used in MongoDB for uniquely identifying each raws by MongoId.

After migration, the already existing unique fields in our database is saved as character type. I am looking for minimum source code changes.

So if any way exist in postgresql for generating auto increment unique Id for each inserting into table.

Upvotes: 1

Views: 3232

Answers (2)

pozs
pozs

Reputation: 36254

The closest thing to MongoDB's ObjectId in PostgreSQL is the uuid type. Note that ObjectId has only 12 bytes, while UUIDs have 128 bits (16 bytes).

You can convert your existsing IDs by appending (or prepending) f.ex. '00000000' to them.

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

Although it would be the best if you can do this while migrating the schema + data. If you can't do it during the migration, you need to update you IDs (while they are still varchars: this way the referenced columns will propagate the change), drop foreign keys, do the alter type and then re-apply foreign keys.

You can generate various UUIDs (for default values of the column) with the uuid-ossp module.

create extension "uuid-ossp";

alter table some_table
    alter id_column
    set default uuid_generate_v4();

Upvotes: 4

user330315
user330315

Reputation:

Use a sequence as a default for the column: 

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

The start with should be bigger then your current maximum number.

Then use that sequence as a default for your column:

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

The better solution would be to change the column to an integer column. Storing numbers in a text (or varchar) column is a really bad idea.

Upvotes: 0

Related Questions