Converting empty input strings to Nulls

Поиск
Список
Период
Сортировка
От Ken Winter
Тема Converting empty input strings to Nulls
Дата
Msg-id 001501c8c345$5d6acfe0$6703a8c0@KenIBM
обсуждение исходный текст
Ответы Re: Converting empty input strings to Nulls
Re: Converting empty input strings to Nulls
Список pgsql-general
Applications accessing my PostgreSQL 8.0 database like to submit no-value
date column values as empty strings rather than as Nulls.  This, of course,
causes this PG error:

SQL State: 22007
ERROR: invalid input syntax for type date: ""

I'm looking for a way to trap this bad input at the database level, quietly
convert the input empty strings to Null, and store the Null in the date
column.  I tried a BEFORE INSERT OR UPDATE trigger evoking this function ...

CREATE OR REPLACE FUNCTION "public"."empty_string_to_null"()
RETURNS trigger AS
$BODY$
BEGIN
    IF CAST(NEW.birth_date AS text) = '' THEN
        NEW.birth_date = Null;
    END IF;
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

... but an empty string still evokes the error even before this function is
triggered.

Is there a way to convert empty strings to Nulls before the error is evoked?

~ TIA
~ Ken


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

Предыдущее
От: "Greg Sabino Mullane"
Дата:
Сообщение: Re: Mediawiki 1.10 and PG 8.3 upgrade
Следующее
От: "Pavel Stehule"
Дата:
Сообщение: Re: Converting empty input strings to Nulls