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?

Improve this question
4
  • 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. Commented Aug 5, 2011 at 6: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 Commented Aug 5, 2011 at 12:52
  • Update: Postgres 18+ offers uuidv7 & uuidv4 functions built-in. No need for uuid-ossp extension. You’ll want Version 7 UUIDs for indexing. See my Answer on related Question. Commented Oct 9 at 23:33
  • By the way a tip: To avoid naming collisions, I suggest appending a trailing underscore on all your names rather than surrounding with double-quotes. The SQL spec explicitly promises to never use a trailing underscore, so you are guaranteed no naming collisions. Commented Oct 9 at 23:36

3 Answers 3

Reset to default
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.)

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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
The table definition you show above doesn't even have a unique constraint, so you are not telling us the full truth.
I'm sorry, post updated CONSTRAINT "TB_Class_ID" PRIMARY KEY ("Id")
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)
Improve this answer

1 Comment

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

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?

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.