I just realized that I wanted to ask about another error. *more than one row returned by a subquery used as an expression * not about *subquery must return only one column*
2013/6/21 Борис Ромашов <boraldomas...@gmail.com> > Tom, suppose you haven't understood what the problem I'm facing with. > Let me explain deeper. > > Try to execute the following 2 queries. > select (select generate_series(1,2)); > select (select generate_series(1,1)); > > They differ only in data, both of them are well-written, so there is not > the problem in parsing. > But first query gives (even in psql) > *ERROR: more than one row returned by a subquery used as an expression* > > Certainly - instead of generate_series I could write any usual query that > fetches some data from database. > And if this query returns one row - everything is correct. > > And certainly, instead of* *selecting from dual (that is how it is called > in Oracle) - I could construct more complex external query such that > subquery could return "more than one row" for just in some exact row (not > in each row) of external record set. > > Example > select id, (select friend.id from user friend where friend.id = user.id) > user from user > > This query fetches all users with their friends assuming that every user > has only one friend. > But if some of them will have 2 friends - this query will fail with > *ERROR: more than one row returned by a subquery used as an expression* > And I will have no chance to guess - which user exactly this happened for. > > > > > 2013/6/20 Tom Lane <t...@sss.pgh.pa.us> > >> boraldomas...@gmail.com writes: >> > When I get this message I cannot guess from it's description what really >> > causes this error. >> > I would like to see exactly the subquery that returned more than one >> column >> > and the row where this happened. >> >> That's a parse-time error, so it's nonsensical to ask for "the row where >> it happened". AFAICS, the parser should give back a syntax-error >> pointer for this error; for example, when I try to provoke the error in >> psql, I get >> >> =# select * from table1 where id = any(array(select c1,c2 from table2)); >> ERROR: subquery must return only one column >> LINE 1: select * from table1 where id = any(array(select c1,c2 ... >> ^ >> >> which shows me that the problem is associated with the ARRAY() construct >> not accepting multiple input columns. If you're not seeing such an >> error pointer, it's the fault of whatever client-side software you're >> working in. >> >> regards, tom lane >> > >