“ERROR: operator does not exist: integer = text” how to fix it?

PostgreSQL 8.3 brought us many great new features. Among them were concurrent autovacuum, enums, tsearch in core.

On of the changes though made a lot of people dislike 8.3. It was removal of implicit casts.

What do I mean?

In PostgreSQL 8.2, You could easily do something like this:

# SELECT 1::int4 = '1'::text;
 ?COLUMN?
----------
 t
(1 ROW)

In PostgreSQL 8.3 though You get:

# SELECT 1::int4 = '1'::text;
ERROR:  operator does NOT exist: INTEGER = text
LINE 1: SELECT 1::int4 = '1'::text;
                       ^
HINT:  No operator matches the given name AND argument TYPE(s). You might need TO ADD explicit TYPE casts.

It means that If You have anywhere join between tables that use fields of int (on one side) and text (on second side) fields to join – it will fail.

You might ask – why did they remove it?

Well, the standard answer is: “to avoid surprising behavior".

What surprising? After all *I* can see that number “1" is equal to string “1", don't I ?

Apparently it's not that simple.

For example, try to answer, if “1" (integer) should be equal to these texts:

  • “01"
  • “00000001"
  • " 01″
  • “1.0"
  • “01.0"
  • " 1. “
  • “a1"
  • “1b"

Oops, apparently it's not as obvious as it seemed earlier.

But, what should one do, if he has a ready software which works on 8.2, but he wants to upgrade to 8.3?

First answer and the best way to solve the problem is: fix the code.

Check which value should be cast to which, and add proper casts. Like this:

SELECT * FROM tablea a JOIN tableb b ON a.intfield = b.textfield;

into:

SELECT * FROM tablea a JOIN tableb b ON a.intfield = b.textfield::int4;

It requires change of code, or even data structure. But it's definitely the best way to handle the issue.

But what happens if Your software is “million lines long, and the developers don't work there anymore"?

It's actually pretty simple – You can add the casts Yourself.

Let's start with the basic one: int4 vs. text.

CREATE FUNCTION int_to_text(INT4) RETURNS TEXT AS '
    SELECT textin(int4out($1));
' LANGUAGE SQL STRICT IMMUTABLE;
CREATE CAST (INT4 AS TEXT)
    WITH FUNCTION int_to_text(INT4)
    AS IMPLICIT;

What does it do? First it creates new function, which takes single, int4 argument, and returns text. Inside it does “magic": it converts int4 from internal representation to “outside" representation (which happens to be in decimal :), and then uses textin to convert it to text internal format.

Pretty simple.

Second command – CREATE CAST, informs PostgreSQL, that it can use this newly created function to case data from INT4 to TEXT. “AS IMPLICIT" means that this function will be used in implicit casts, like this:

SELECT * FROM tablea a JOIN tableb b ON a.intfield = b.textfield

Which is what we need.

So, how does it work afterwards?

# SELECT 1::INT4 = '1'::TEXT;
 ?COLUMN?
----------
 t
(1 ROW)

Now, that's nice, but let's check if it really works nicely:

SELECT 1::INT4 = '01'::TEXT;
 ?COLUMN?
----------
 f
(1 ROW)

Apparently it doesn't.

Reason is very simple. Our implicit cast made the query look like:

SELECT '1'::TEXT = '01'::TEXT;

Which is of course not true.

And what if You'd like it to be true?

First, I need to drop previous cast:

DROP CAST (TEXT AS INT4);

Then, I'll make another function, this time to convert text to int:

CREATE FUNCTION text_to_int(TEXT) RETURNS INT4 AS '
    SELECT int4in(textout($1));
' LANGUAGE SQL STRICT IMMUTABLE;

And now I'll add new cast:

CREATE CAST (TEXT AS INT4)
    WITH FUNCTION text_to_int(TEXT)
    AS IMPLICIT;

And how does it work now?

SELECT 1::INT4 = '1'::TEXT, 1::INT4 = '01'::TEXT, 1::INT4 = '2'::TEXT;
 ?COLUMN? | ?COLUMN? | ?COLUMN?
----------+----------+----------
 t        | t        | f
(1 ROW)

There is one issue though. With previous cast (int to text), if I did something like this:

SELECT 1::int4 = 'z'::text;

I would simply get “false". Now, with reverse cast I get:

ERROR:  invalid input syntax for integer: "z"
CONTEXT:  SQL function "text_to_int" statement 1

Now. I could simply make my text_to_int function smarter. Perhaps use regexp to find first number in string. It all depends on Your use-case.

Generally – if You have to add casts – make them as simple as possible.

Now, besides int4 = text, there are tons of other casts required for some legacy applications. int2, int8, timestamp, timestamptz. All of these might require specific cast.

And all of them are quite simple to write – Just take a look at the example above, and You'll easily add Your own case in no time. Or, if You really need someplace to copy/paste from, please check Peter's post about “Readding implicit casts in PostgreSQL 8.3" – it has ready-made list of all functions and casts that should be added to restore 8.2 auto casts.

21 thoughts on ““ERROR: operator does not exist: integer = text” how to fix it?”

  1. Your first cast did work nicely, or at least it matched 8.2’s behavior.

    postgres=# select 1::int4 = ‘1’::text;
    ?column?
    ———-
    t
    (1 row)

    postgres=# select 1::int4 = ’01’::text;
    ?column?
    ———-
    f
    (1 row)

    I notice neither yours nor Peter’s post mention that restoring the pre-8.3 casts could cause things in 8.3 to break, since the 8.3 back-end expects a different behavior than the casts set up, people should be made aware of that.

  2. @Robert Treat:
    Hmm, Can You show me some case where adding cast breaks things?

    I was pretty sure this is safe – after all – PostgreSQL is modular, and You can add any casts/functions/operators/anything You want, and I guessed that if You can add something – it shouldn’t break anything else.

    Of course if we have a software that *relies* on the fact that 1::int4 = ‘1’::text will raise exception – this is totally different case, but I find it hard to imagine why would someone code this kind of reliance in his/her application.

  3. I have some problem with Peter’s sql script.
    When I use it, there is some error messages in the log, and Psql 8.3 server must be restarted to work normaly:
    ERROR: function quote_literal(date) is not unique at character 363
    HINT: Could not choose a best candidate function. You might need to add explicit type casts.
    SELECT …
    started <= ‘ || quote_literal( $3 ) || ‘::date

    The solution is:
    started <= ‘ || quote_literal( par_when::varchar ) || ‘::date

  4. I hate implicit casts also. I think is a good thing they were removed.

  5. @Alin Hanghiuc:
    to be honest I don’t really have strong opinion on this.
    On one hand – removal of them didn’t touched me personally as i never did things like int = text.
    On the other hand – I’ve seen too many people that had their precious applications broken after upgrade.

    Of course it’s their (or, to be exact: their developers) fault, but it just doesn’t feel 100% right.

    I think the best solution would be to issue a warning for a version or two, then migrate it to error while adding switch to turn it back to warning, and then after 4-or-so versions – simply remove the switch.

    Of course – it would make the whole process longer, but I think it would be less painful for those who has to maintain and/or use legacy apps. At least they would have some early warning and possibility to go to boss and say: “hi, we need additional $10000 in budget to rewrite the app, because it will stop working in 3-4 releases”.

    So – I’m not opposing removal of implicit casts, I just feel that the way that it was done was too harsh.

  6. As someone who is about to start using Postgres “real soon now”, I’m happy that 8.3+ does not have implicit casts as this makes development a lot more rigorous and less error prone, not to mention more logically correct.

  7. @Darren Duncan:
    not all implicit casts were removed. Only the ones that could have misleading results.

  8. Hello,

    I created int_to_text and text_to_int methods with CASTs in my public database as you proposed here.
    Anyway I get the message:

    # select 1::int4 = ‘1’::text;
    ERROR: operator does not exist: integer = text
    LINE 1: select 1::int4 = ‘1’::text;
    ^
    HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

    The CUST exists. I checked it. I cannot create it again without to drop the old one.

    Do you have any Idea?

    Regards,
    Dmitry

  9. @Dmitry Kirzhner:
    can you create new, empty database, and repeat all steps, then send the screenshot/session log to me?

  10. I’m trying to get my EXECUTE function to work and is giving me type cast errors and has me stumped. And I’m not sure how to align the casting properly.

    I have a trigger that calls a function. In my trigger I dynamically create my select statement.

    My Variable:
    array_details character varying[];
    I populate it with {a,b}

    Hard-coded (Works):
    SELECT catalogue.cg_bdd (ARRAY[‘a’,’b’]);

    Dynamically Created (Fails):
    EXECUTE ‘SELECT catalogue.cg_bdd ‘(‘ || array_details || ‘);’;

    Error:
    ERROR: operator does not exist: text || character varying[]
    LINE 1: SELECT ‘SELECT ‘ || $1 || ‘(‘ || $2 || ‘);’
    HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
    QUERY: SELECT ‘SELECT ‘ || $1 || ‘(‘ || $2 || ‘);’

  11. @flexpadawan:
    well, one could argue that it should work, but generally – don’t mix your datatypes.

    change your array to be text[] and not varchar[], and it will work.

  12. Pingback: Sam Halperin | Simple Postgres Type Cast
  13. I think the postgres people had Oracle envy, and were thus inspired to force a hideous syntax on developers.

    Look, I understand that there are edge cases. Fine, flag them with errors. But for the non-edge cases, the “upgrade” breaks one of the sacred principles all applications should follow: if the computer CAN figure it out, it SHOULD.

    Now I am wasting time to add ugly casts to code that just worked before, to suit some purist obsession.

    Count me in the “unhappy” camp.

    Grrr…

  14. After adding old implicit castingin pg9.3

    if i run SELECT 1|| ‘/%’;
    it gives error
    ERROR: operator is not unique: integer || text

    i have to drop integer as text cast.

  15. column needs to be unnest

    WITH bar AS (
    SELECT array_agg(b) AS bs
    FROM foo
    WHERE c < 3
    )
    SELECT a
    FROM foo
    WHERE b = ANY ( SELECT unnest(bs) FROM bar);

  16. This web page saved me, very useful post.
    suffering from the and got fixed with help of this post.

    Thanks Thanks…………

  17. When you implicitly cast, you cast DOWN to the next sensible generic data type until you get to matching types. Always go int to string, not the other way around! ‘bool’ is a specialized form of ‘int’. ‘int’ is a specialized form of ‘string’. ‘string’ is a specialized form of ‘binary’. You get the idea. This is not hard to get right.

    I just ran into this in a LEFT OUTER JOIN query where the type in the target table is a string but is keyed such that a simple cast WORKS JUST FINE and a simple implicit cast from int to string is guaranteed to NEVER FAIL and, even if it ever does, I still get the left table data. As far as I’m concerned, this is a major bug in Postgres. All the other major database products out there and every decent scripting language worth its salt seem to handle implicit int to string casts just fine. If the devs want to have explicit casts in their database engine, then they should do something along the lines of the triple-equals operator (===). That way, those of us using generic DAO/ORM classes can continue to use them without having to worry about some insane developer’s decision to break application compatibility.

    There are legitimate use-cases where casting from integer to string is the ONLY available option. But, seriously, this post has the answer all wrong. You want to go from int to string and THEN do the comparison, not the other way around.

  18. Hi

    good evening

    I am not able to convert charater type field varying to integer in postgres ..

    field:
    Qt_prest_pagas::int + 1

    The following error appears:

    ERROR: invalid input syntax for integer: “”

    Greetings.

    Well

  19. @well:

    sorry, but what is not clear about it?

    You can’t cast empty string to integer. What value would it be?

Comments are closed.