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)
)
);
More than one row returned by a subquery used as an expression but only in function
If you want your function to return an array of report objects as your query does, then change this part:
select row_to_json(full_data) from full_data
To aggregate the rows into an array:
select json_agg(row_to_json(full_data)) from full_data
If you want the function to return multiple rows, then you need to make two changes:
CREATE OR REPLACE FUNCTION report(arguments json)
RETURNS table (row_to_json json) AS
And for the return
:
RETURN QUERY (
WITH full_data as (
Updated fiddle here.
More than one row returned by a subquery used as an expressin
use EXISTS (subquery)
to determine if a subquery returns at least one row. It returns a boolean, so you can put it directly after the WHEN
.
Related Topics
Rodbc Temporary Table Issue When Connecting to Ms SQL Server
How to Turn on Regexp in SQLite3 and Rails 3.1
SQL Server (Tsql) - How to Exec Statements in Parallel
What Is the Mysterious 'Timestamp' Datatype in Sybase
Window Functions or Common Table Expressions: Count Previous Rows Within Range
What Is the Equivalent of 'Describe Table' in SQL Server
When to Denormalize a Database Design
Display Names of All Constraints for a Table in Oracle SQL
Why Is Iterating Through a Large Django Queryset Consuming Massive Amounts of Memory
Reference an Alias Elsewhere in the Select List
Which Database Design Gives Better Performance
Inner Join in Update SQL for Db2
Why Postgres Returns Unordered Data in Select Query, After Updation of Row
How to Fill Date Gaps in MySQL
Why Using a Udf in a SQL Query Leads to Cartesian Product