Re: Check constraint failure messages

Поиск
Список
Период
Сортировка
От Miles Elam
Тема Re: Check constraint failure messages
Дата
Msg-id CAALojA-=iKKp-YuHwY=RKT9ow=AdKKG1iYK674AVw5PdTcXTzg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Check constraint failure messages  (Miles Elam <miles.elam@productops.com>)
Список pgsql-general
Following up in case someone else runs into this problem. I changed the function the CHECK statement called to raise a warning. Not perfect, but noticeably better. I don't get the column that failed but I do get what bad input gummed things up.

CREATE OR REPLACE FUNCTION po.confirm(p_val anyelement, p_validated boolean)
RETURNS boolean LANGUAGE plpgsql STRICT IMMUTABLE PARALLEL SAFE AS $$
BEGIN
  IF NOT p_validated THEN
    RAISE WARNING 'Invalid value: %', p_val;
  END IF;
  RETURN p_validated;
END;
$$;
COMMENT ON FUNCTION po.confirm(anyelement,boolean) IS
'Raises a warning when a condition is false; useful for outputting CHECK constraint error values.';

CREATE DOMAIN po.email AS varchar
  CHECK (po.confirm(VALUE, VALUE IS NULL OR NOT po.email_expanded(VALUE) IS NULL));

Code is not seamless or DRY, but manageable.

- Miles

On Tue, Apr 6, 2021 at 2:18 PM Miles Elam <miles.elam@productops.com> wrote:
On Tue, Apr 6, 2021 at 1:59 PM Ron <ronljohnsonjr@gmail.com> wrote:

The blunt force answer is to not use bulk inserts.  Try COPY; it's good at saying which record throws an error.

Sadly, this is a cloud-managed database without direct access to 5432 from outside the VPC and bastian instances are frowned upon by our security folks. Guess I'm stuck with bisecting. Thanks for the confirmation.

В списке pgsql-general по дате отправления:

Предыдущее
От: Kevin Brannen
Дата:
Сообщение: RE: Check constraint failure messages
Следующее
От: Siddhartha2485
Дата:
Сообщение: Re: ERROR : invalid transaction termination : PostgreSQL v12