pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFull outer join
7+ messages / 2 participants
[nested] [flat]
* Full outer join
@ 2001-04-12 18:09 Tim Perdue <tim@perdue.net>
2001-04-13 07:02 ` Re: Full outer join Tom Lane <tgl@sss.pgh.pa.us>
2001-04-15 00:50 ` Re: Full outer join Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: Tim Perdue @ 2001-04-12 18:09 UTC (permalink / raw)
To: pgsql-sql
Looking at the docs for 7.1, it appears that full outer joins are supported:
http://www.postgresql.org/devel-corner/docs/user/queries.html
However, I keep getting this error, and I don't know what it means:
ERROR: FULL JOIN is only supported with mergejoinable join conditions
I've tried several variations and keep getting the same error.
Anyone have any ideas?
Details below.
Thanks,
Tim
SELECT *
FROM (stats_http_downloads sh
FULL OUTER JOIN stats_ftp_downloads sf USING (day,filerelease_id)) ;
ERROR: FULL JOIN is only supported with mergejoinable join conditions
SELECT day,filerelease_id
FROM (stats_http_downloads sh
FULL OUTER JOIN stats_ftp_downloads sf USING (day,filerelease_id)) ;
ERROR: FULL JOIN is only supported with mergejoinable join conditions
alexandria=# \d stats_http_downloads
Table "stats_http_downloads"
Attribute | Type | Modifier
----------------+---------+----------------------
day | integer | not null default '0'
filerelease_id | integer | not null default '0'
group_id | integer | not null default '0'
downloads | integer | not null default '0'
alexandria=# \d stats_ftp_downloads
Table "stats_ftp_downloads"
Attribute | Type | Modifier
----------------+---------+----------------------
day | integer | not null default '0'
filerelease_id | integer | not null default '0'
group_id | integer | not null default '0'
downloads | integer | not null default '0'
--
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Full outer join
2001-04-12 18:09 Full outer join Tim Perdue <tim@perdue.net>
@ 2001-04-13 07:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-13 13:23 ` Re: Full outer join Tim Perdue <tim@perdue.net>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2001-04-13 07:02 UTC (permalink / raw)
To: Tim Perdue <tim@perdue.net>; +Cc: pgsql-sql
Tim Perdue <tim@perdue.net> writes:
> However, I keep getting this error, and I don't know what it means:
> ERROR: FULL JOIN is only supported with mergejoinable join conditions
Works for me:
regression=# create table stats_http_downloads (day int, filerelease_id int);
CREATE
regression=# create table stats_ftp_downloads (day int, filerelease_id int);
CREATE
regression=# SELECT * FROM (stats_http_downloads sh
regression(# FULL OUTER JOIN stats_ftp_downloads sf USING (day,filerelease_id));
day | filerelease_id
-----+----------------
(0 rows)
What version are you using?
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Full outer join
2001-04-12 18:09 Full outer join Tim Perdue <tim@perdue.net>
2001-04-13 07:02 ` Re: Full outer join Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-04-13 13:23 ` Tim Perdue <tim@perdue.net>
2001-04-13 15:11 ` Re: Full outer join Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Tim Perdue @ 2001-04-13 13:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Fri, Apr 13, 2001 at 03:02:32AM -0400, Tom Lane wrote:
> Tim Perdue <tim@perdue.net> writes:
> > However, I keep getting this error, and I don't know what it means:
> > ERROR: FULL JOIN is only supported with mergejoinable join conditions
>
> Works for me:
>
> regression=# create table stats_http_downloads (day int, filerelease_id int);
create table stats_http_downloads (day int, filerelease_id int,group_id int,
downloads int);
> CREATE
> regression=# create table stats_ftp_downloads (day int, filerelease_id int);
create table stats_ftp_downloads (day int, filerelease_id int,group_id int,
downloads int);
> CREATE
> regression=# SELECT * FROM (stats_http_downloads sh
> regression(# FULL OUTER JOIN stats_ftp_downloads sf USING (day,filerelease_id));
> day | filerelease_id
> -----+----------------
> (0 rows)
>
>
> What version are you using?
That's RC3 and RC1.
I wonder if the problem is because stats_http_downloads and
stats_ftp_downloads both have group_id and downloads, which I don't want to
use as part of the join.
Does it still work with those revised CREATE statements?
Tim
--
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Full outer join
2001-04-12 18:09 Full outer join Tim Perdue <tim@perdue.net>
2001-04-13 07:02 ` Re: Full outer join Tom Lane <tgl@sss.pgh.pa.us>
2001-04-13 13:23 ` Re: Full outer join Tim Perdue <tim@perdue.net>
@ 2001-04-13 15:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-13 15:34 ` Re: Full outer join Tim Perdue <tim@perdue.net>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2001-04-13 15:11 UTC (permalink / raw)
To: Tim Perdue <tim@perdue.net>; +Cc: pgsql-sql
Tim Perdue <tim@perdue.net> writes:
> Does it still work with those revised CREATE statements?
Yes, works fine here.
>> What version are you using?
> That's RC3 and RC1.
Curious. I'd expect this to happen for column datatypes whose '='
operator isn't marked mergejoinable, but plain old int certainly doesn't
have that problem. I think there's something you haven't told us. Is
either of these tables actually a view?
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Full outer join
2001-04-12 18:09 Full outer join Tim Perdue <tim@perdue.net>
2001-04-13 07:02 ` Re: Full outer join Tom Lane <tgl@sss.pgh.pa.us>
2001-04-13 13:23 ` Re: Full outer join Tim Perdue <tim@perdue.net>
2001-04-13 15:11 ` Re: Full outer join Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-04-13 15:34 ` Tim Perdue <tim@perdue.net>
2001-04-13 15:46 ` Re: Full outer join Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Tim Perdue @ 2001-04-13 15:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Tim Perdue <tim@perdue.net>; pgsql-sql
On Fri, Apr 13, 2001 at 11:11:26AM -0400, Tom Lane wrote:
> Tim Perdue <tim@perdue.net> writes:
> > Does it still work with those revised CREATE statements?
>
> Yes, works fine here.
>
> >> What version are you using?
>
> > That's RC3 and RC1.
>
> Curious. I'd expect this to happen for column datatypes whose '='
> operator isn't marked mergejoinable, but plain old int certainly doesn't
> have that problem. I think there's something you haven't told us. Is
> either of these tables actually a view?
Hehe - no. I sent the \d of both tables at the bottom of that email.
Tim
--
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Full outer join
2001-04-12 18:09 Full outer join Tim Perdue <tim@perdue.net>
2001-04-13 07:02 ` Re: Full outer join Tom Lane <tgl@sss.pgh.pa.us>
2001-04-13 13:23 ` Re: Full outer join Tim Perdue <tim@perdue.net>
2001-04-13 15:11 ` Re: Full outer join Tom Lane <tgl@sss.pgh.pa.us>
2001-04-13 15:34 ` Re: Full outer join Tim Perdue <tim@perdue.net>
@ 2001-04-13 15:46 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2001-04-13 15:46 UTC (permalink / raw)
To: Tim Perdue <tim@perdue.net>; +Cc: pgsql-sql
Tim Perdue <tim@perdue.net> writes:
>> Is either of these tables actually a view?
> Hehe - no. I sent the \d of both tables at the bottom of that email.
\d isn't very helpful for these sorts of reports. How about pg_dump -s ?
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Full outer join
2001-04-12 18:09 Full outer join Tim Perdue <tim@perdue.net>
@ 2001-04-15 00:50 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2001-04-15 00:50 UTC (permalink / raw)
To: Tim Perdue <tim@perdue.net>; +Cc: pgsql-sql
Tim Perdue <tim@perdue.net> writes:
> SELECT *
> FROM (stats_http_downloads sh
> FULL OUTER JOIN stats_ftp_downloads sf USING (day,filerelease_id)) ;
> ERROR: FULL JOIN is only supported with mergejoinable join conditions
I've committed a fix for this problem. It'll appear in 7.1.1, or you
can grab CVS or a nightly snapshot if you are in a hurry.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2001-04-15 00:50 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-12 18:09 Full outer join Tim Perdue <tim@perdue.net>
2001-04-13 07:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-13 13:23 ` Tim Perdue <tim@perdue.net>
2001-04-13 15:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-13 15:34 ` Tim Perdue <tim@perdue.net>
2001-04-13 15:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-15 00:50 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox