Обсуждение: 9.2 timestamp function syntax error

I don't understand the error resulting from the following progression on
9.2 (specifically "EnterpriseDB 9.2.1.3 on x86_64-unknown-linux-gnu,
compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit"):

select sysdate => timestamp without time zone

select timestamptz(sysdate) => timestamp with time zone

select timestamp(timestamptz(sysdate)) => ERROR:  syntax error at or
near "timestamptz"

OR

select timestamp(sysdate::timestamptz)) => ERROR:  syntax error at or
near "sysdate"

I see a function in pg_catalog with signature timestamp(timestamp with
time zone).  Why isn't it being applied?

Thanks.

--
Guy Rouillier

Guy Rouillier wrote:
> I don't understand the error resulting from the following progression on
> 9.2 (specifically "EnterpriseDB 9.2.1.3 on x86_64-unknown-linux-gnu,
> compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit"):
>
> select sysdate => timestamp without time zone
>
> select timestamptz(sysdate) => timestamp with time zone
>
> select timestamp(timestamptz(sysdate)) => ERROR:  syntax error at or
> near "timestamptz"
>
> OR
>
> select timestamp(sysdate::timestamptz)) => ERROR:  syntax error at or
> near "sysdate"
>
> I see a function in pg_catalog with signature timestamp(timestamp with
> time zone).  Why isn't it being applied?

That must be EnterpriseDB's proprietary Postgres Plus, since
regular PostgreSQL doesn't have "sysdate".

Try to ask EnterpriseDB, they are more likely to be able to help.

Yours,
Laurenz Albe

On 03/05/2013 12:30 AM, Albe Laurenz wrote:
> Guy Rouillier wrote:
>> I don't understand the error resulting from the following progression on
>> 9.2 (specifically "EnterpriseDB 9.2.1.3 on x86_64-unknown-linux-gnu,
>> compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit"):
>>
>> select sysdate => timestamp without time zone
>>
>> select timestamptz(sysdate) => timestamp with time zone
>>
>> select timestamp(timestamptz(sysdate)) => ERROR:  syntax error at or
>> near "timestamptz"
>>
>> OR
>>
>> select timestamp(sysdate::timestamptz)) => ERROR:  syntax error at or
>> near "sysdate"
>>
>> I see a function in pg_catalog with signature timestamp(timestamp with
>> time zone).  Why isn't it being applied?
>
> That must be EnterpriseDB's proprietary Postgres Plus, since
> regular PostgreSQL doesn't have "sysdate".

Actually that is not the issue.

test=> select version();
                                                            version

----------------------
  PostgreSQL 9.2.3

This does not work:

test=> select timestamp(now()::timestampz);
ERROR:  syntax error at or near "now"
LINE 1: select timestamp(now()::timestampz);

test=> select timestamp(now());
ERROR:  syntax error at or near "now"
LINE 1: select timestamp(now());


This does:

test=> select (now()::timestamptz)::timestamp;
             now
----------------------------
  2013-03-05 06:23:05.169524
(1 row)


test=> select now()::timestamp;
             now
---------------------------
  2013-03-05 06:24:43.50932
(1 row)



>
> Try to ask EnterpriseDB, they are more likely to be able to help.
>
> Yours,
> Laurenz Albe
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

Adrian Klaver <adrian.klaver@gmail.com> writes:
> This does not work:

> test=> select timestamp(now()::timestampz);
> ERROR:  syntax error at or near "now"

timestamp(something) is a type name.  Per the comment in gram.y:

 * The type names appearing here are not usable as function names
 * because they can be followed by '(' in typename productions, which
 * looks too much like a function call for an LR(1) parser.

            regards, tom lane

On 3/5/2013 9:52 AM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@gmail.com> writes:
>> This does not work:
>
>> test=> select timestamp(now()::timestampz);
>> ERROR:  syntax error at or near "now"
>
> timestamp(something) is a type name.  Per the comment in gram.y:
>
>   * The type names appearing here are not usable as function names
>   * because they can be followed by '(' in typename productions, which
>   * looks too much like a function call for an LR(1) parser.

In PgAdmin, here is how timestamp() is defined:

CREATE OR REPLACE FUNCTION "timestamp"(timestamp with time zone)
   RETURNS timestamp without time zone AS
'timestamptz_timestamp'
   LANGUAGE internal STABLE STRICT
   COST 1;
ALTER FUNCTION "timestamp"(timestamp with time zone)
   OWNER TO postgres;
COMMENT ON FUNCTION "timestamp"(timestamp with time zone) IS 'convert
timestamp with time zone to timestamp';

and here is now timestamptz() is defined:

CREATE OR REPLACE FUNCTION timestamptz(timestamp without time zone)
   RETURNS timestamp with time zone AS
'timestamp_timestamptz'
   LANGUAGE internal STABLE STRICT
   COST 1;
ALTER FUNCTION timestamptz(timestamp without time zone)
   OWNER TO postgres;
COMMENT ON FUNCTION timestamptz(timestamp without time zone) IS 'convert
timestamp to timestamp with time zone';

Ugh, I just noticed the quotation marks around the timestamp function.
This works:

select "timestamp"(now()::timestamptz); => timestamp without time zone

This is a subtlety bound to be lost on most.  Why is there both a
function and a type name with the same name?  I suppose I could define a
synonym to make the function name distinct, but this seems like
something that should be addressed.

Thanks.

--
Guy Rouillier

Guy Rouillier <guy.rouillier@gmail.com> writes:
> Ugh, I just noticed the quotation marks around the timestamp function.
> This works:

> select "timestamp"(now()::timestamptz); => timestamp without time zone

> This is a subtlety bound to be lost on most.  Why is there both a
> function and a type name with the same name?

All cast functions are named after the target type, by convention.

> I suppose I could define a
> synonym to make the function name distinct, but this seems like
> something that should be addressed.

Really the right way to address it is to use cast notation.  The
function notation is sometimes convenient, but you have to worry
about gotchas like this one, not to mention that some built-in
types have multiple aliases.

            regards, tom lane