Re: Bug or feature in AFTER INSERT trigger? - Mailing list pgsql-sql

The problem is with your function, not Pg logic.

Namely you have this condition:

        AND coalesce(ma.valid_from, '-infinity') < now()
        AND coalesce(ma.valid_to, 'infinity') > now()

Let's assume you didn't fill in values for valid_from/valid_to. Valid_from, due to "default" becomes now(). and valid_to null.

The thing is now() doesn't change within transaction.

So the value of now() that your where compares is *exactly* the same as the one inserted into row.

So, the condition: coalesce(ma.valid_from, '-infinity) <now() returns false, because it is = now(), and not < now().

If you'd insert literal NULL value, for example by doing:

INSERT INTO tmp.mail_account(contract_id, username, domain, email, valid_from, valid_to) VALUES (123, 'depesz', 'depesz.com', 'depesz@gmail.com', NULL, NULL);

Then, the column would be null, and coalesce() would return '-infinity', which would give true when comparing with now().

But if you insert data like:

INSERT INTO tmp.mail_account(contract_id, username, domain, email) VALUES (123, 'depesz', 'depesz.com', 'depesz@gmail.com');

Then the valid_from gets value from default expression.

depesz