select * from depesz;

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.