postgreSQL uuid generation

user_704244 0
Viewed 35735
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?

postgresql sql database
3 Answers

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?

user_9401570
Related