Postgresql – generate array of unique uuid in postgreSQL

postgresqluuid

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.

{711ec909-fc2c-45aa-8912-39207788768e,711ec909-fc2c-45aa-8912-39207788768e}

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.

Best Answer

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