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

607 views Asked by At

I am doing an example from the pgrouting a Practical Guide book but with my own tables and I have run into this error. I have used LIMIT 1 to avoid this error as the example in the book does, but there must be another error. Any ideas on the fix?

ERROR:

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

SELECT *
    FROM pgr_drivingDistance(
    'SELECT id, source, target,
        cost
        FROM network.roads',
    (SELECT v.id
      FROM network.master_table AS h
        ,LATERAL (SELECT id FROM network.roads_vertices_pgr  AS n
           ORDER BY h.geom <-> n.the_geom LIMIT 1 ) AS v),
       5000, false, false
    );
1

There are 1 answers

0
Evan Carroll On

The limit 1 is on

SELECT id FROM network.roads_vertices_pgr  AS n
       ORDER BY h.geom <-> n.the_geom LIMIT 1

The error is on the whole subquery,

(SELECT v.id
      FROM network.master_table AS h
        ,LATERAL (SELECT id FROM network.roads_vertices_pgr  AS n
           ORDER BY h.geom <-> n.the_geom LIMIT 1 ) AS v)

That's a CROSS JOIN LATERAL on network.master_table and the result of that subquery (which you know to be one row). So if the the SELECT on network.master_table returns more than one row, you get that error.