Thread: 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.klaver@aklaver.com

gzh <gzhcoder@126.com> writes:
> I run following sql in PostgreSQL 8.2, it return my expected result as ¡®YYYY-MM-DD¡¯ format.

> --PostgreSQL 8.2
> ---------------

> SELECT to_date(now() + '-7day', 'YYYY-MM-DD');

TBH, this was poor SQL code in 8.2, and it's poor SQL code now.
to_date doesn't take timestamp, and never has: it takes text.
The reason the query succeeded in 8.2 was that at the time, the
server would silently cast just about anything to text if necessary
to find a workable interpretation of the query.  We got rid of that
behavior in 8.3, because way too often the server's opinion of a
"workable interpretation" was surprising.

So you could make it work the way it used to by inserting the text
coercion explicitly:

=# SELECT to_date((now() + '-7day')::text, 'YYYY-MM-DD');
  to_date
------------
 2022-05-10

However, this hasn't done anything to make the call safer or more
performant.  It's slow because of an unnecessary formatting and
deformatting of the text equivalent of the timestamp, and it's
unsafe because your format choice represents a hard-wired assumption
that DateStyle is ISO.  For example, if I do

=# set datestyle = postgres;
SET

which means that timestamps will render to text like

=# select (now() + '-7day');
              ?column?
-------------------------------------
 Tue May 10 12:11:25.474873 2022 EDT
(1 row)

then it falls over completely:

=# SELECT to_date((now() + '-7day')::text, 'YYYY-MM-DD');
ERROR:  invalid value "Tue " for "YYYY"
DETAIL:  Value must be an integer.

You could get the equivalent behavior quicker and more safely by just
casting the timestamp value to date:

=# select (now() + '-7day')::date;
    date
------------
 2022-05-10
(1 row)

You might also want to look at other non-text-based manipulations
such as date_trunc() and date_part().

            regards, tom lane

gzh schrieb am 17.05.2022 um 17:55:
> 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
>
> I run following sql in PostgreSQL 8.2, it return my expected result as ‘YYYY-MM-DD’ format.
>
>
> --PostgreSQL 8.2
>
> ---------------
>
> SELECT to_date(now() + '-7day', 'YYYY-MM-DD');

Calling to_date() on a value that is already a date or timestamp value is a huge mistake to begin with (and as you
foundout a bug waiting to happen). 

That should have been current_date + 7 from day one.

On 5/17/22 9:23 AM, John W Higgins wrote:
> Good Morning,
> 

> Just create the missing function
> 
> create function to_date(timestamp with time zone, text) RETURNS text AS
>     'SELECT to_date($1::text, $2)::text'
>     LANGUAGE SQL
>     IMMUTABLE;


That is just kicking the can down the road. What the OP is running into 
is the tip of the iceberg of the changes not only in 8.3 but the other 6 
major versions involved. The time spent trying to create a compatibility 
layer for this jump and subsequent jumps would be better spent actually 
making the code current.

> John


-- 
Adrian Klaver
adrian.klaver@aklaver.com