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.
source to share
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();
source to share
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.
source to share