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


Fri,  1 Mar 2002 04:50:26 -0500 (EST)
pgsql-bugs@postgresql.org
Bug #605:  timestamp(timestamp('a timestamp)) no longer works
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

Fri, 01 Mar 2002 10:03:41 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug #605: timestamp(timestamp('a timestamp)) no longer works 
> 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

Fri, 01 Mar 2002 15:16:23 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: Bug #605:  timestamp(timestamp('a timestamp)) no longer works
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

Fri, 01 Mar 2002 15:21:01 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: Bug #605:  timestamp(timestamp('a timestamp)) no longer works
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?


02 Mar 2002 12:56:22 +1300
Andrew McMillan <andrew@catalyst.net.nz>
Re: Bug #605:  timestamp(timestamp('a timestamp)) no longer works