Create extension postgis_tiger_geocoder with schema results in error

Products

• VMware Tanzu Greenplum

Issue/Introduction

When using the steps followed in the documentation to install a postgis extension in a specific schema, the postgis_tiger_geocoder extension fails with the following error:

kevin=# set search_path to "user", public, postgis, tiger;
SET
kevin=# create extension postgis with schema postgis;
CREATE EXTENSION
kevin=# SET search_path TO "user", postgis, tiger ;
SET
kevin=# create extension fuzzystrmatch with schema postgis;
CREATE EXTENSION
kevin=# SET search_path TO "user", public, postgis, tiger ;
SET
kevin=# create schema tiger;
CREATE SCHEMA
kevin=# create extension postgis_tiger_geocoder with schema tiger;
ERROR:  function soundex(character varying) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Environment

Product Version: 6.11

Resolution

In order for this to work, you need to use alter the database to set the search path to get postgis_tiger_geocoder to install correctly:

pgis=# create schema postgis;
CREATE SCHEMA
pgis=# set search_path to postgis;
SET
pgis=# create extension postgis with schema postgis;
CREATE EXTENSION
pgis=# create extension fuzzystrmatch  with schema postgis;
CREATE EXTENSION
-- Alter database to add postgis to the search_path
pgis=# ALTER DATABASE pgis SET search_path = public,postgis;
ALTER DATABASE
pgis=# \c
-- Verify the reset_val now has the schema set in it  
pgis=# select reset_val from pg_settings where name = 'search_path';
    reset_val
-----------------
 public, postgis
(1 row)
pgis=# create extension postgis_tiger_geocoder; 
CREATE EXTENSION