Hi, all

I'm writing Reference Manual and I have two questions:

1 - Why PostgreSQL accept HOUR, MINUTE and SECOND to extract if from a
    date ?

   EXTRACT (field FROM date_expression)

   There are no such fields on a date!
    
   It make sense only for SQL92 syntax because it uses also time and interval
   types:

   EXTRACT -- extract a datetime field from a datetime or interval.

        The possible values for field are:
           - YEAR
           - MONTH
           - DAY
           - HOUR
           - MINUTE
           - SECOND
           - TIMEZONE_HOUR
           - TIMEZONE_MINUTE

------------------------------------------------------------------------

2. - Seems that optional ALL keyword of UNION doesn't work.
     The following query prints always the same result with and without
     the ALL clause.

*    UNION of two tables:

mytable:            yourtable:
         id|name               id|name
         --+------             --+------
          1|Smith               1|Soares
          2|Jones               2|Panini
          3|Soares


SELECT mytable.id, mytable.name
FROM mytable
WHERE mytable.name LIKE 'S%'
   UNION
       SELECT yourtable.id, yourtable.name
       FROM yourtable
       WHERE yourtable.name LIKE 'S%';

this is the result even if I don't specify ALL.
                     id|name
                     --+------
                      1|Smith
                      1|Soares
                      3|Soares
---------
SQL92 says that result does not contain any duplicate rows anless
      the ALL keyword is specified.		      

What's wrong with my example ?
                                                  Thanks, Jose'


Mon, 30 Mar 1998 15:19:05 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@proxy.bazzanese.com>
Reference Manual
> 2. - Seems that optional ALL keyword of UNION doesn't work.
>      The following query prints always the same result with and without
>      the ALL clause.
> 
> *    UNION of two tables:
> 
> mytable:            yourtable:
>          id|name               id|name
>          --+------             --+------
>           1|Smith               1|Soares
>           2|Jones               2|Panini
>           3|Soares
> 
> 
> SELECT mytable.id, mytable.name
> FROM mytable
> WHERE mytable.name LIKE 'S%'
>    UNION
>        SELECT yourtable.id, yourtable.name
>        FROM yourtable
>        WHERE yourtable.name LIKE 'S%';
> 
> this is the result even if I don't specify ALL.
>                      id|name
>                      --+------
>                       1|Smith
>                       1|Soares
>                       3|Soares

The second column is duplicate, but the first is not.  It looks at all
columns to determine duplicates.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Mon, 30 Mar 1998 10:25:03 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Reference Manual]
> I'm writing Reference Manual and I have two questions:
> 
> 1 - Why PostgreSQL accept HOUR, MINUTE and SECOND to extract if from a
>     date ?
>    EXTRACT (field FROM date_expression)
>    There are no such fields on a date!

And it returns zeros for those fields. I think that is OK; it makes for
a symmetric implementation...

>            - TIMEZONE_HOUR
>            - TIMEZONE_MINUTE

Hmm. Don't do these yet. But:

tgl=> select date_part('timezone', 'now');
date_part
---------
    -7200
(1 row)

so the underlying implementation does know about timezones. It may only
need a parser adjustment. Will look at it...

                        - Tom

Mon, 30 Mar 1998 16:17:17 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [DOCS] Reference Manual
> so the underlying implementation does know about timezones. It may 
> only need a parser adjustment. Will look at it...

tgl=> select extract(timezone_hour from datetime 'now');

date_part
---------
       -1
(1 row)

tgl=> show timezone;
NOTICE:  Time zone is GMT-1
SHOW VARIABLE

Was more than a parser adjustment, but not too bad. 
Will be in v6.4 :)

                     - Tom

Mon, 30 Mar 1998 16:55:03 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [DOCS] Reference Manual
> The point is: why EXTRACT accepts only date types ?
> SQL92 specifies date, time, timestamp and interval.

tgl=> select extract (year from date 'now');
date_part
---------
     1998
(1 row)
tgl=> select extract (year from datetime 'now');
date_part
---------
     1998
(1 row)
tgl=> select extract (year from abstime 'now');
date_part
---------
     1998
(1 row)
tgl=> select extract(year from timestamp 'now');
date_part
---------
     1998
(1 row)
tgl=> select extract (hour from timespan '5 hours');
date_part
---------
        5
(1 row)

tgl=> select extract (hour from reltime '5 hours');
date_part
---------
        5
(1 row)
tgl=> select extract (hour from interval '5 hours');
date_part
---------
        5
(1 row)

And,

tgl=> select extract (hour from time '03:04:05');
ERROR:  function 'time_timespan(time)' does not exist

This is a known problem; will fix for v6.4.

                       - Tom

Wed, 01 Apr 1998 14:33:42 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Re: [DOCS] Reference Manual
On Mon, 30 Mar 1998, Thomas G. Lockhart wrote:

> > I'm writing Reference Manual and I have two questions:
> > 
> > 1 - Why PostgreSQL accept HOUR, MINUTE and SECOND to extract if from a
> >     date ?
> >    EXTRACT (field FROM date_expression)
> >    There are no such fields on a date!
> 
> And it returns zeros for those fields. I think that is OK; it makes for
> a symmetric implementation...
> 
The point is: why EXTRACT accepts only date types ?

SQL92 specifies date, time, timestamp and interval.

                                                            Ciao, Jose'


Wed, 1 Apr 1998 14:46:48 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@proxy.bazzanese.com>
Re: [HACKERS] Re: [DOCS] Reference Manual
> > tgl=> select extract (year from date 'now');
> > date_part
> > ---------
> >      1998
> > (1 row)
> It doesn't work for me.  Why ??
> psql=> select extract (year from current_timestamp);
> ERROR:  function date_part(unknown, timestamp) does not exist

What version of Postgres are you running? Something may have gone a
little screwy in v6.3.1, since the numerology regression test has been
reported to have failed with it unable to compare an int4 to a float8. 

It must work for some installations though since they wouldn't have
released without a clean regression test, right? :)

I'm still developing with v6.3 because I'm in the middle of working on
the automatic type conversion stuff...

                      - Tom

Wed, 01 Apr 1998 16:08:30 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Re: [DOCS] Reference Manual
On Wed, 1 Apr 1998, Thomas G. Lockhart wrote:

> > The point is: why EXTRACT accepts only date types ?
> > SQL92 specifies date, time, timestamp and interval.
> 
> tgl=> select extract (year from date 'now');
> date_part
> ---------
>      1998
> (1 row)
> tgl=> select extract (year from datetime 'now');
> date_part
> ---------
>      1998
> (1 row)
> tgl=> select extract (year from abstime 'now');
> date_part
> ---------
>      1998
> (1 row)
> tgl=> select extract(year from timestamp 'now');
> date_part
> ---------
>      1998
> (1 row)
> tgl=> select extract (hour from timespan '5 hours');
> date_part
> ---------
>         5
> (1 row)
> 
> tgl=> select extract (hour from reltime '5 hours');
> date_part
> ---------
>         5
> (1 row)
> tgl=> select extract (hour from interval '5 hours');
> date_part
> ---------
>         5
> (1 row)
> 
> And,
> 
> tgl=> select extract (hour from time '03:04:05');
> ERROR:  function 'time_timespan(time)' does not exist
> 
> This is a known problem; will fix for v6.4.
> 
>                        - Tom
> 

It doesn't work for me.  Why ??

psql=> select extract (year from current_timestamp);
ERROR:  function date_part(unknown, timestamp) does not exist
psql=> select extract (hour from current_time);
ERROR:  function time_timespan(time) does not exist
psql=> select extract (minute from current_time);
ERROR:  function time_timespan(time) does not exist
psql=> select extract (second from current_time);
ERROR:  function time_timespan(time) does not exist
                                                            Ciao, Jose'


Wed, 1 Apr 1998 16:32:29 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@proxy.bazzanese.com>
Re: [HACKERS] Re: [DOCS] Reference Manual
On Wed, 1 Apr 1998, Thomas G. Lockhart wrote:

> > > tgl=> select extract (year from date 'now');
> > > date_part
> > > ---------
> > >      1998
> > > (1 row)
> > It doesn't work for me.  Why ??
> > psql=> select extract (year from current_timestamp);
> > ERROR:  function date_part(unknown, timestamp) does not exist
> 
> What version of Postgres are you running? Something may have gone a
> little screwy in v6.3.1, since the numerology regression test has been
> reported to have failed with it unable to compare an int4 to a float8. 
> 
> It must work for some installations though since they wouldn't have
> released without a clean regression test, right? :)
> 
> I'm still developing with v6.3 because I'm in the middle of working on
> the automatic type conversion stuff...
> 
I I'm running version 6.3 and my regress.out is like this:

=============== Notes...                              =================
postmaster must already be running for the regression tests to succeed.
The time zone is now set to PST8PDT explicitly by this regression test
 client frontend. Please report any apparent problems to
   ports@postgresql.org
See regress/README for more information.

=============== destroying old regression database... =================
=============== creating new regression database...   =================
=============== running regression queries...         =================
boolean .. ok
char .. ok
char2 .. ok
char4 .. ok
char8 .. ok
char16 .. ok
varchar .. ok
text .. ok
strings .. ok
int2 .. ok
int4 .. ok
oid .. ok
oidint2 .. ok
oidint4 .. ok
oidname .. ok
float4 .. ok
float8 .. ok
numerology .. ok
point .. ok
lseg .. ok
box .. ok
path .. ok
polygon .. ok
circle .. ok
geometry .. failed
timespan .. ok
datetime .. ok
reltime .. ok
abstime .. ok
tinterval .. ok
horology .. ok
comments .. ok
create_function_1 .. ok
create_type .. ok
create_table .. ok
create_function_2 .. ok
constraints .. ok
triggers .. ok
copy .. ok
create_misc .. ok
create_aggregate .. ok
create_operator .. ok
create_view .. ok
create_index .. ok
sanity_check .. ok
errors .. ok
select .. ok
select_into .. ok
select_distinct .. ok
select_distinct_on .. ok
subselect .. ok
aggregates .. ok
transactions .. ok
random .. ok
portals .. ok
misc .. ok
arrays .. ok
btree_index .. ok
hash_index .. ok
select_views .. ok
alter_table .. ok
portals_p2 .. ok
                                                      Ciao, Jose'


Thu, 2 Apr 1998 11:11:46 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@proxy.bazzanese.com>
Re: [HACKERS] Re: [DOCS] Reference Manual