May 5th, 2008 by depesz | Tags: , , , | 16 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

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.

  1. 16 comments

  2. # Robert Treat
    May 5, 2008

    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.

  3. May 6, 2008

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

  4. May 6, 2008

    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

  5. # Alin Hanghiuc
    May 7, 2008

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

  6. May 7, 2008

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

  7. May 8, 2008

    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.

  8. May 8, 2008

    I just noticed that the manual for 8.3 seems out of date, if implicit casts no longer exist … http://www.postgresql.org/docs/8.3/interactive/typeconv.html and its subsections talk about implicit casts all over the place.

  9. May 8, 2008

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

  10. # Dmitry Kirzhner
    Aug 7, 2008

    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

  11. Aug 7, 2008

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

  12. # radek
    Mar 4, 2009

    A working link to Peter’s “Readding implicit casts in PostgreSQL 8.3″ article: http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html

  13. # flexpadawan
    Apr 27, 2009

    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 || ‘);’

  14. Apr 27, 2009

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

  15. Oct 12, 2010

    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…

  16. # Shri
    Jul 26, 2014

    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.

  1. 1 Trackback(s)

  2. Mar 6, 2010: Sam Halperin | Simple Postgres Type Cast

Leave a comment