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

score 7 · Answer 1 · answered Jun 23 '14 at 19:05

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.

score 6 · Accepted Answer · edited May 23 '17 at 12:13

6

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.

edited May 23 '17 at 12:13

Community

1
1

answered Sep 08 '12 at 16:14

Erwin Brandstetter

605,456
145
1,078
1,228

Do you mean something like this? = SELECT Assign_vertex_id('ways'::text, 0.00001::numeric, 'the_geom'::text, 'gid'::numeric). Sorry but I am new to these functions. – IT_info Sep 08 '12 at 16:34
1

@Ryan: Your function `Assign_vertex_id()` obviously (directly or indirectly) calls another function: `addgeometrycolumn()`, and the problem lies in this function call - as the error message clearly states. Looks like it is called in the process of creating a table automatically. So you need to remove one of more copies of `addgeometrycolumn()` from your system or add type casts to the call in `Assign_vertex_id()` to make it unambiguous. – Erwin Brandstetter Sep 08 '12 at 16:57
So how can I fix the problem that lies in the function call? Thanks for your reply – IT_info Sep 08 '12 at 17:00
@Ryan: I think I already answered that. I added a link to the manual for more on type casts. – Erwin Brandstetter Sep 08 '12 at 17:02
Yes I agree that I have to remove one of more copies of addgeometrycolumn(). But how am I going to remove them? – IT_info Sep 08 '12 at 17:06
1

You should *not* remove them. The standard postgis distribution defines 3 functions named addgeometrycolumn(), all with different parameter types/count, obviously. Just do the casting for the literal arguments. @ErwinBrandstetter: the MixedCase silliness is caused by postgis. – wildplasser Sep 08 '12 at 17:13
@Ryan: First, yo need to figure out, what went wrong. Don't start by deleting functions. You may make it worse. Maybe set up a clean installation and compare to find out which functions are supposed to be there. I can only speculate, as I don't have the complete picture. – Erwin Brandstetter Sep 08 '12 at 17:13
@wildplasser: I understand that the standard postgis defines 3 functions named addgeometrycolumn(), but for some reason I have 6 functions where 3 of the functions have NULL proarguments. – IT_info Sep 08 '12 at 17:20
I don't understand why you have six. Maybe you have two different versions installed on top of eachother (in a different schema?), or you messed up your catalogs. Maybe they are only different in the dreaded MixedCase ? What about the rest of the functions, have they been cloned, too? (my postgis installation has 558 functions) – wildplasser Sep 08 '12 at 17:26
`Three rows with column proargnames returning a blank or (null) value`: indeed looks like a corrupted catalog/failed installation. what about the other functions? – wildplasser Sep 08 '12 at 17:29
@wildplasser: functions in a different schema would not compete directly. – Erwin Brandstetter Sep 08 '12 at 17:31
On my database I have 949 functions, but i have imported also some pgrouting functions. Without pgrouting I would have a bit more than 700 – IT_info Sep 08 '12 at 17:32
1

@ErwinBrandstetter: you are correct. Ryan: if you select/dump all the functions+argnames, maybe there is a way to to distinguish the correct ones from the corrupt ones? (you could create an empty gis-enabled database to get the "correct" list) – wildplasser Sep 08 '12 at 17:41
It seems that that error is gone by deleting 3 corrupted functions of the addgeometrycolumn(). But now I have another error when executed the same query. I have posted another question with this problem; http://stackoverflow.com/questions/12333251/assign-vertex-id-function – IT_info Sep 08 '12 at 18:12

ERROR: function addgeometrycolumn is not unique

2 Answers2

Linked