- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我正在尝试在我们的大型 RDS 实例上安装 AWS“批准的”PostgreSql 扩展,但每次我尝试“创建扩展 postgis_tiger_geocoder”时,我都会得到这个:
SQL Error [42883]: ERROR: function soundex(character varying) does not exist
我花了很多时间阅读 AWS/postgis/postgresql 论坛,但不幸的是没有找到墙上的文字。
采取的步骤
安装了 POSTGIS 扩展
create EXTENSION postgis;
安装了包含 soundex 函数的 FuzzyStrMatch 扩展(已验证)
create EXTENSION fuzzystrmatch;
最后,当我运行这个创建扩展时,我得到了上面的错误
create extension postgis_tiger_geocoder;
SQL Error [42883]: 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.
Position: 57558
org.postgresql.util.PSQLException: 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.
Position: 57558
我尝试过的事情:
set search_path = <schema_name>, public
关注这里: Installing PostgreSQL Extension to all schemas深入研究 postgis 安装文档通读有关添加扩展的 RDS 文档...
如果有人不得不在 AWS 上处理这种挫败感,我会很高兴地交换掉我头上剩下的几根头发,因为我无法解决这个问题。
\dx+结果
Objects in extension "fuzzystrmatch"
Object Description
--------------------------------------------------------------------------------
function <schema>.difference(...)
function <schema>.dmetaphone_alt(...)
function <schema>.dmetaphone(...)
function <schema>.levenshtein_less_equal(...)
function <schema>.levenshtein_less_equal(...)
function <schema>.levenshtein(...)
function <schema>.levenshtein(...)
function <schema>.metaphone(...)
function <schema>.soundex(...)
function <schema>.text_soundex(...)
(10 rows)
\dfS+ soundex 的结果
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Owner | Security | Access privileges | Language | Source code | Description
--------+------+------------------+---------------------+------+------------+-------+----------+-------------------+----------+-------------+-------------
(0 rows)
最佳答案
有同样的问题,通过改变数据库的 search_path 解决它,并在创建扩展 postgis_tiger_geocoder 之前重新连接。寻找 FIX 部分:
-- Postgis Installation
------------------------------------------------------------------------------------------------------------------------------------------------
-- PostGIS AWS Configuration --
-- https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.PostGIS --
------------------------------------------------------------------------------------------------------------------------------------------------
-- On postgis schema
SET SCHEMA '${POSTGIS_SCHEMA_NAME}';
-- Step 2: Load the PostGIS Extensions
create extension postgis;
create extension fuzzystrmatch;
-- FIX : To avoid "ERROR: function soundex(character varying) does not exist", change schema and reconnect
ALTER DATABASE ${DATABASE_NAME} SET search_path=${POSTGIS_SCHEMA_NAME};
\connect ${DATABASE_NAME};
-- End FIX
create extension postgis_tiger_geocoder;
create extension postgis_topology;
-- Step 3: Transfer Ownership of the Extensions to the rds_superuser Role
alter schema tiger owner to ${MASTER_USER};
alter schema tiger_data owner to ${MASTER_USER};
alter schema topology owner to ${MASTER_USER};
-- Step 4: Transfer Ownership of the Objects to the rds_superuser Role
CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO ${MASTER_USER};')
FROM (
SELECT nspname, relname
FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname in ('tiger','topology') AND
relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;
-- Adding postgis to default schema
ALTER DATABASE ${DATABASE_NAME} SET search_path=${SCHEMA_NAME},${POSTGIS_SCHEMA_NAME};
关于postgresql - AWS RDS PostgreSQL 9.5.4 扩展 postgis_tiger_geocoder 缺少 Soundex?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/46394880/
我正在尝试在我们的大型 RDS 实例上安装 AWS“批准的”PostgreSql 扩展,但每次我尝试“创建扩展 postgis_tiger_geocoder”时,我都会得到这个: SQL Error
我是一名优秀的程序员,十分优秀!