Error:More Than One Row Returned by a Subquery Used as an Expression

Postgres Error: More than one row returned by a subquery used as an expression

Technically, to repair your statement, you can add LIMIT 1 to the subquery to ensure that at most 1 row is returned. That would remove the error, your code would still be nonsense.

... 'SELECT store_key FROM store LIMIT 1' ...

Practically, you want to match rows somehow instead of picking an arbitrary row from the remote table store to update every row of your local table customer.

Your rudimentary question doesn't provide enough details, so I am assuming a text column match_name in both tables (and UNIQUE in store) for the sake of this example:

... 'SELECT store_key FROM store
     WHERE match_name = ' || quote_literal(customer.match_name)  ...

But that's an extremely expensive way of doing things.

Ideally, you completely rewrite the statement.

UPDATE customer c
SET    customer_id = s.store_key
FROM   dblink('port=5432, dbname=SERVER1 user=postgres password=309245'
            , 'SELECT match_name, store_key FROM store')
       AS s(match_name text, store_key integer)
WHERE c.match_name = s.match_name
AND   c.customer_id IS DISTINCT FROM s.store_key;

This remedies a number of problems in your original statement.

Obviously, the basic problem leading to your error is fixed.

It's typically better to join in additional relations in the FROM clause of an UPDATE statement than to run correlated subqueries for every individual row.

When using dblink, the above becomes a thousand times more important. You do not want to call dblink() for every single row, that's extremely expensive. Call it once to retrieve all rows you need.

With correlated subqueries, if no row is found in the subquery, the column gets updated to NULL, which is almost always not what you want. In my updated query, the row only gets updated if a matching row is found. Else, the row is not touched.

Normally, you wouldn't want to update rows, when nothing actually changes. That's expensively doing nothing (but still produces dead rows). The last expression in the WHERE clause prevents such empty updates:

     AND   c.customer_id IS DISTINCT FROM sub.store_key

Related:

• How do I (or can I) SELECT DISTINCT on multiple columns?

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

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'), ...);

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

Real Culprit is this line in your code

(SELECT username FROM main.users WHERE main.users.user_id = user_id)

Try it like This:

INSERT INTO comments (text, post_id, user_id)
VALUES('sample', 11, 1)
RETURNING 
comment_id,
text,
post_id,
(SELECT username FROM users t1 WHERE t1.user_id = comments.user_id) AS username,
created_at,
updated_at

DEMO:

I have removed the schema name for clarity

PostgreSQL more than one row returned by a subquery used as an expression

Use an aggregation function, such as string_agg() or json_agg():

SELECT name,
       (SELECT string_agg(car_name) FROM cars WHERE user = id)
FROM users
WHERE user_id = 1
ORDER BY name;

SQL Error [21000]: ERROR: more than one row returned by a subquery used as an expression

The sub select is not needed (and will return more than one row, hence the error). I think you can simplify like this:

select
    v."name" as "name",
    string_agg(t."name", ', ' order by vt."videoId") as "tags"
from
    video v
join "videoTag" vt on
    vt."videoId" = v.id
join tag t on
    t.id = vt."tagId"
group by v."name";

Postgres more than one row returned by a subquery used as an expression

Instead of trying to LIKE multiple names, it could LIKE ANY of the names.

select * 
from "History" 
where "Message" LIKE ANY (
     select '%'||"Name"||'%'
     from  public."Campaign"
     where "Employ" IN (
           (select "Employ" from public."Sub" 
            where "Company" ilike '%XX%' limit 1) 
           union all
           (select "Employ" from public."Sub"
            where "Company" not ilike '%XX%' limit 1)
     ) 
);

---

Related Topics