1

Let's say I have a table that has roughly the following schema (for brevity, I only kept the relevant fields):

CREATE TABLE IF NOT EXISTS some_table
(
    id           SERIAL                   NOT NULL PRIMARY KEY,
    user_ids     TEXT[]                   NOT NULL,
    field_1      TEXT                     NOT NULL,
    field_2      TEXT                     NOT NULL,
    field_3      TEXT                     NOT NULL,
    hash_id      TEXT UNIQUE              NOT NULL
)

hash_id = md5(field_1 || field_2 || field_3)

The idea is to make sure that there are no duplicates in terms of (field_1, field_2, field_3) triplets. If the row to be inserted is a duplicate, we just append the user_id value to the source array.

It seems rather intuitive to hash the concatenated result and store it as an additional field that is constrained to be unique.

My first question is whether it's recommended to set a trigger for such a case like below:

CREATE OR REPLACE FUNCTION create_hash() RETURNS trigger AS
$$
BEGIN
    IF tg_op = 'INSERT' OR tg_op = 'UPDATE' THEN
        NEW.hash_id = MD5(NEW.field_1 || NEW.field_2 || NEW.field_3);
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER some_table_hash_update
    BEFORE INSERT OR UPDATE
    ON some_table
    FOR EACH ROW
EXECUTE PROCEDURE create_hash();

This solutions works and it's pretty convenient that the database automatically handles this hash_id field on every insert and update.

On the other hand, inserting into this table makes it necessary to specify hash_id anyway, which doesn't make a lot of sense. In addition, if I understand correctly, triggers might not work nicely with the ON CONFLICT part.

That makes me think that probably it's easier in this case to always hash the triplet in the code and just pass it as an argument to queries.

INSERT INTO some_table (user_ids, field_1, field_2, field_3, hash_id)

VALUES (ARRAY [$val], 'field_1_value', 'field_2_value', 'field_3_value', '')

ON CONFLICT (hash_id)
    DO UPDATE SET user_ids = ARRAY_APPEND(some_table.user_ids, $val)

The second usecase is when we need to delete by hash_id (this is a path parameter), but there is also user_id (this is a query parameter).

If length(user_ids) == 1, then we need to delete the row because the array will become empty. Else, just remove the user_id from user_ids.

It seems that I could use a trigger to delete the row AFTER UPDATE if the condition is satisfied, but this approach looks a bit clumsy. Probably, there are some better ways to achieve the result I am describing.

2
  • 2
    Why a trigger? This can be done using a generated column which is a bit more efficient than a trigger.
    – user1822
    Aug 8, 2022 at 19:27
  • @a_horse_with_no_name, thanks, this is much better than using a trigger for my case. That said, what do you think about the second feature when a row is deleted when the length of its array field is zero? In this case, it seems that setting a trigger is the way to go
    – Don Draper
    Aug 9, 2022 at 5:18

0

Your Answer

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

Browse other questions tagged or ask your own question.