Thread: 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 afterme :-) 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
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?