Forum OpenACS Q&A: Porting Oracle's (+) operator to pg.

Collapse
Posted by Nick Carroll on
Hi guys,

I need help porting the following query from Oracle to Postgresql. In particular the (+) operator. I am not sure how to perform the equivalent LEFT OUTER JOIN in PostgreSQL. I have checked out the porting guides, but they suggest using a UNION, and other postings have examples using LEFT JOINS. Which method is more widely accepted?

Thanks in advance, Nick.

CREATE OR REPLACE VIEW n_sessions_day_user AS
SELECT b.date_id, b.n_sessions_day,
nvl(a.n_users, 0) AS members, b.n_users AS non_members
FROM visits_grouped a, visits_grouped b
WHERE b.n_sessions_day = a.n_sessions_day(+)
AND b.date_id = a.date_id(+)
AND 1 = a.member_p(+)
AND b.member_p = 0;

Collapse
Posted by Jonathan Ellis on
the porting guide was written back in the bad old days of PG 6.

PG >= 7.0 supports the ANSI outer join syntax:

CREATE OR REPLACE VIEW n_sessions_day_user AS 
SELECT b.date_id, b.n_sessions_day, 
coalesce(a.n_users, 0) AS members, b.n_users AS non_members 
FROM visits_grouped b left outer join visits_grouped a  
on (b.n_sessions_day = a.n_sessions_day
    AND b.date_id = a.date_id
    AND 1 = a.member_p)
WHERE b.member_p = 0; 
Collapse
Posted by Michael A. Cleverly on
Outer Joins are available in Postgres beginning with version 7.1.
Collapse
Posted by Nick Carroll on
Thanks guys, that simplifies things a lot.

Nick.