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?

score 10 · Answer 1 · edited Aug 10 '23 at 15:19

You tagged it postgresql-9.1, but I strongly suspect you are dealing with an older version here. You can check which version you're using by asking:

SELECT version();

left() was introduced with version 9.1. If you are using an older version, substitute left(fname, 1) with:

substr(fname, 1, 1)

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 LANGUAGE 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.

Thank you Erwin, that was the issue we had members using different versions of postgres — AnApprentice, Oct 19 '12 at 20:48

score 2 · Answer 2 · answered Nov 20 '15 at 05:05

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;

answered Nov 20 '15 at 05:05

Wize

1,040
9
20

I provided this answer, because I wasnt able to change the code inside library. So this may help others in a similar situation – Wize Nov 20 '15 at 05:07
1

This is useful, but I see room for improvement. You might be interested in the alternative I added to my answer. – Erwin Brandstetter Apr 08 '16 at 01:14
Does anyone know why I was getting ERROR: syntax error at or near "COST" why creating this function? It went go if I removed VOLATILE COST 100; – uniquegino Oct 25 '19 at 15:00

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

2 Answers2