13
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?

bartolo-otrit
  • 2,396
  • 3
  • 32
  • 50
  • This looks like a bug to me. Try `SELECT random() AS one, random() AS two;`. I get two different values, whether or not I'm in a transaction. `uuid_generate_v4` is a `VOLATILE` function, so I think the fault lies either in the uuid-ossp module, or the underlying library. – Joey Adams Aug 05 '11 at 06:36
  • 1
    Yes "SELECT random() AS one, random() AS two" get different values and i don't understand why uuid_generate_v4 get equal values – bartolo-otrit Aug 05 '11 at 12:52

3 Answers3

17

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

Peter Eisentraut
  • 35,221
  • 12
  • 85
  • 90
  • 1
    I replaced IMMUTABLE for VOLATILE in `uuid-ossp.sql` `select uuid_generate_v1() as one, uuid_generate_v4() as two` gives two different results but `insert into "TB" ("Id", "Title") values (uuid_generate_v4(), '111'); insert into "TB" ("Id", "Title") values (uuid_generate_v4(), '111'); ` continues to give "duplicate key" error – bartolo-otrit Aug 08 '11 at 06:25
  • 1
    The table definition you show above doesn't even have a unique constraint, so you are not telling us the full truth. – Peter Eisentraut Aug 08 '11 at 10:37
  • I'm sorry, post updated `CONSTRAINT "TB_Class_ID" PRIMARY KEY ("Id")` – bartolo-otrit Aug 08 '11 at 12:18
13

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:

  1. Make separate database calls every time you use the function (this is easiest)
  2. 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)
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 6
    Just FYI for everyone who came here from Google or whatever, but I don't think this is a bug or issue anymore. "select uuid_generate_v4() as one, uuid_generate_v4() as two" works properly in my PostgreSQL 9.3/9.4 installs. Like @peter-eisentraut said, it was probably a bug that was fixed a while back. – The Alchemist Jul 31 '15 at 16:18
4

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?

Puzirki
  • 442
  • 5
  • 16