Re: CHECK that involves a function call behaves differently during bulk load?

On Thursday, March 13, 2025, Kirk Parker <khp(at)equatoria(dot)us> wrote:
>
>
> CREATE FUNCTION public.valid_upc_ean(target character varying) RETURNS
> boolean
> LANGUAGE plpgsql
> AS $$
> DECLARE
> len integer;
> holder varchar;
>
> begin
> if target is null then
> return true;
> end if;
> len = length(trim(target));
> if len = 12 then
> holder := target::upc;
> return true;
> elsif len = 13 then
> holder := target::ean13;
> return true;
> elsif len = 0 then
> return true;
> else
> return false;
> end if;
> exception when others then
> return false;
> end;
> $$;
>
> It's used like this:
>
> CREATE TABLE public.inv_variant
> (
> id integer NOT NULL,
> prod_id integer NOT NULL,
> sku character varying(126) NOT NULL,
> upc character varying(14),
> ...
> CONSTRAINT var_upc_check CHECK (public.valid_upc_ean(upc))
> );
>
> As mentioned about, the function fires and works perfectly once the data
> is loaded, but the bulk load gives this error:
>
> ERROR: new row for relation "inv_variant" violates check constraint
> "var_upc_check"
> DETAIL: Failing row contains (2264, 2170, BOX-1, 012345678912, ...
>
> Every row with NULL in this column inserts successfully, every one with
> data fails in this way. The data itself is valid -- I can copy the text
> starting with the '(' from the DETAIL: Failing row... message, paste it
> after "insert into inv_variant", and it executes perfectly--this in the
> very same psql where that exact set of insert data is failing on the
> redirected dump-file input.
>
> I am quite at a loss about how to even go about troubleshooting this.
>

Not discarding useful error messages as you do in the exception block is a
good start.

>
> Since valid_upc_ean() is a pure function with no side effects or
> references to anything other than the table in question,
>
>
And two non-core data types that lack schema qualifications.

David J.