4

My table, for simplicity, looks like this:

id | foreign_key_1 | foreign_key_2 | value

As clear from the names, the fields foreign_key_1 and foreign_key_2 refer to PKs in two other tables.

The tricky thing here is that only one of them can be not null at the same time so I also have the following constraints:

UNIQUE (foreign_key_1),
UNIQUE (foreign_key_2),
CHECK ((foreign_key_1 IS NOT NULL AND foreign_key_2 IS NULL) 
    OR (foreign_key_1 IS NULL AND foreign_key_2 IS NOT NULL))

These checks guarantee the integrity I wanted but now I also want to delete the existing entry by either foreign_key_1 or foreign_key_2 before inserting a new entry.

DELETE FROM my_table
WHERE ($1::INTEGER IS NULL OR foreign_key_1 = $1::INTEGER)
    AND ($2::INTEGER is NULL OR foreign_key_2 = $2::INTEGER)

Initially, the idea was to have just one query that could handle both (foreign_key_1, NULL) and (NULL, foreign_key_2) passed as arguments.

The main concern is that executing the delete statement might succeed whereas the insert might fail, and then the table will be in an invalid state.

It seems that using a transaction here is the way to go, but from the code organization perspective, it will require much refactoring now to wrap both calls to repositories in the same transaction.

My next idea was using an UPSERT and ON CONFLICT(target) DO UPDATE...

I don't have a single constraint here to use as a target and the version that I am using (Postgres 14) does not yet have the clause NULLS NOT DISTINCT.

To reiterate, I would like to have an intersection of:

  • UNIQUE(foreign_key_1)
  • UNIQUE(foreign_key_2)
  • CHECK ((foreign_key_1 IS NOT NULL AND foreign_key_2 IS NULL) OR (foreign_key_1 IS NULL AND foreign_key_2 IS NOT NULL))

... so that it can be used as a target in ON CONFLICT while doing an UPSERT. Is that possible to achieve somehow?

Overall, my understanding is that in this situation the options are:

  • Refactoring and executing the two statements in one transaction [a lot of refactoring]
  • Finding a way to have a single target for the ON CONFLICT clause
  • Coming up with a clever CTE that will delete the existing row first
  • something else

What are your suggestions?

Update

After some thinking, I can probably use:

CREATE UNIQUE INDEX one_null_idx ON my_table(COALESCE(foreign_key_1, -1), COALESCE(foreign_key_2, -1));

The check constraint to guarantee only one non-null foreign key is also necessary here though.

Given that the foreign keys are serial and can never be -1, looks like this could work. What do you think?

4
  • 1
    I think your idea will work. Nov 30, 2022 at 7:21
  • Hi, thank you for replying. You mean the one with COALESCE?
    – Don Draper
    Nov 30, 2022 at 7:24
  • 2
    Yes, your update. Nov 30, 2022 at 7:25
  • Thank you very much!
    – Don Draper
    Nov 30, 2022 at 7:25

1 Answer 1

3

You need a single "conflict_target" for ON CONFLICT DO UPDATE, so your "Update" solution is the best workaround:

CREATE UNIQUE INDEX my_table_one_null_idx ON my_table(COALESCE(foreign_key_1, -1), COALESCE(foreign_key_2, -1));

... while you can't use NULLS NOT DISTINCT in Postgres 15. See:

But:

Given that the foreign keys are serial and can never be -1,

serial columns are never negative by default. But nothing keeps you from entering a negative number manually. Enforce that with CHECK constraints. Plus, use the simpler num_nulls() for your other constraint while being at it:

...
, CONSTRAINT fk1_never_minus1 CHECK (foreign_key_1 <> -1)
, CONSTRAINT fk2_never_minus1 CHECK (foreign_key_2 <> -1)
, CONSTRAINT fk1_fk2_one_null CHECK (num_nulls(foreign_key_1, foreign_key_2) = 1)
...

See:

Alternative

There is another alternative with multiple CTEs in a single query. See:

2
  • Thank you very much! Didn't even know about the existence of num_nulls :) Looks great!
    – Don Draper
    Nov 30, 2022 at 9:36
  • 1
    @DonDraper: I remember having solved the same without expression index. See the link in the added alternative. Dec 2, 2022 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.