postgres: How to prevent INSERT in a special case

Question

Hi there,

I got a table 'foo' that looks like

   ID  | NAME 
 ------+----------------------------
   123 | PiratesAreCool
   254 | NinjasAreCoolerThanPirates

and a second table 'bar'

  SID  |  ID  |  created   |  dropped
 ------+------+------------+-----------
  9871 |  123 | 03.24.2009 | 03.26.2009
  9872 |  123 | 04.02.2009 |

bar.ID is a reference (foreign key) to foo.ID

Now I want to prevent that you can insert a new record to 'bar' when there is a record with the same ID and bar.dropped is null on that record.

So, when the 'bar' looks like above

   INSERT INTO BAR VALUES ('9873','123','07.24.2009',NULL);

should be forbidden, but

   INSERT INTO BAR VALUES ('9873','254','07.24.2009',NULL);

should be allowed (because there is no 'open' bar-record for 'NinjasAreCoolerThanPirates').

How do i do that? I hope my problem is clear and somebody can help me.

Answer 1

hmm, that should be enough to just create a unique index.

create unique index ix_open_bar on bar (id, dropped);

of course, that would also have the effect that you can not drop a bar twice per day (unless the dropped is a timestamp which would minimize the risk)

Actually, I noticed that Postgres have support for partial indexes:

 create unique index ix_open_bar on bar (id) where dropped is null;

Update: After some tests, the unique constraint is not enforced on null values, but the partial indexes will still work.

And if you don't want to use the partial indexes, this might work as well:

 create unique index ix_open_bar on bar(id, coalesce(dropped, 'NULL'));

However, when using coalesce, you need to have the same datatypes on them (so if dropped is a timestamp, you need to change 'NULL' to a timestamp value instead).

Answer 2

This will only insert a record if there isn't an 'open' record in bar for your id

INSERT INTO bar 
SELECT '9873','254','07.24.2009',NULL
WHERE NOT EXISTS(SELECT 1 FROM bar WHERE ID='254' AND dropped IS NULL)

Answer 3

Set up a trigger on the table bar on insert that checks to see if the current row's ID is present in the table already and reject it if so.

I don't know the specific postgres syntax, but it should work something like this:

CREATE TRIGGER trigger_name BEFORE INSERT ON bar
IF EXISTS (
  SELECT 1
  FROM bar
  WHERE bar.ID = inserted.ID
  AND bar.dropped IS NULL
)
BEGIN
  // raise an error or reject or whatever Postgres calls it.
END

And then whenever you try to insert into bar, this trigger will check if something already exists and reject it if so. If bar.dropped isn't null, it'll allow the insert just fine.

If someone knows the right syntax for this, please feel free to edit my answer.

Answer 4

You can create a partial index with a WHERE clause. For your purposes this might do;

CREATE UNIQUE INDEX my_check on bar(id) where dropped is null;

Assuming id 124 does NOT exists in the table, this will be allowed , but only ONE record can have dropped=NULL for a given ID:

INSERT INTO BAR VALUES ('9873','124','07.24.2009',NULL);

And this will be allowed wether or not 124 already exists

INSERT INTO BAR VALUES ('9873','124','07.24.2009','07.24.2009');

If 125 already exists, this will not be allowd

INSERT INTO BAR VALUES ('9873','125','07.24.2009',NULL);

But this will

INSERT INTO BAR VALUES ('9873','125','07.24.2009','07.24.2009');