Postgresql multi insert from array of composite type and one additional column

Question

What I have:

CREATE TYPE Item AS (
    a bigint,
    b bigint
);

CREATE TABLE items (
    id bigint NOT NULL,
    a bigint NOT NULL,
    b bigint NOT NULL
);

CREATE OR REPLACE FUNCTION items_insert(
        _id bigint,
        _items Item[]
) RETURNS void AS
...

How can I insert multi ~~columns~~ rows to table items with same _id from _items by ONE multi insert query?

I am using Postgresql-9.2

Unrelated to your problem, but: Postgres 9.2 is no longer supported you should plan an upgrade as soon as possible. — user330315
– user330315, Commented Mar 22, 2021 at 16:12

user330315 · Accepted Answer · 2021-03-23 07:12:23Z

3

I think you mean "insert multiple rows" rather than columns.

Assuming that is correct, I think you are looking for a function like this:

create or replace function items_insert(_id bigint, _items item[]) 
  returns void 
as
$$
  insert into items (id, a, b)
  select _id, it.*
  from unnest(_items) as it(a,b);
$$
language sql;

Online example

answered Mar 23, 2021 at 7:12

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

Konstantin Primenko Over a year ago

Thanks for advance. I found shorter way: INSERT INTO items(id, a, b) SELECT _id, (unnest(_items)).*;

user330315 Over a year ago

@КонстантинПрименко: using unnest() in the SELECT list is not recommended. It's better to use it in the FROM clause

Konstantin Primenko Over a year ago

Why using unnest() in the SELECT list is not recommended?

Collectives™ on Stack Overflow

Postgresql multi insert from array of composite type and one additional column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related