5

I have the following table:

CREATE TABLE claim ( claim_number TEXT NOT NULL, line_id TEXT, process TEXT NOT NULL );

I want to add a constraint to it so that the combination of claim_number and line_id is always unique.

In most cases line_id is null and there will only be a single row for that claim number. In some cases there will be multiple rows for a given claim_number and in those cases line_id will always contain a value. The goal here is to be able to have a constraint that forces a unique combination on the (claim_number, line_id) combo so that I can use it as a conflict target in an INSERT...ON CONFLICT DO UPDATE statement so that the process column can be updated. A UNIQUE constraint won't work because it doesn't evaluate NULL = NULL, which makes sense, but isn't what I need.

I have tried adding an exclusion constraint such as:

ALTER TABLE claim ADD EXCLUDE ( claim_number WITH =, line_id WITH IS NOT DISTINCT FROM);

But that fails with:

ERROR: syntax error at or near "IS"

Is there a way to use IS NOT DISTINCT FROM in an exclusion constraint?

Improve this question
9
  • 3
    Whyis line_id NULLable? Why is it text? (it appears to be a number) Commented Aug 30, 2017 at 21:21
  • 1
    A simple workaround would be to define a default value that would be your equivalent to a NULL value that you would of course handle in your application. Also wondering why those columns are TEXT... Commented Aug 30, 2017 at 21:25
  • @wildplasser I put all IDs as TEXT, always, so that values that have leading zeros don't end up changed. For instance a SSN such as 009543219 shouldn't be stored as an INTEGER because it will lose leading zeros. Also, the data in claim_number is alphanumeric. Basically, if I'm not doing math with it then it shouldn't be a number. Commented Aug 30, 2017 at 21:31
  • @wildplasser The line_id column is nullable because I'm not in control of the source data I just need to work with it and that is how the data is when I receive it. Commented Aug 30, 2017 at 21:33
  • 2
    An exclusion constraint can't be used for an on conflict target Commented Aug 31, 2017 at 5:33

1 Answer 1

Reset to default
1

In MSSQL Server, this is done using a Filtered Unique Index, where the filter predicate is that it only indexes rows with non-NULL values.

I'm not a PostgreSQL expert, but Googling shows it's possible using a "Partial Index": https://www.postgresql.org/docs/current/static/indexes-partial.html

CREATE UNIQUE INDEX ix_claim_lines ON claim ( claim_number, line_id ) WHERE line_id IS NOT NULL
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I'm not sure that will work for this instance because I need the index in question to cover the entire table, not just part of it, in order for it to work as needed with the ON CONFLICT DO UPDATE statement.
@GregoryArenius This index does cover the entire table. This index is not used for searching or query performance, only to enforce the UNIQUE constraint for claim_number+line_id when line_id is specified. Perhaps there's a better approach than using ON CONFLICT?
@GregoryArenius: the partial index would prevent (claim_1, line_1) to be inserted a second time. But the drawback is that a partial index (like an exclusion constraint) can't be used for an on conflict clause. And you can't create a unique constraint using a partial index. The bottom line is: you can prevent those rows from being inserted, but you can't use on conflict with that restriction

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.