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)

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

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 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'

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:

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'

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'