postgreSQL uuid generation
select uuid_generate_v4() as one, uuid_generate_v4() as two;
"one" uuid and "two" uuid are equal!
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
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
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;
I understand that maestro added records one by one, but why uuid_generate_v4() returns the same value after two calls? (In pgAdmin case).
And how can I add several rows by one request?
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
.)
Within a given transaction, the function uuid_generate_v4()
returns the same value.
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.
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; COMMIT
pair (this is a hassle - don't do this unless you have to)
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?
精彩评论