Forum OpenACS Q&A: Postgres 7.1 beta 1 problems

Hello!
While I know that this isn't the luckiest forum to ask this question, I want to share a reflection.
I had installed PG 7.1 beta1 from CVS, and tried to play with OUTER JOIN. While it accepts the syntax without any squeaks, it simply doesn't work (it works just like an INNER JOIN). Having tried the UNION way of doing outer join and the SQL one(t1 NATURAL LEFT OUTER JOIN t2.....) - the first one returns good set of tuples, the second one does an inner join(even the query plans are the same.

Anyone encountered this, too?

I suspect that though you've built PG 7.1, you must be accidently running an older install of PG 7.0 (which did accept the syntax but didn't implement the outer joins). If you installed PG 7.0 earlier from RPMs, your PG 7.1 install will go elsewhere by default and if you didn't change your PG* environment variables, you'll still actually run the PG 7.0 install.

I updated PG 7.1 from CVS and rebuilt it to make sure that outer joins haven't broken in the last few weeks. Here are the results of some tests:

pgtest=# select * from one;
 i | j 
---+---
 1 | 2
 2 | 3
 4 | 4
(3 rows)

pgtest=# select * from two;
 i | k 
---+---
 2 | 2
 3 | 2
 4 | 2
 5 | 5
(4 rows)

pgtest=# select * from one left join two using (i);
 i | j | k 
---+---+---
 1 | 2 |  
 2 | 3 | 2
 4 | 4 | 2
(3 rows)

pgtest=# select * from one right join two using (i);
 i | j | k 
---+---+---
 2 | 3 | 2
 3 |   | 2
 4 | 4 | 2
 5 |   | 5
(4 rows)

pgtest=# select * from one full join two using (i);
 i | j | k 
---+---+---
 1 | 2 |  
 2 | 3 | 2
 3 |   | 2
 4 | 4 | 2
 5 |   | 5
(5 rows)

pgtest=# 

The outer joins in views are working, too, though not in more complicated queries(the ec_recommendations_cats_view is clearly too complicated and still generates errors).