CODEWITHSUNDEEP
postgresqlsql-insert
Niki
Niki

Reputation: 39

getting an error as more than one row returned by a subquery used as an expression when trying to insert more than one rows in table

I am trying to insert multiple values into a table from different table in postgresql and encountering an error as [21000]: ERROR: more than one row returned by a subquery used as an expression

INSERT INTO coupon (id,entityid)
values
(select nextval('seq_coupon')),(select entityid from card where country in ('China')));

This query [select entityid from card where country in ('China'))] has multiple rows.

Any help is much appreciated.

Upvotes: 0

Views: 1791

Answers (1)

user330315
user330315

Reputation:

If you want to insert rows that come from a SELECT query, don't use the values clause. The SELECT query you use for the second column's value returns more than one row which is not permitted in places where a single value is required.

To include a constant value for all newly inserted rows, just add it to the SELECT list of the source query.

INSERT INTO coupon (id, entityid, coupon_code)
select nextval('seq_coupon'), entityid, 'A-51'
from card
where country in ('China');

As a side note: when using nextval() there is no need to prefix it with a SELECT, even in the values clause, e.g.

insert into coupon (id, entityid) 
values (nextval('some_seq'), ...);

Upvotes: 0

Related Questions