function soundex(character varying) does not exist

[mikem]

Version info: "PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit POSTGIS="2.1.0 r11822" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.6" LIBJSON="UNKNOWN" RASTER"

@ ​http://postgis.net/install, I am following the "Getting Started" > "Enabling PostGIS" > "Run the following SQL" instructions.

The last successful instruction I can execute is "CREATE EXTENSION fuzzystrmatch;"

The next statement fails "CREATE EXTENSION postgis_tiger_geocoder"

The message is: ERROR: function soundex(character varying) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts. Character: 56874

Background: I was able to successfully execute all CREATE EXTENTION commands before when I was using, and had, the "public" schema which is included in a default installation. Having followed recommendations to remove the "public" schema, I removed these extensions and dropped the "public" schema. Then I created a "postgis2" schema, set my search_path to it and for good measure altered my login to default to the new "postgis2" schema. Then started executing the CREATE EXTENSION commands again and got stuck as noted above. I have verified that function soundex(text) exist in my new "postgis2" schema along with 1058 other functions.

The only change from when this worked to when it broke is that when it worked, the installation schema was the "public" schema and it does not work when the public schema is dropped and I install use a custom schema.

I tried to resolve the issue by reverting with: drop extension fuzzystrmatch cascade; drop extension postgis_topology cascade; drop extension postgis cascade; drop schema postgis2 cascade; create schema public; set search_path to public; alter user michael set search_path to public; CREATE EXTENSION postgis; CREATE EXTENSION postgis_topology; CREATE EXTENSION fuzzystrmatch; CREATE EXTENSION postgis_tiger_geocoder;

… but the problem persists.

[robe]

I do recall their being an issue with CREATE EXTENSION fuzzystrmatch being installed in topology schema if you install right after topology, but can't think of an easy way out of that.

Try installing fuzzystrmatch first.

The error usually means that where you have fuzzstrmatch is not in your search_path or the account you are using doesn't have rights to the installed schema.

Make sure that postgis2 is in your database search path with command something like

ALTER DATABASE gisdb
  SET search_path = "$user", postgis2, tiger_geocoder,topology;

I would avoid setting your user profile to specific search paths. That's just asking for trouble unless you know what you are doing and can remember that you did it for a specific role.

Do a:

ALTER ROLE michael RESET search_path;

[mpaul]

I'll elaborate on robe's very useful comment.

I had the same problem, and decided to check the source code. In postgis-2.1.0/extensions/postgis_tiger_geocoder/sql/postgis_tiger_geocoder—2.1.0.sql , search_path is immediately set to its reset_val in pg_settings. To find out what value is being used, do:

SELECT reset_val FROM pg_settings WHERE name = 'search_path';

As robe suggested, I removed my own search_path ( ALTER ROLE ... RESET search path; ), changed the default search_path for the database ( ALTER DATABASE ... SET search_path ... ; ) to include the schema in which I had created the fuzzystrmatch extension, logged out, logged back in, and successfully ran:

CREATE EXTENSION postgis_tiger_geocoder;

In psql, you can use \df soundex to find the name of the schema where fuzzystrmatch was installed. If you wish, you can DROP that extension and CREATE it in a different schema before proceeding.

[mikem]

Agreed. I did reset my search_path, log-out and log back in again. Then re-ran the script and it did work. Sorry for the false alarm. Thanks guys!