Re: TO_DATE function between PostgreSQL 8.2 and 9.4

On 5/17/22 08:55, gzh wrote:
> Hi,
>
> I have had a Perl Website working for 7 years and have had no problems
>
> until at the weekend I replace my database server with a newer one.
>
> Database server (old): PostgreSQL 8.2 32bit
>
> Database server (new): PostgreSQL 9.4 64bit

You do know 9.4 is 2 years past EOL.

>
> Of course, the most reliable way to deal with it is to
>
> rewrite the application or SQL to handle types strictly,
>
> but the application is large and rewrite is a terrible job.
>
>
> Is there any easy way to maintain compatibility?

Not really this jump went through the 8.3 type casting changes:

https://www.postgresql.org/docs/8.3/release-8-3.html

In particular:

"

Non-character data types are no longer automatically cast to TEXT
(Peter, Tom)

Previously, if a non-character value was supplied to an operator or
function that requires text input, it was automatically cast to text,
for most (though not all) built-in data types. This no longer happens:
an explicit cast to text is now required for all non-character-string
types. For example, these expressions formerly worked:

substr(current_date, 1, 4)
23 LIKE '2%'

but will now draw "function does not exist" and "operator does not
exist" errors respectively. Use an explicit cast instead:

substr(current_date::text, 1, 4)
23::text LIKE '2%'

(Of course, you can use the more verbose CAST() syntax too.) The reason
for the change is that these automatic casts too often caused surprising
behavior. An example is that in previous releases, this expression was
accepted but did not do what was expected:

current_date < 2017-11-17

This is actually comparing a date to an integer, which should be (and
now is) rejected — but in the presence of automatic casts both sides
were cast to text and a textual comparison was done, because the text <
text operator was able to match the expression when no other < operator
could.

Types char(n) and varchar(n) still cast to text automatically. Also,
automatic casting to text still works for inputs to the concatenation
(||) operator, so long as least one input is a character-string type.
"
>
> Regards,
>
> --
>
> gzh
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com