PostgreSQL: FULL OUTER JOIN with partitioned tables

Options
UserBird
UserBird Dataiker, Alpha Tester Posts: 535 Dataiker
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?

Answers

  • jereze
    jereze Alpha Tester, Dataiker Alumni Posts: 190 ✭✭✭✭✭✭✭✭
    edited July 17
    Options

    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'

  • jrouquie
    jrouquie Dataiker Alumni Posts: 87 ✭✭✭✭✭✭✭
    Options
    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.
Setup Info
    Tags
      Help me…