Thread: Can't "EXTRACT" from a field?

Can't "EXTRACT" from a field?

From
"Marc G. Fournier"
Date:
Simple, really ... using v7.2b3 that hasn't been released yet ...

traf_stats=# select EXTRACT(WEEK FROM TIMESTAMP runtime) from hourly_stats;
ERROR:  parser: parse error at or near "runtime"
traf_stats=# \d hourly_stats                Table "hourly_stats"  Column   |            Type             | Modifiers
------------+-----------------------------+-----------from_ip    | inet                        |to_ip      | inet
                |port       | integer                     |bytes      | bigint                      |runtime    |
timestamp(6)with time zone |no_records | integer                     |
 
Indexes: hourly_from_ip,        hourly_to_ip




Re: Can't "EXTRACT" from a field?

From
Brent Verner
Date:
On 22 Nov 2001 at 10:02 (-0500), Marc G. Fournier wrote:
| 
| Simple, really ... using v7.2b3 that hasn't been released yet ...
| 
| traf_stats=# select EXTRACT(WEEK FROM TIMESTAMP runtime) from hourly_stats;
| ERROR:  parser: parse error at or near "runtime"

The following works for me (on 7.2b3).
 create table test( id serial, tid timestamp default now() ); select extract(week from tid) from test;


gram.y has extract_list:  extract_arg FROM a_expr

which appears to be in keeping with the sql99 def. part2-         <extract expression> ::= part2:              EXTRACT
<leftparen> <extract field> part2-                  FROM <extract source> <right paren>
 


I don't know if there was ever any other format for extract(), but
things look normal from here.


btw Marc, can you help me in getting archives of the various lists?

cheers. brent

-- 
"Develop your talent, man, and leave the world something. Records are 
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing."  -- Duane Allman


Re: Can't "EXTRACT" from a field?

From
"Marc G. Fournier"
Date:
On Thu, 22 Nov 2001, Brent Verner wrote:

> On 22 Nov 2001 at 10:02 (-0500), Marc G. Fournier wrote:
> |
> | Simple, really ... using v7.2b3 that hasn't been released yet ...
> |
> | traf_stats=# select EXTRACT(WEEK FROM TIMESTAMP runtime) from hourly_stats;
> | ERROR:  parser: parse error at or near "runtime"
>
> The following works for me (on 7.2b3).
>
>   create table test( id serial, tid timestamp default now() );
>   select extract(week from tid) from test;
>
>
> gram.y has
>   extract_list:  extract_arg FROM a_expr
>
> which appears to be in keeping with the sql99 def.
>   part2-         <extract expression> ::=
>   part2:              EXTRACT <left paren> <extract field>
>   part2-                  FROM <extract source> <right paren>
>
>
> I don't know if there was ever any other format for extract(), but
> things look normal from here.

ya, I hadn't clued in until fighting with it some more that if the fieldis
already a timestamp, yyou don't have to put it in as 'EXTRACT(WEEK FROM
TIMESTAMP tid) :(


> btw Marc, can you help me in getting archives of the various lists?

they are all at archives.postgresql.org ... no?




Re: Can't "EXTRACT" from a field?

From
Brent Verner
Date:
On 22 Nov 2001 at 11:18 (-0500), Marc G. Fournier wrote:
| On Thu, 22 Nov 2001, Brent Verner wrote:
| 
| > On 22 Nov 2001 at 10:02 (-0500), Marc G. Fournier wrote:
| > |
| > | Simple, really ... using v7.2b3 that hasn't been released yet ...
| > |
| > | traf_stats=# select EXTRACT(WEEK FROM TIMESTAMP runtime) from hourly_stats;
| > | ERROR:  parser: parse error at or near "runtime"
| >
| > The following works for me (on 7.2b3).
| >
| >   create table test( id serial, tid timestamp default now() );
| >   select extract(week from tid) from test;
| >
| >
| > gram.y has
| >   extract_list:  extract_arg FROM a_expr
| >
| > which appears to be in keeping with the sql99 def.
| >   part2-         <extract expression> ::=
| >   part2:              EXTRACT <left paren> <extract field>
| >   part2-                  FROM <extract source> <right paren>
| >
| >
| > I don't know if there was ever any other format for extract(), but
| > things look normal from here.
| 
| ya, I hadn't clued in until fighting with it some more that if the fieldis
| already a timestamp, yyou don't have to put it in as 'EXTRACT(WEEK FROM
| TIMESTAMP tid) :(

I didn't even know what the args to extract were, which is how I ended
up in gram.y...

note: extract() is correctly not listed as a function, but it doesn't      have any '\h' help available.  Is this a
TODOkind-of-thing?  If     it is I can try to add it sometime later today.  There is not
 
note2: This documentation is incorrect as of last night's cvs.     an example given in the docs is.       SELECT
EXTRACT(CENTURYFROM TIMESTAMP '2001-02-16 20:38:40');     I can go ahead and update func.sgml if noone else is already
  getting it.
 

| > btw Marc, can you help me in getting archives of the various lists?
| 
| they are all at archives.postgresql.org ... no?

Not that I see.  archives. points at www2.us.

I ftp'd around every postgresql.org (and hub.org) anonftp server
I could find, and sent an 'index' command to majordomo, and it has
/some/ files listed, but I could not ever retreive those files...

I'm really looking forward to having locally (mutt!!) searchable
docs...

Did you ever get the documentation issue worked out?  Is there anything
I can do to help with that?  I did get man.tar and postgres.tar built
from cvs on my debian box.

Thanks, Brent

-- 
"Develop your talent, man, and leave the world something. Records are 
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing."  -- Duane Allman


Re: Can't "EXTRACT" from a field?

From
Brent Verner
Date:
On 22 Nov 2001 at 11:18 (-0500), Marc G. Fournier wrote:
| 
| ya, I hadn't clued in until fighting with it some more that if the fieldis
| already a timestamp, yyou don't have to put it in as 'EXTRACT(WEEK FROM
| TIMESTAMP tid) :(

Ok, scratch my previous email WRT the sgml docs being wrong...

Something is strange, tho.

brent=# select extract( week from timestamp ('2001-02-06 20:38:40'::timestamp)>
ERROR:  parser: parse error at or near "'"
brent=# select extract( week from "timestamp" ('2001-02-06 20:38:40'::timestam>date_part 
-----------        6
(1 row)

brent=# select extract( week from timestamp ('2001-02-06 20:38:40') );
ERROR:  parser: parse error at or near "'"
brent=# select extract( week from timestamp '2001-02-06 20:38:40'::timestamp );date_part 
-----------        6
(1 row)

brent=# select extract( week from timestamp '2001-02-06 20:38:40' );date_part 
-----------        6
(1 row)

Notice: timestamp( type )         => fail "timestamp"( type )       => OK timestamp type            => OK timestamp
column_of_type => fail     [1] Marc's original observation.
 


can't help any more... brent

-- 
"Develop your talent, man, and leave the world something. Records are 
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing."  -- Duane Allman


Re: Can't "EXTRACT" from a field?

From
Tom Lane
Date:
Brent Verner <brent@rcfile.org> writes:
> note: extract() is correctly not listed as a function, but it doesn't 
>       have any '\h' help available.  Is this a TODO kind-of-thing?  If
>       it is I can try to add it sometime later today.

There is no mechanism for keeping track of help entries for functions,
only statements.

> note2: This documentation is incorrect as of last night's cvs.
>       an example given in the docs is.
>         SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');

This example is fine.  Try it.
        regards, tom lane


Re: Can't "EXTRACT" from a field?

From
Tom Lane
Date:
Brent Verner <brent@rcfile.org> writes:
> Something is strange, tho.

The construct you're poking around the edges of here is
    type-name  literal-string

which is an SQLish typed constant.  (Or more accurately, it's Thomas'
generalization of some type-specific constant syntaxes that appear in
SQL92.  AFAIK the spec itself doesn't claim this is a type-universal
construction.)

Casting something other than a string literal requires different, more
explicit syntax; eg, CAST(foo AS type), foo::type, or if the type name
is allowable as a function name type(foo) will work.
        regards, tom lane