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.
