Restrict insert into column with default value

Question

Taking the following schema as an example:

CREATE TABLE public."RiskHeaders"
(
    "UID" uuid NOT NULL DEFAULT uuid_generate_v4(),
    "Name" character varying(35) NOT NULL
)

Is it possible to prevent INSERT statements including values for the UID column?

For avoidance of doubt, I wish for all values within the UID column to be randomly generated via the default value function.

I had hoped that I could use an INSERT trigger to see whether UID had been populated but that doesn't appear to be possible:

BEGIN
    IF TG_OP = 'INSERT' THEN
        IF TG_WHEN = 'BEFORE' THEN
            RAISE NOTICE 'Before: Old = %    New = %', OLD."UID", NEW."UID";
        ELSIF TG_WHEN = 'AFTER' THEN
            RAISE NOTICE 'After: Old = %    New = %', OLD."UID", NEW."UID";
        END IF;
    END IF;
    RETURN NEW;
END;

Running INSERT INTO "RiskHeaders"("Name") VALUES ('Test'); gives the following console output:

NOTICE:  Before: Old =     New = ccc6757f-d1a2-4849-828d-017d2b738c9d
NOTICE:  After: Old =     New = ccc6757f-d1a2-4849-828d-017d2b738c9d

Running INSERT INTO "RiskHeaders"("UID", "Name") VALUES (uuid_generate_v4(), 'Test1'); gives the same output (albeit with a different UUID).

Thank you in advance for any suggestions and hopefully this isn't too daft a question!

Many thanks for all the suggestions!

Given my (reckless?) determination to keep the UID column as a uuid type and prevent the user from specifying any values on INSERT, I have come up with the following work-around(/bodge):

I am now using the following schema:

CREATE TABLE public."RiskHeaders"
(
    "UID" uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'::uuid,
    "Name" character varying(35) NOT NULL
}

Taking @stickybit's suggestion to use a trigger... I have come up with a trigger to the effect of:

    IF TG_OP = 'INSERT' THEN
        IF TG_WHEN = 'BEFORE' THEN
            IF NEW."UID" <> '00000000-0000-0000-0000-000000000000'::uuid THEN
                RAISE EXCEPTION 'A UID cannot be specified when creating a risk.';
            ELSE
                NEW."UID" := uuid_generate_v4();
                RETURN NEW;             
            END IF;
            ...
        END IF;
    END IF;
    ...

The drawbacks from this are:

The schema doesn't make it clear that UIDs are actually being randomly assigned.
The user could specify a zero UID which would then get silently replaced.

Restrict insert into column with default value

0 Answers