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 I am wrong?

  • Why the array_fill and unnest? Why can't you simply use INSERT INTO Organisation (uuid) VALUES (uuid_generate_v4()), (uuid_generate_v4()) to insert two rows? – a_horse_with_no_name Jan 29 '20 at 13:54
  • @a_horse_with_no_name, I have a variable number of organisations, that I set in ARRAY[number_of_organisations] – Jane Jan 29 '20 at 13:56
  • But the array_fill() thing will always repeat the same value, sounds like a really strange thing to do – a_horse_with_no_name Jan 29 '20 at 14:07
  • I would probably try something like this: dpaste.com/1MZ5JTX – a_horse_with_no_name Jan 29 '20 at 14:10
  • 1
    Something like this then maybe? dpaste.com/1A9WH01 I still don't understand what exactly you are trying to achieve. – a_horse_with_no_name Jan 29 '20 at 14:38

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!

  • 1
    That can be simplified to select uuid_generate_v4() from generate_series(1,30) – a_horse_with_no_name Jan 29 '20 at 15:17
  • Oh that's neat! Much simpler. – fds Jan 29 '20 at 15:21
  • @a_horse_with_no_name, great! Thank you! I need more experience... If you would like post it as answer I accept it – Jane Jan 29 '20 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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