Array Insertion Postgres

Question

I have a table item with attributes no(integer) and price(integer), also another table cart with attributes no(integer) and items(array of item).

I have some records in items.

When i tried :

INSERT INTO myschema.cart VALUES(1,'{SELECT item from myschema.item}')

I'am getting error malformed record literal.

I expected this to insert all items from myschema.item into the cart record.

Phill · Accepted Answer · 2017-06-06 06:37:20Z

1

It's hard to give you exact statement without the table structures and such, but you can select into an array:

INSERT INTO myschema.cart (id, item_ids)
SELECT 1, array(SELECT id from myschema.item)

This will select the id's from the item table into an array.

You can test it out by writing:

select array(SELECT id from myschema.item)

answered Jun 6, 2017 at 6:37

Phill

18.9k7 gold badges66 silver badges104 bronze badges

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

Comments

Sevanteri · Accepted Answer · 2017-06-06 06:42:26Z

0

You can't write a subquery inside a string like that.

What you need to do is aggregate the items into a array with array_agg

INSERT INTO myschema.cart
VALUES (1, (SELECT array_agg(item) FROM myschema.item));

Or

INSERT INTO myschema.cart
SELECT 1, array_agg(item) FROM myschema.item;

answered Jun 6, 2017 at 6:36

Sevanteri

4,0981 gold badge26 silver badges30 bronze badges