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

On Thursday, March 13, 2025, Kirk Parker <khp@equatoria.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.