ERROR: function addgeometrycolumn is not unique

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

Answers


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.


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.


Need Your Help

Xcode project is locked if using Cordova (Phonegap)

ios xcode cordova

I'm building an app with Cordova (Phonegap) and installed the Framework and build my project (for iOS and Android) with the command line tool.

Parsing JSON in Spring MVC using Jackson JSON

java json spring spring-mvc jackson

Ok, so I've been looking at this for a little while now and am no further on. I've got a Spring MVC servlet that I need to accept JSON from a JavaScript front end web app. To parse the JSON I nee...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.