PostgreSQL Time as PrimaryKey, don't allow Insert of entry with a Timestamp which is after the-CodePudding

I have a Table which has a Column with timestamptz as PK and a column to store values. The timestamptz is provided on the insert. I do not want to allow Inserts into this table with a timestamptz which lies in the future. The result should be an exception.

I guess i need a Rule or a Trigger function. But i do not know the best way to do this.

Example in Picture

CodePudding user response：

You cannot use a check constraint because NOW() is a non deterministic function.

You can use a trigger. I used as table name mytable. Adjust the script:

create function check_time() RETURNS trigger AS
$$
BEGIN
    IF NEW.time > now() THEN
        RAISE EXCEPTION 'time cannot be in future';
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

And:

CREATE TRIGGER trg_check_future BEFORE
INSERT OR UPDATE
    ON
    public.mytable FOR EACH ROW EXECUTE FUNCTION check_time();