ERROR: function addgeometrycolumn is not unique

Question

I am trying to use the following function;

SELECT Assign_vertex_id('ways', 0.00001, 'the_geom', 'gid')

but for some reason it is giving me the following error;

NOTICE:  CREATE TABLE will create implicit sequence "vertices_tmp_id_seq" for serial column "vertices_tmp.id"
CONTEXT:  SQL statement "CREATE TABLE vertices_tmp (id serial)"
PL/pgSQL function "assign_vertex_id" line 15 at EXECUTE statement
ERROR:  function addgeometrycolumn(unknown, unknown, integer, unknown, integer) is not unique
LINE 1: SELECT addGeometryColumn('vertices_tmp', 'the_geom', 4326, '...
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
QUERY:  SELECT addGeometryColumn('vertices_tmp', 'the_geom', 4326, 'POINT', 2)
CONTEXT:  PL/pgSQL function "assign_vertex_id" line 24 at EXECUTE statement

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

ERROR: function addgeometrycolumn(unknown, unknown, integer, unknown, integer) is not unique
SQL state: 42725
Hint: Could not choose a best candidate function. You might need to add explicit type casts.
Context: PL/pgSQL function "assign_vertex_id" line 24 at EXECUTE statement

Now from what I found it has to be something with old PostGIS signatures around.Infect when I ran The following command;

select proname, proargnames from pg_proc where proname = 'addgeometrycolumn'; 

The result was this;

pg_proc returns 6 rows.

Three rows with column proargnames  returning a blank or (null) value

Can someone help me? Is it something that has to do with old postgis signitures? if so, how can I fix it?

Thanks

Answer 1

I have also encountered this problem and I think the OP may have solved it incorrectly. First, AddGeometryColumn is indeed overloaded. The three prototypes are:

    AddGeometryColumn(table_name, column_name, srid, type, dimension,
use_typmod=true)
    AddGeometryColumn(schema_name, table_name, column_name, srid, type, dimension, use_typmod=true)
    AddGeometryColumn(catalog_name, schema_name, table_name, column_name, srid, type, dimension, use_typmod=true)

In my case, changing the following query:

SELECT AddGeometryColumn('public', 'facilities', 'walk_area', 4326, 'POLYGON', 2);

(which uses the second form) to this:

SELECT AddGeometryColumn('public', 'facilities', 'walk_area', 4326, 'POLYGON', 2, true);

solved the problem.

Answer 2

PostgreSQL supports function overloading.

With overloaded functions (like you obviously have), a call with just text literals (and no explicit type casts) can be ambiguous.

Normally, adding explicit type casts to your parameters literals fixes the problem. Arbitrary example:

SELECT my_fuc('foo'::text, 0.001::numeric, 123::int);

In your case, this call is ambiguous:

addGeometryColumn('vertices_tmp', 'the_geom', 4326, 'POINT', 2)

Be aware of these points:

• All unquoted identifiers are cast to lower case in Postgres. addGeometryColumn(...) is effectively the same as addgeometrycolumn(...).

• You may need to schema-qualify the function name to make it unambiguous. (Maybe you recently changed the search_path leading to a surprising result.

• If you do indeed have overloaded functions (not uncommon), add type casts to make your calls unambiguous.

• Defining parameter defaults for overloaded functions can make a previously unique call ambiguous.