1

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.

jisna
  • 2,225
  • 2
  • 16
  • 23
  • By *MongoId* you mean the [ObjectId](https://docs.mongodb.com/manual/reference/method/ObjectId/) type in MongoDB? (Some APIs use the name MongoId for that, but please be explicit). If that's the case, are they in their hex format after the migration? – pozs Jan 06 '17 at 10:25
  • yeah its in Hex format. – jisna Jan 06 '17 at 11:09
  • 1
    The closest thing to that in PostgreSQL is the [`uuid` type](https://www.postgresql.org/docs/current/static/datatype-uuid.html). Note that MongoDB's `ObjectId` has only 12 bytes, while UUIDs have 128 bits (16 bytes). But you can convert your existsing IDs by appending (or prepending) f.ex. `'00000000'` to them. You can generate various UUIDs (for default values of the column) with the [`uuid-ossp` module](https://www.postgresql.org/docs/current/static/uuid-ossp.html) – pozs Jan 06 '17 at 12:14
  • @pozs Please post that as an answer; by far the best approach is to use a uuid for this and convert existing columns from varchar to uuid. – Craig Ringer Jan 06 '17 at 13:36

2 Answers2

4

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();
pozs
  • 34,608
  • 5
  • 57
  • 63
0

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.

  • I can't change into varchar since that field is a foreign key and also db is filled with data – jisna Jan 06 '17 at 09:56
  • @CelinVeronicca: then you should change the other columns as well. Storing numbers in `varchar` is columns _is_ a really **bad** idea. It takes up much more space and is slower then using proper numbers –  Jan 06 '17 at 09:58