I would like to insert array of unique uuid values in table with uuid column as primary key. Table structure is

CREATE TABLE Organisation (uuid id PRIMARY KEY DEFAULT uuid_generate_v4());

e.g. I have 2 organisations, so I am trying to implement it with query looks like

INSERT INTO Organisation (uuid) VALUES (unnest(array_fill(uuid_generate_v4()::uuid, ARRAY[2]))) RETURNING uuid as uuid_org;

It fails with

ERROR: duplicate key value violates unique constraint "organisation_pkey"

because query fairly tries to insert two duplicated not-unique values, e.g.


My trick works when RETURNING ids or uuids while inserting in tables with not the only column - I pass the uuid column and query automatically generate unique uuids that I can aggregate (see below for table TimeSlice).

Generally, my idea is to insert simultaneously in several tables using WITH:

WITH ins_ts as (INSERT INTO TimeSlice (validTimeBegin) VALUES 
             (unnest(array_fill('2020-01-02'::datetype, ARRAY[2])))
             RETURNING id as id_ts),
     ins_org AS (INSERT INTO Organisation (uuid) ... ---my fail query ),
INSERT INTO OrganisationTimeSlice (idTimeSlice, uuid, name) 
            VALUES (unnest(array(select array_agg(id_ts) from ins_ts)), 
    (unnest(array(select array_agg(uuid_org) from ins_org))),
                     unnest(array['name1', 'name2']));

Where am I wrong?

To clarify why this needs to be an array instead of e.g. explicitly providing two rows like this:

INSERT INTO Organisation (uuid) VALUES (uuid_generate_v4()), (uuid_generate_v4())

This is because I have a variable number of organisations, that I set in ARRAY[number_of_organisations]. Therefore I need to generate a variable number of uuids.


2 Answers 2


N.B.: This was originally suggested by a_horse_with_no_name in a comment.

The solution by fds can be simplified to

select uuid_generate_v4() from generate_series(1,30);

Assuming you don't actually want them in an array, since you are unnesting it right away, here's how to generate a table of any amount of UUIDs:

WITH RECURSIVE genx(n, uu) AS (
  VALUES (1, uuid_generate_v4())
  SELECT n + 1, uuid_generate_v4()
  FROM genx WHERE n < 30
) SELECT uu FROM genx;

Change the 30 to as many as you want!


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.