postgreSQL uuid 生成
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6939628/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgreSQL uuid generation
提问by bartolo-otrit
select uuid_generate_v4() as one, uuid_generate_v4() as two;
"one" uuid and "two" uuid are equal!
“一个”uuid 和“两个”uuid 是相等的！
CREATE TABLE "TB"
(
"Id" uuid NOT NULL DEFAULT uuid_generate_v4(),
"Title" character varying NOT NULL,
CONSTRAINT "TB_Class_ID" PRIMARY KEY ("Id")
);
postgresql 9.0 pgAdmin 1.12.3
PostgreSQL 9.0 pgAdmin 1.12.3
insert into "TB" ("Id", "Title") values (uuid_generate_v4(), '111');
insert into "TB" ("Id", "Title") values (uuid_generate_v4(), '111');
insert into "TB" ("Id", "Title") values (uuid_generate_v4(), '111');
or
或者
insert into "TB" ("Title") values ('111');
insert into "TB" ("Title") values ('111');
insert into "TB" ("Title") values ('111');
result:
结果：
ERROR: duplicate key value violates unique constraint "TB_Class_ID"
DETAIL: Key ("Id")=(12ab6634-995a-4688-9a9a-ee8c3fe24395) already exists.
whereas
然而
postgreSQL maestro 9.2.0.4
postgreSQL 大师 9.2.0.4
insert into "TB" ("Id", "Title") values (uuid_generate_v4(), '111');
insert into "TB" ("Id", "Title") values (uuid_generate_v4(), '111');
insert into "TB" ("Id", "Title") values (uuid_generate_v4(), '111');
result: 1 rows affected;
结果：1 行受影响；
I understand that maestro added records one by one, but why uuid_generate_v4() returns the same value after two calls? (In pgAdmin case).
我明白大师是一一添加记录的，但是为什么 uuid_generate_v4() 两次调用后返回相同的值？（在 pgAdmin 的情况下）。
And how can I add several rows by one request?
如何通过一个请求添加多行？
回答by Peter Eisentraut
At some point in the past, the uuid_generate_*functions were erroneously marked as IMMUTABLE, which would result in the behavior you show. This has been fixed in all the latest minor versions, but you have to re-run the installation script (uuid-ossp.sql) to get the updated function definitions. (You can also look into the installation script to verify that you have an up-to-date version. The functions should be marked VOLATILE.)
在过去的某个时候，uuid_generate_*函数被错误地标记为IMMUTABLE，这会导致您显示的行为。这已在所有最新的次要版本中得到修复，但您必须重新运行安装脚本 ( uuid-ossp.sql) 以获取更新的函数定义。（您还可以查看安装脚本以验证您是否拥有最新版本。这些功能应标记为VOLATILE。）
回答by Bohemian
Within a given transaction, the function uuid_generate_v4()returns the same value.
在给定的事务中，该函数uuid_generate_v4()返回相同的值。
When statements are grouped together and run as "one command", there is one transaction, so every call to uuid_generate_v4()will return the same value.
当语句组合在一起并作为“一个命令”运行时，存在一个事务，因此每次调用uuid_generate_v4()都会返回相同的值。
The two ways to "fix" this are:
“修复”这个问题的两种方法是：
- Make separate database calls every time you use the function (this is easiest)
- Use a non-auto commit connection where you control the transactions and separate each usage within a
BEGIN; COMMITpair (this is a hassle - don't do this unless you have to)
- 每次使用该函数时都进行单独的数据库调用（这是最简单的）
- 使用非自动提交连接，您可以在其中控制事务并将每个使用分开
BEGIN; COMMIT成对（这很麻烦 - 除非必须，否则不要这样做）
回答by Puzirki
To avoid duplicates you can use generation like this:
为避免重复，您可以像这样使用生成：
select md5(random()::text || clock_timestamp()::text)::uuid AS new_id, id from table;
But, be careful: this generates UUID but it is not UUIDv4. See more: Generating a UUID in Postgres for Insert statement?
但是，请注意：这会生成 UUID，但它不是 UUIDv4。查看更多：在 Postgres 中为 Insert 语句生成 UUID？
回答by bartolo-otrit
begin ISOLATION LEVEL READ UNCOMMITTED;
insert into "TB" ("Title") values ('111');
insert into "TB" ("Title") values ('111');
insert into "TB" ("Title") values ('111');
commit;
it's possible too
也有可能
