PG::Error: ERROR: function left(character varying, integer) does not exist

Question

I'm using PostgreSQL along with Rails 3.2. One of my db migrations has the following:

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

While migrating on some dbs, we're getting the following error:

==  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;

What's strange is this does not happen on all dbs, just some (staging). Any suggestions as to what's wrong with this index execution?

Answer 1

You tagged it postgresql-9.1, but I strongly suspect you are dealing with an older version here. What do you get when you ask:

SELECT version();

left() was introduced with version 9.1. For older versions substitute for left(fname, 1) with:

substr(fname, 1, 1)

Drop-in replacement

If you cannot modify the query for some reason (like @Wize), you can create a drop-in replacement for older versions before 9.1:

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

This typically won't cause conflicts after a version upgrade, since the default schema search path has pg_catalog (implicitly) before public, so the user-defined function is out of business as soon as the system function exits - unless schema-qualified explicitly. But you should remove it after a version upgrade anyway.

I added this, to suggest a number of improvements over what @Wize provided:

• Use LANGUGAE sql (not plpgsql) for multiple reasons:

  • Shorter, simpler.
  • plpgsql is not installed by default in versions before 9.0, so you might run into another problem just like the one you are trying to solve.
  • The simple function I present can be inlined, which improves performance in the context of bigger queries.

• Use function volatility STABLE, that's appropriate and helps performance.

• Use $n notation to reference function parameters, since parameter names are not supported for SQL functions in older versions.

• Create the function in the public schema explicitly. Else it might be created in the current users "private" schema and not work for other users. Depending on your schema search path, this should serve you best.

• Use data type text, which is the default character type and the same as left() or substr() return. Works for varchar as well.

Answer 2

Just create the following function for versions of Posgres before 9.1

CREATE OR REPLACE FUNCTION left(s character varying,i int)
RETURNS character varying AS
$BODY$
BEGIN
  return substr(s, 1, i);
 END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;