2201B ERROR: invalid regular expression when using replace() function

Question

Why Postgres gives me this error

[2014-10-30 13:43:36] [2201B] ERROR: invalid regular expression: invalid escape \ sequence Where: PL/pgSQL function normalize_username() line 5 at assignment

when I use the SQL statement:

UPDATE users."user" SET username = username

with the following trigger active:

CREATE OR REPLACE FUNCTION normalize_username()
  RETURNS TRIGGER AS $normalize_username$
BEGIN
  IF NOT (NEW.username IS NULL)
  THEN
    NEW.username := replace(NEW.username, 'ё', 'е');
  END IF;
  RETURN NEW;
END;
$normalize_username$ LANGUAGE plpgsql;

Table was created like this:

            CREATE TABLE USERS.user (
              user_id                SERIAL PRIMARY KEY,
              username                CITEXT,
)

replace() is a simple text function, right? It shouldn't have any relation to regular expressions I suppose.

Vladislav Rastrusny Vladislav Rastrusny · Accepted Answer · 2014-10-30T10:19:58

The problem was solved by casting username to TEXT in assignment like this:

CREATE OR REPLACE FUNCTION normalize_username()
  RETURNS TRIGGER AS $normalize_username$
BEGIN
  IF NOT (NEW.username IS NULL)
  THEN
    NEW.username := replace(NEW.username::TEXT, 'ё', 'е');
  END IF;
  RETURN NEW;
END;
$normalize_username$ LANGUAGE plpgsql;

It looks like CITEXT column operations are silently converted to regular expression operations under the hood. Can someone confirm this?

2201B ERROR: invalid regular expression when using replace() function

1 Answers