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

Question

I am using PostgreSQL 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 

When porting to some dbs, we get 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 is strange, this does not happen in all dbs, just in some (staging). Any suggestions as to what is wrong with this index execution?

Answer 1

You marked it with postgresql-9.1 , but I strongly suspect that 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, replace left(fname, 1) with:

 substr(fname, 1, 1) 

Replacement insert

If you cannot change the request for any reason ( like @Wize ), you can create a replacement for older versions prior to 9.1:

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

Usually this does not cause conflicts after updating the version, since the default search path is pg_catalog (implicitly) to public , so the user -defined function exits the business as soon as the system function completes - unless the scheme is explicitly specified. But you must delete it after updating the version.

I added this to offer a number of improvements regarding what @Wize provided:

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

  • In short, easier.
  • plpgsql is not installed by default in versions prior to 9.0 , so you may run into another problem similar to the one you are trying to solve.
  • The simple function that I present can be inlined , which improves performance in the context of large queries.

• Use the volatility of the STABLE function, which is consistent and helps performance.

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

• Create a function in the public schema explicitly. Otherwise, it may be created in the scheme of existing "private" users and not work for other users. Depending on your search path, this should serve you best.

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

Answer 2

Just create the following function for Posgres versions prior to 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;