pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Full outer join
7+ messages / 2 participants
[nested] [flat]

* Full outer join
@ 2001-04-12 18:09  Tim Perdue <tim@perdue.net>
  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-13 07:02  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 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-13 13:23  Tim Perdue <tim@perdue.net>
  parent: 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-13 15:11  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 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-13 15:34  Tim Perdue <tim@perdue.net>
  parent: 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-13 15:46  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Tim Perdue <tim@perdue.net>
  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-15 00:50  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Tim Perdue <tim@perdue.net>
  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