From: | "Ken Winter" <ken(at)sunward(dot)org> |
---|---|
To: | "PostgreSQL pg-general List" <pgsql-general(at)postgresql(dot)org> |
Subject: | Converting empty input strings to Nulls |
Date: | 2008-05-31 17:40:05 |
Message-ID: | 001501c8c345$5d6acfe0$6703a8c0@KenIBM |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2008-05-31 18:35:29 | Re: Converting empty input strings to Nulls |
Previous Message | Greg Sabino Mullane | 2008-05-31 15:07:37 | Re: Mediawiki 1.10 and PG 8.3 upgrade |