Full outer join in postgres

Question

I have the following table in Postgresql:

sch_code   sch_level   start_date   end_date      flag
1234          P        01-01-2018   31-01-2018    V
1234          S        01-01-2018   31-01-2018    V
5678          S        01-01-2018   31-01-2018    V
8965          P        01-01-2018   31-01-2018    V

The result which I require is as follows.

sch_code    start_P         end_P     start_S     end_S
1234        01-01-2018   31-01-2018   01-01-2018   31-01-2018  
5678        00-00-0000   00-00-0000   01-01-2018   31-01-2018  
8965        01-01-2018   31-01-2018   00-00-0000   00-00-0000

The queries which i tried with UNION did not provide a result. I have also tried using looped select statements.

score 0 · Answer 1 · answered May 10 '18 at 11:43

You need a FULL OUTER JOIN (see guide here).
And since you can't use a WHERE filter due to potential NULL values, you have to pre-filter the 2 data sets using CTEs (see guide here).

WITH pdata AS (
  SELECT * FROM mytable WHERE sch_level='P'
),
sdata AS (
  SELECT * FROM mytable WHERE sch_level='S'
)
SELECT
  COALESCE(pdata.sch_code, sdata.sch_code) AS sch_code,
  pdata.start_date AS start_P,
  pdata.end_date   AS end_P,
  sdata.start_date AS start_S,
  pdata.end_date   AS end_S
FROM pdata
FULL OUTER JOIN sdata ON pdata.sch_code = sdata.sch_code

If you don't want NULL values in your date fields, simply use COALESCE and provide default values in whichever data type you might be using.

Full outer join in postgres

1 Answers1