PostgreSQL error: subquery must return only one column

Question

Using PostgreSQL-9.1 and PostGIS 2.0.1, when doing a SELECT query containing a sub-query that returns multiple columns, I am getting the error subquery must return only one column.

How can the query/subquery be modified to return multiple columns?

Query

SELECT l.id, l.lat, l.lng, l.geom,
        (SELECT g.id, g.lat, g.lng, ST_Distance(l.geom, g.geom)
        FROM stage.dogs as g
        LIMIT 1)

FROM stage.users As l

---

Full Query

SELECT l.id, l.lat, l.lng, l.geom,
    g.id, g.lat, g.lng, ST_Distance(l.geom, g.geom) 
FROM stage.users As l
CROSS JOIN (SELECT *
    FROM stage.dogs as g
    ORDER BY g.geom <-> l.geom 
    LIMIT 1) as g

Error

ERROR: invalid reference to FROM-clause entry for table "l"
SQL state: 42P01
Hint: There is an entry for table "l", but it cannot be referenced from this part of the query.

Answer 1

SELECT l.id, l.lat, l.lng, l.geom,
       g.id, g.lat, g.lng, ST_Distance(l.geom, g.geom)
FROM stage.users As l
CROSS JOIN (SELECT * FROM stage.dogs LIMIT 1) as g

This is literally what you had (assuming stage.dogs) is not empty. Not sure if there should be a correlation between users and dogs though.

---

To find the closest dog to a user, you can use this query. The scalar subquery finds the dog's ID, and is joined back to the table to retrieve the other columns.

SELECT l.id, l.lat, l.lng, l.geom,
       g.id, g.lat, g.lng, ST_Distance(l.geom, g.geom)
FROM (
    SELECT l1.*, (SELECT g1.id
                  FROM stage.dogs as g
                  ORDER BY g.geom <-> l.geom 
                  LIMIT 1) g_id
    FROM stage.users As l1
) l
JOIN stage.dogs as g ON g.id = l.g_id;

Fair warning that this will NOT be a fast query.

---

At the risk of performing even slower, see query below for multiple tables

SELECT l.id, l.lat, l.lng, l.geom,
       g.id, g.lat, g.lng, ST_Distance(l.geom, g.geom) dog_distance,
       c.id, c.lat, c.lng, ST_Distance(l.geom, c.geom) cat_distance,
       b.id, b.lat, b.lng, ST_Distance(l.geom, b.geom) bird_distance
FROM (
    SELECT l1.*, (SELECT g1.id
                  FROM stage.dogs as g1
                  ORDER BY g1.geom <-> l.geom 
                  LIMIT 1) dog_id,
                 (SELECT c1.id
                  FROM stage.cats as c1
                  ORDER BY c1.geom <-> l.geom 
                  LIMIT 1) cat_id,
                 (SELECT b1.id
                  FROM stage.cats as b1
                  ORDER BY b1.geom <-> l.geom 
                  LIMIT 1) bird_id
    FROM stage.users As l1
) l
LEFT JOIN stage.dogs as g ON g.id = l.dog_id
LEFT JOIN stage.dogs as c ON c.id = l.cat_id
LEFT JOIN stage.dogs as b ON b.id = l.bird_id;