0

I am new to postgresql and I'm using postgresql 9.3 and postgis 2.1.

In pgadmin, I am able to use a function in database "AddGeometryColumn", but when I write that script in Ubuntu bash, it doesn't work. The error I got is

ERROR:  function addgeometrycolumn(unknown, unknown, unknown, unknown, unknown, integer) does not exist
LINE 1: SELECT AddGeometryColumn('tiger_staging','al_place','the_geo...
           ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Does anyone have similar experience?

Also how do you specify "use" database in postgresql? like a query in mysql "use mydatabase" to switch database?

Suanmeiguo
  • 1,365
  • 3
  • 17
  • 28

3 Answers3

1

I presume you are using "psql", so do just like this:

psql [databasename] -U [username] -t -e --command="select [function]"

As "Hint" says, you need to cast your param values with exact same types of your function's parameters:

psql [databasename] -U [username] -t -e --command="SELECT AddGeometryColumn(cast('tiger_staging' as character varying),..."

Also check if the number of params in your select matchs your function's parameters.

Christian
  • 7,062
  • 9
  • 53
  • 79
1

You are probably connected to a database that does not have the PostGIS extension installed (e.g. the default postgres database). In psql (the command-line client), check that the name coming before the =# prompt matches the database you want to use:

postgres=#

To switch to another database, use the \c command:

postgres=# \c mydatabase

... mydatabase=#

To check that the PostGIS extension is correctly installed in your current DB, use the postgis_full_version() function:

SELECT postgis_full_version();

This should return information on the PostGIS version if the extension is enabled. If you get a No function matches the given name and argument types error, you will need to install the extension in your current DB:

CREATE EXTENSION postgis;

Note that the PostGIS package has to be installed in order to be able to enable the extension in a database.

etdube
  • 11
  • 1
0

You are tripping over CaMeL case names.

Identifier (like AddGeometryColumn) are folded to lower case (addgeometrycolumn) unless double-quoted ("AddGeometryColumn"). Details in the manual.
Unfortunately, PostGis functions follow a naming convention that requires double-quoting at all times.

For your bash-invocation, you probably need to use single quotes around your string to remove the special meaning of double-quotes. Or escape with backslash (\").

Where ever I can I stick to legal, lower-case names exclusively for all identifiers to prevent problems like the one at hand.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Most of the PostGIS functions are documented as camel case, but operate as lower case names. Some were even renamed to fit this style, e.g. `ST_Line_Locate_Point` was recently renamed `ST_LineLocatePoint` – Mike T Dec 04 '13 at 20:44
  • @MikeToews: Well, I keep seeing desperate questions like the one above which reinforces my standing advice: only use *legal, lower case identifiers* that don't have to be double-quoted and save yourself some headache. The PostGis people probably have their reasons. Maybe some geo-data cross-platform compliance? I never use CaMeL case names if I can avoid it. I clarified the answer: legal to use, but my advice is to avoid them. – Erwin Brandstetter Dec 04 '13 at 20:52
  • I generally agree to use sane names. The "ST_CamelCase" convention used by most spatial databases started in the OGC and SQL/MM standards. – Mike T Dec 04 '13 at 22:07