assign_vertex_id error

Question

I am trying to test if pgrouting works fine or not. To do so I have created a table containing the following attributes.

  Columns:
 gid      |
 length   |
 the_geom |
 source   |
 target

Now my problem is that when I try to execute the assign_vertex_id function it is giving me the following error;

PL/pgSQL function "assign_vertex_id" line 15 at EXECUTE statement
ERROR:  query string argument of EXECUTE is null
CONTEXT:  PL/pgSQL function "assign_vertex_id" line 32 at EXECUTE statement

********** Error **********

ERROR: query string argument of EXECUTE is null
SQL state: 22004


Context: PL/pgSQL function "assign_vertex_id" line 32 at EXECUTE statement

Any suggestions what does this mean?

It would be easier to figure out if you showed the column types, any indexes and constraints (basically, the output of \d would be good), and especially the failing query. Somehow you have a function running an EXECUTE statement as dynamically generated SQL, but the string it's being passed is NULL. One way this could happen is to concatenate a series of values where one of them is NULL. — kgrittn

willglynn willglynn · Accepted Answer · 2012-09-07T20:45:34

assign_vertex_id() is defined as part of PGrouting in routing_topology.sql. (Running \df+ assign_vertex_id would also give you the current source.) Line 32's EXECUTE statement is:

EXECUTE 'update ' || quote_ident(geom_table) || 
    ' SET source = ' || source_id || 
    ', target = ' || target_id || 
    ' WHERE ' || quote_ident(gid_cname) || ' =  ' || _r.id;

The error is that EXECUTE is being called with a NULL argument. How? Well, the SQL || operator means concatenate, and concatenating NULL to a string results in NULL:

=> select ('string' || null) is null;
 ?column? 
----------
 t
(1 row)

My guess is the gid column of the underlying table (_r.id here) contains NULLs, although I suppose it could be source/target_id too. Fix it, then add a constraint to prevent that going forward:

ALTER TABLE whatever ALTER COLUMN gid SET NOT NULL;

assign_vertex_id error

2 Answers