From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Kirk Parker <khp(at)equatoria(dot)us> |
Cc: | PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: CHECK that involves a function call behaves differently during bulk load? |
Date: | 2025-03-13 21:05:19 |
Message-ID: | CAKFQuwZG5GwXFwE5WLLGeu4hEKt51mN-6iKM-q5rqb4wMD+a1w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Kirk Parker | 2025-03-13 22:33:33 | Re: CHECK that involves a function call behaves differently during bulk load? |
Previous Message | Kirk Parker | 2025-03-13 20:52:49 | CHECK that involves a function call behaves differently during bulk load? |