Обсуждение: Reference Manual
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'
> 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)
> 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
> 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
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'
> 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
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'
> > 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
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'