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.
FOO.doc was checked first and its hash (at the time) was added. Same deal with bar.txt.FOO.doc's hash changed so a second entry was added. bar.txt's entry was left alone since its hash remained the same.bar.txt was deleted, FOO.doc remained untouched since last time.bar.txt was added again and FOO.doc remained untouched again.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.
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')