CODEWITHSUNDEEP
postgresql
Ahmad Coachendo
Ahmad Coachendo

Reputation: 81

Updating timestamp when a field is edited in pg admin

I am modifying data directly in pg admin 4, where I have to validate manually by marking a boolean value to true that is false by default.

enter image description here

I want that when I modify the value in that column, the updated_at column value should also be updated to current timestamp so I can query data with modified date. How do I achieve this?

Upvotes: 0

Views: 43

Answers (1)

jian
jian

Reputation: 4867

The post desired behavior is more simple than code demo in https://www.postgresql.org/docs/current/plpgsql-trigger.html.

setup:

CREATE temp TABLE test (
    misc int,
    is_valid boolean,
    updated_at timestamptz
);

INSERT INTO test (misc, is_valid)
    VALUES (1, FALSE);

CREATE FUNCTION update_test_update_at() RETURNS trigger AS $func$
    BEGIN
        IF NEW.* != OLD.* THEN NEW.updated_at := current_timestamp;
        END IF;
        RETURN NEW;
    END;
$func$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER update_last_edit
    BEFORE UPDATE OF is_valid ON test
    FOR EACH ROW
    EXECUTE FUNCTION update_test_update_at();

test:

UPDATE
    test
SET
    is_valid = true
RETURNING
    *;

Upvotes: 1

Related Questions