postgresql - PG::Error: ERROR: function left(character variables, integer) 不存在

我正在使用 PostgreSQL 和 Rails 3.2。我的数据库迁移之一具有以下内容:

execute <<-SQL
  CREATE INDEX users_multi_idx
  ON users (lower(left(fname, 1)), fname)
  WHERE deleted_at IS NULL;
SQL

在某些数据库上迁移时，我们收到以下错误:

==  AddFnameIndexToUsers: migrating ===========================================
-- execute("      CREATE INDEX users_multi_idx\n      ON users (lower(left(fname, 1)), fname)\n      WHERE deleted_at IS NULL;\n")
rake aborted!
An error has occurred, this and all later migrations canceled:

PG::Error: ERROR:  function left(character varying, integer) does not exist
LINE 2:       ON users (lower(left(fname, 1)), fname)
                              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
:       CREATE INDEX users_multi_idx
      ON users (lower(left(fname, 1)), fname)
      WHERE deleted_at IS NULL;

奇怪的是这不会发生在所有 dbs 上，只是一些(暂存)。关于此索引执行有什么问题的任何建议？

最佳答案

您将其标记为 postgresql-9.1 ，但我强烈怀疑您在这里处理的是旧版本。当您询问时，您会得到什么:

SELECT version();

left() 是在 9.1 版中引入的。对于旧版本，用 left(fname, 1) 替代:

substr(fname, 1, 1)

直接更换

如果由于某种原因无法修改查询 ( like @Wize )，您可以为 9.1 之前的旧版本创建一个替代品:

CREATE OR REPLACE FUNCTION public.left(text, int)
 RETURNS text LANGUAGE sql STABLE COST 30 AS
'SELECT substr($1, 1, $2)';

这通常不会在版本升级后引起冲突，因为默认 schema search path 在 pg_catalog 之前具有 public (隐式)，因此一旦系统函数退出，用户定义的函数就会停止运行 - 除非模式明确限定。但是无论如何您都应该在版本升级后将其删除。

我添加了这个，以建议对 @Wize 提供的内容进行一些改进:

出于多种原因使用 LANGUGAE sql (而不是 plpgsql ):

更短、更简单。

plpgsql is not installed by default in versions before 9.0 ，因此您可能会遇到另一个问题，就像您要解决的问题一样。

我提供的简单函数可以是 inlined ，它可以提高更大查询上下文中的性能。

使用函数波动性 STABLE ，这是合适的，有助于提高性能。

使用 $n 表示法来引用函数参数，因为旧版本的 SQL 函数不支持参数名称。

在 public 架构中显式创建函数。否则，它可能是在当前用户的“私有(private)”模式中创建的，不适用于其他用户。根据您的 schema search path ，这应该最适合您。

使用数据类型 text ，这是默认的字符类型，与 left() 或 substr() 返回相同。也适用于 varchar。

关于postgresql - PG::Error: ERROR: function left(character variables, integer) 不存在，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12982085/