CODEWITHSUNDEEP
sqlpostgresqlsql-insertcheck-constraints
leetbacoon
leetbacoon

Reputation: 1249

Conditional CHECK constraint

In Postgresql 11.5 I want to have a table store file hashes and their respective filenames, as well as a date as to when this information was processed. Instead of having an available BOOLEAN column I would rather put DELETED! into the crc32 column.

Like this table:

 filename |  crc32   |   date
----------+----------+------------
 FOO.doc  | 005929FA | 2015-03-14
 bar.txt  | C5907C6A | 2015-03-14
 FOO.doc  | 2AF317BB | 2016-08-22
 bar.txt  | DELETED! | 2018-11-30
 bar.txt  | AC0132D9 | 2019-09-10

This table shows the file history from a directory.

The thing about this table is, I have a CHECK constraint that allows only this regex to be entered into the crc32 column: ^[0-9A-F]{8}$|^DELETED\!$

I want to make this a bit stricter. I want Postgresql to error out when I try to use DELETED! if the file was never in the table before. So if I try adding an entry like: INSERT INTO hist VALUES ('egg.txt', 'DELETED!', '2019-09-23'); it will error out. If I try INSERT INTO hist VALUES ('FOO.doc', 'DELETED!', '2019-09-23'); it will work since FOO.doc was there already.

So basically DELETED! can only be used when the file in question already has an entry in the table.

Is this possible? I hope this makes sense. If I didn't explain this enough let me know. Thanks.

Upvotes: 0

Views: 184

Answers (2)

leetbacoon
leetbacoon

Reputation: 1249

I added this CHECK constraint and it appears to work.

CHECK (crc32 IS NULL AND is_deleted = 't' OR crc32 IS NOT NULL AND is_deleted = 'f')

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247790

That additional requirement would best be met with a BEFORE trigger:

CREATE FUNCTION check_upd() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF NOT EXISTS (SELECT 1 FROM my_table
                  WHERE filename = NEW.filename)
   THEN
      RAISE EXCEPTION 'cannot delete a non-existing row';
   END IF;

   RETURN NEW;
END;$$;

CREATE TRIGGER check_upd BEFORE INSERT ON my_table
   FOR EACH ROW WHEN (NEW.crc32 = 'DELETED!')
EXECUTE PROCEDURE check_upd();

Upvotes: 1

Related Questions