POSTGRESQL：varchar类型字段的自动递增 [英] POSTGRESQL:autoincrement for varchar type field

问题描述

我正在从MongoDB切换到PostgreSQL，并且想知道如何实现与MongoDB中相同的概念，以通过MongoId唯一标识每个原始数据。

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.

因此，如果在postgresql中存在用于为每次插入表生成自动增量唯一ID的方式。

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

推荐答案

与 MongoDB的ObjectId 最接近的东西在PostgreSQL中是 uuid 类型。请注意，ObjectId只有12个字节，而UUID只有128位（16个字节）。

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).

您可以通过附加（或前置）f.ex来转换现有的ID。 '00000000'给他们。

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;

尽管最好的是在迁移架构+数据时这样做。如果在迁移期间无法执行此操作，则需要更新ID（当它们仍为 varchar s时：通过这种方式，引用的列将传播更改），外键，请执行 alter类型，然后重新应用外键。

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.

您可以生成各种UUID（用于列的默认值）与 uuid-

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();

这篇关于POSTGRESQL：varchar类型字段的自动递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！