How to insert array of complex types into a table

Question

I have a complex type defined as:

create type TP_IdAndVer AS
(
    id  uuid,
    ver integer
);

And I'd like to insert an array of them into a temporary table (so I can join on a composite key).

This is what I have so far but it doesn't work:

DO $$

declare fred TP_IdAndVer[];

BEGIN

drop table if exists tmpTable;

fred := array[ ('034892e4-6137-440c-bb62-b609b60575aa'::uuid, 1), ('034892e4-6137-440c-bb62-b609b60575aa'::uuid, 2) ];

create temporary table temptbl_ids_and_vers(id uuid, ver integer)
on commit drop;

-- I want to insert each row of fred as a row into my temp table. But how??
insert into temptbl_ids_and_vers(id, ver) values (fred);

CREATE TEMPORARY TABLE tmpTable AS
select * from temptbl_ids_and_vers;

END $$;

select  *
from    tmpTable;

I get this error:

ERROR:  INSERT has more target columns than expressions
LINE 1: insert into temptbl_ids_and_vers(id, ver) values (fred)
                                             ^
QUERY:  insert into temptbl_ids_and_vers(id, ver) values (fred)
CONTEXT:  PL/pgSQL function inline_code_block line 15 at SQL statement

********** Error **********

ERROR: INSERT has more target columns than expressions
SQL state: 42601
Context: PL/pgSQL function inline_code_block line 15 at SQL statement

If someone could point me to the right syntax to use it would be a big help. I guess I could loop through each element of the array inserting each row individually, but that feels a bit hacky.

Alternatively, is there a way of passing a table to a stored proc? This would get around what I'm trying to achieve at the moment since I could just a pass a table in without needing to worry about arrays.

Thanks,

Adam.

Try unnest(), see: postgresql.org/docs/9.4/static/functions-array.html — Usagi Miyamoto
– Usagi Miyamoto, Commented Aug 16, 2017 at 15:21
@UsagiMiyamoto: Thanks for the response. I tried insert into temptbl_ids_and_vers(id, ver) values (unnest(fred)); but still get an error: ERROR: INSERT has more target columns than expressions — Adam Benson
– Adam Benson, Commented Aug 16, 2017 at 15:37
Try this: INSERT INTO temptbl_ids_and_vers(id, ver) SELECT UNNEST(fred); — Usagi Miyamoto
– Usagi Miyamoto, Commented Aug 16, 2017 at 15:43
Pretty much the same. Tried: insert into temptbl_ids_and_vers(id, ver) select unnest(fred); And got: ERROR: INSERT has more target columns than expressions. Thanks anyhow :-) — Adam Benson
– Adam Benson, Commented Aug 16, 2017 at 15:50

Usagi Miyamoto · Accepted Answer · 2017-08-17 03:25:33Z

1

INSERT INTO temptbl_ids_and_vers(id, ver)
SELECT *
FROM UNNEST(fred) AS q(id, ver)

answered Aug 17, 2017 at 3:25

Usagi Miyamoto

6,3471 gold badge21 silver badges37 bronze badges

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

1 Comment

Adam Benson Over a year ago

You got me there in the end! <Blush> Thanks Usagi.

Clodoaldo Neto · Accepted Answer · 2017-08-16 16:21:04Z

0

insert into temptbl_ids_and_vers(id, ver)
select id, ver
from unnest(fred) u

answered Aug 16, 2017 at 16:21

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

1 Comment

Adam Benson Over a year ago

Thanks - Usagi was trying to get me there. I was just a little slow to catch on!

Collectives™ on Stack Overflow

How to insert array of complex types into a table

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related