Bug #605: timestamp(timestamp('a timestamp)) no longer works
Andrew McMillan (andrew@catalyst.net.nz) reports a bug with a severity of 3
The lower the number the more severe it is.
Short Description
timestamp(timestamp('a timestamp)) no longer works
Long Description
In version 7.2 it seems that I can't reduntantly cast value to timestamp if it is already a timestamp.
I do this reasonably often in my code by way of being paranoid that I might have a date, or a time, where I for sure _really_ want it to be a timestamp...
It's cleaning up some bugs in my code, I suppose, but I kind of like making it explicit to people who might come along after me :-)
Sample Code
Here's the broken query:
pcnz=# select timestamp('2002-03-01'::timestamp);
ERROR: parser: parse error at or near "'"
pcnz=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)
I notice that int4(int4()) still works:
pcnz=# select int4( '777'::int4 );
int4
------
777
(1 row)
A couple of older versions where this worked:
pcnz=# select timestamp('2002-03-01'::timestamp);
timestamp
------------------------
2002-03-01 00:00:00+13
(1 row)
pcnz=# select version();
version
---------------------------------------------------------------
PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.95.2
(1 row)
stimulus=# select timestamp('2002-03-01'::timestamp);
timestamp
------------------------
2002-03-01 00:00:00+13
(1 row)
stimulus=# select version();
version
---------------------------------------------------------------
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)
No file was uploaded with this report
pgsql-bugs@postgresql.org writes:
timestamp(timestamp('a timestamp)) no longer works
timestamp(x) is a type name now. In place of timestamp(foo) use
"timestamp"(foo)
foo::timestamp
CAST(foo AS timestamp)
And yes, this is pointed out in the migration notes...
regards, tom lane
timestamp(timestamp('a timestamp)) no longer works
I do this reasonably often in my code by way of being paranoid
that I might have a date, or a time, where I for sure _really_
want it to be a timestamp...
pcnz=# select timestamp('2002-03-01'::timestamp);
ERROR: parser: parse error at or near "'"
You *can* coerce timestamps to be timestamps, but in 7.2 non-standard
syntax no longer works to do this. The reason is that "timestamp(p)" now
follows the SQL9x usage of defining a timestamp type with precision "p".
So trying to call a function "timestamp()" no longer works as it did.
You can use SQL9x syntax for the type coersion:
select cast('2002-03-01'::timestamp as timestamp);
or (not recommended) you can cheat and force the call to the function by
surrounding it in double-quotes:
select "timestamp"('2002-03-01'::timestamp);
hth
- Thomas
How would I go about clearing this "bug" from the bug database? I
haven't looked at the bugtool in quite some time, but I'm not seeing a
reference to it on the web site...
- Thomas
Go to: http://www.ca.postgresql.org/bugs/admin/managebugs.php and
you should be able to select and remove/update/whatever it.
Warning: Unable to connect to PostgreSQL server:
No pg_hba.conf entry for host 64.49.215.8,
user vev, database postgresql in
/usr/local/www/www/html/bugs/admin/opendb.inc on line 3
Unable to access database
Import Notes
Reference msg id not found: Pine.BSF.4.40.0203011044400.26557-100000@paprika.michvhf.com | Resolved by subject fallback
On Sat, 2002-03-02 at 04:16, Thomas Lockhart wrote:
timestamp(timestamp('a timestamp)) no longer works
I do this reasonably often in my code by way of being paranoid
that I might have a date, or a time, where I for sure _really_
want it to be a timestamp...
pcnz=# select timestamp('2002-03-01'::timestamp);
ERROR: parser: parse error at or near "'"You *can* coerce timestamps to be timestamps, but in 7.2 non-standard
syntax no longer works to do this. The reason is that "timestamp(p)" now
follows the SQL9x usage of defining a timestamp type with precision "p".
So trying to call a function "timestamp()" no longer works as it did.You can use SQL9x syntax for the type coersion:
select cast('2002-03-01'::timestamp as timestamp);
or (not recommended) you can cheat and force the call to the function by
surrounding it in double-quotes:select "timestamp"('2002-03-01'::timestamp);
Thanks Thomas,
I wasn't aware of that SQL9x timestamp precision, which was why it
seemed like a strange change to me.
Sorry to have not read the migration issues before filing this - I
thought from following these mailing lists that I knew them already :-)
Cheers,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?