Continuously insert all unique combinations encountered of three IDs

Question

I want to continuously update a table with all the unique combinations encountered of three different IDs. These IDs can be product/group/region IDs etc. I've abstracted that away here. I want to update this table by calling create_combinations_if_needed('{{1, 10, 100}, {2, 11, 101}}'), which will create the combinations {1, 10, 100} and {1, 10, 100} if they don't already exist.

Here is my attempt at doing that below.

CREATE TABLE combinations (
    id serial PRIMARY KEY,
    some_id1 integer NOT NULL,
    some_id2 integer NOT NULL,
    some_id3 integer NOT NULL
);
CREATE UNIQUE INDEX ON combinations(some_id1, some_id2, some_id3);

CREATE OR REPLACE function create_combinations_if_needed(p_combinations integer[][]) RETURNS boolean
LANGUAGE sql AS
$$
   INSERT INTO combinations (some_id1, some_id2, some_id3)
   SELECT some_id1, some_id2, some_id3
   FROM UNNEST(p_combinations) AS comb(some_id1, some_id3, some_id3)
   ON CONFLICT (some_id1, some_id2, some_id3)
   DO NOTHING
   RETURNING TRUE;
$$;

However, if I try to create this function I get the following error:

ERROR:  table "comb" has 1 columns available but 3 columns specified
CONTEXT:  SQL function "create_combinations_if_needed"

What am I doing wrong?

The two dimensions of the passed array get lost when passing that to `unnest()` you can see that for yourself if you run `select * from unnest('{{1, 10, 100}, {2, 11, 101}}'::int[][]);` — , Jul 30 '19 at 05:48
Thanks. I wasn't sure that this was the issue, but that solved it! Perhaps you have an idea for how to solve this related issue? https://stackoverflow.com/questions/57281162/bulk-updating-postgresql-table-using-lookup-in-another-table — pir, Jul 30 '19 at 23:10

S-Man · Accepted Answer · 2019-07-30T11:53:28.140

As @a_horse_with_no_name already stated: The unnest() function flattens not only the first dimension but all nested elements. So it creates one row per integer. That, of course results in one column with (in your case) six values. This is what the exception message means: You generate one column but three were expected.

So, you need a solution to unnest only the first dimension. I am using the solutions presented here:

https://stackoverflow.com/a/8142998/3984221 from @LukasEklund and @ErwinBrandstetter

demo:db<>fiddle

Creating the Lukas' function:

CREATE OR REPLACE FUNCTION unnest_2d_1d(anyarray)
  RETURNS SETOF anyarray AS
$func$
SELECT array_agg($1[d1][d2])
FROM   generate_subscripts($1,1) d1
    ,  generate_subscripts($1,2) d2
GROUP  BY d1
ORDER  BY d1
$func$  LANGUAGE sql IMMUTABLE;

This one is unnesting only the first dimension. So you can use it instead of your unnest() try within your function:

CREATE OR REPLACE function create_combinations_if_needed(p_combinations integer[][]) RETURNS boolean
LANGUAGE sql AS
$$
   INSERT INTO combinations (some_id1, some_id2, some_id3)
   SELECT unnest[1], unnest[2], unnest[3]
   FROM unnest_2d_1d(p_combinations) as unnest
   ON CONFLICT (some_id1, some_id2, some_id3)
   DO NOTHING
   RETURNING TRUE;
$$;

Thanks, this works great! Perhaps you have an idea for how to solve this related issue? https://stackoverflow.com/questions/57281162/bulk-updating-postgresql-table-using-lookup-in-another-table — pir, Jul 30 '19 at 23:09

Continuously insert all unique combinations encountered of three IDs

1 Answers1

Linked