Can postgres insert triggers and/or check be ran without inserting

Question

I would love to be able to validate objects representing table rows using the database's existing constraints (triggers that raise exceptions and checks) without actually inserting them into the database.

Is there currently a way one could do this in postgres? At least with BEFORE INSERT triggers and CHECK, I assume it makes no sense with AFTER INSERT triggers.

The easiest way I can think or right now would be to:

1. Lock the table
2. Insert a new row
3. If exception raise to the API / else DELETE the row and call it valid
4. Unlock

But I can see several issues with this.

Answer 1

A simpler way is to insert within a transaction and not commit:

BEGIN;
INSERT INTO tbl(...) VALUES (...);
-- see effects ...
ROLLBACK;

No need for additional locking. The row is never visible to any other transaction with default transacton isolation level READ COMMITTED. (You might be stalling concurrent writes that confict with the tested row.)

Notable side-effect: Sequences of serial or IDENTITY columns are advanced even if the INSERT is never committed. But gaps in sequential numbers are to be expected anyway and nothing to worry about.

Be wary of triggers with side-effects. All "transactional" SQL effects are rolled back, even most DDL commands. But some special operations (like advancing sequences) are never rolled back.

Also, DEFERRED constraints do not kick in. The manual:

DEFERRED constraints are not checked until transaction commit.

If you need this a lot, work with a copy of your table, or even your database.

Strictly speaking, while any trigger / constraint / concurrent event is allowed, there is no other way to "validate objects" than to insert them into the actual target table in the actual target database at the actual point in time. Triggers, constraints, even default values, can interact with the current state of the whole DB. The more possibilities are ruled out and requirements are reduced, the more options we might have to emulate the test.

Answer 2

CREATE FUNCTION validate_function ( )
 RETURNS trigger LANGUAGE plpgsql
 
AS $function$
DECLARE
    valid_flag boolean := 't';
BEGIN
    --Validation code
    if valid_flag = 'f' then
        RAISE EXCEPTION 'This record is not valid id  %', id
        USING HINT = 'Please enter valid record';    
        RETURN NULL;
    else
        RETURN NEW;
    end if;
END;
$function$

CREATE TRIGGER validate_rec BEFORE INSERT OR UPDATE ON some_tbl
    FOR EACH ROW EXECUTE FUNCTION validate_function();

With this function and trigger you validate inside the trigger. If the new record fails validation you set the valid_flag to false and then use that to raise exception. The RETURN NULL; is probably redundant and I am not sure it will be reached, but if it is it will also abort the insert or update. If the record is valid then you RETURN NEW and the insert/update completes.