ERROR: more than one row returned by a subquery used as an expression - POSTGRES

Question

I'm having an issue while inserting a data to postgres table..

Here is my query:

INSERT INTO products (product_id, user_id) 
SELECT id, (SELECT id FROM users WHERE c_id = _c_id) FROM product_badge WHERE comp_id = _comp_id;

Issue I'm receiving is

ERROR: more than one row returned by a subquery used as an expression CONTEXT

Thanks in advance

What are _c_id and _comp_id? What are you trying to achieve? You should give more context to your question, for example by providing sample data and expected results. — GMB
– GMB, Commented Feb 17, 2020 at 15:32
What part of the error do you not understand? Sample data and desired results would help figure out what to do. — Gordon Linoff
– Gordon Linoff, Commented Feb 17, 2020 at 15:32
Before inserting all combination be sure of the functional impact and the business implications. The technically working solution doesn't mean that it is the right thing. — zip
– zip, Commented Feb 17, 2020 at 15:37

Gordon Linoff · Accepted Answer · 2020-02-17 15:32:51Z

1

You can first the problem with limit:

INSERT INTO products (product_id, user_id) 
    SELECT id, (SELECT u.id FROM users u WHERE u.c_id = _c_id LIMIT 1) 
    FROM product_badge
    WHERE comp_id = _comp_id;

However, that is a work-around. Normally in this case, you might want all combinations, and that suggests a JOIN:

INSERT INTO products (product_id, user_id) 
    SELECT pb.id, u.id
    FROM product_badge pb CROSS JOIN
         users u
    WHERE pb.comp_id = _comp_id AND
          u.c_id = _c_id;

answered Feb 17, 2020 at 15:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

MateuszRek Over a year ago

This would add/take same user_id for each row im inserting?

Gordon Linoff Over a year ago

@MateuszRek . . . The first would take an arbitrary matching user id, but probably the same one. The second would insert rows for all user ids.

zip · Accepted Answer · 2020-02-17 15:32:43Z

0

MateuszRek,

As long as

SELECT id FROM product_badge inner join users WHERE c_id = _c_i

returns more than one row you will have a problem. Using a subquery as selected field implies the engine get the result as the value of that field. having more rows poses problem as the engine can pick a value

You can us limit to pick one value. However, even if that solves the technical part of the problem,it might leave a functional/business one because maybe something is missing, for example, for to choose that id. using limit doesnt garantee its the right value

answered Feb 17, 2020 at 15:32

zip

4,0612 gold badges13 silver badges22 bronze badges

Collectives™ on Stack Overflow

ERROR: more than one row returned by a subquery used as an expression - POSTGRES

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related