Forum OpenACS Q&A: Response to Postgres 7.1 beta 1 problems

Collapse
Posted by Don Baccus on
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=#