New to Dataiku DSS? Try out our NEW Quick Start Programs today and get onboarded on the product in just one hour! Let's go

PostgreSQL: FULL OUTER JOIN with partitioned tables

UserBird
Dataiker
Dataiker
PostgreSQL: FULL OUTER JOIN with partitioned tables
I have 2 datasets (PostgreSQL) to join: "input1", "input2" and the result should be "output".
All datasets are partitioned by date.

How to make a FULL OUTER JOIN and keep the partition?
2 Replies
jereze
Dataiker
Dataiker

Here it is (with the partition named "date"):




select
COALESCE("input1"."date", "input2"."date") as "date",
...

from "input1"
full outer join "input2"
... and "input1"."date" = "input2"."date"

where
"input1"."date" = '$DKU_DST_date' or "input2"."date" = '$DKU_DST_date'


 

Jeremy, Product Manager at Dataiku
jrouquie
Dataiker Alumni
Side note: since it's an OUTER join, SQL keeps all lines from both datasets, even ones that don't match the ON clause, putting NULL values in columns from the other dataset. So adding a condition like "date" = '$DKU_DST_date' in the ON clause does not works, one needs to add it in the WHERE clause.
0 Kudos
Labels (3)
A banner prompting to get Dataiku DSS