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