Thread: Converting empty input strings to Nulls
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
Hello
2008/5/31 Ken Winter <ken@sunward.org>:
> 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?
>
no - it's not possible. And some "magic" fix in triggers is bad style.
you can write own custom type (not in plpgsql) that allows this
behave. You can copy and modify postgre's DateADT implementation.
pgsql/src/backend/utils/adt/date.c
Datum
date_in(PG_FUNCTION_ARGS)
{
char *str = PG_GETARG_CSTRING(0);
DateADT date;
fsec_t fsec;
struct pg_tm tt,
*tm = &tt;
int tzp;
int dtype;
int nf;
int dterr;
char *field[MAXDATEFIELDS];
int ftype[MAXDATEFIELDS];
char workbuf[MAXDATELEN + 1];
// your hack
if (strlen(str) == 0)
PG_RETURN_NULL();
dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
field, ftype,
MAXDATEFIELDS, &n
if (dterr == 0)
dterr = DecodeDateTime(field, ftype, nf, &dtype, tm,
&fsec, &tzp)
if (dterr != 0)
DateTimeParseError(dterr, str, "date");
switch (dtype)
more: http://www.postgresql.org/docs/8.3/interactive/xtypes.html
I am not sure if in function can return NULL.You should to test it
regards
Pavel Stehule
> ~ TIA
> ~ Ken
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Pavel Stehule wrote: > Hello > > 2008/5/31 Ken Winter <ken@sunward.org>: >> ... 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? >> > no - it's not possible. And some "magic" fix in triggers is bad style. The rule system may be able to handle this transformation (using insert rules or an updateable view). Getting it to work could be a bit arcane, though, and having never really delved into the rule system I can't be of much help. See: http://www.postgresql.com.cn/docs/8.3/static/rules.html http://www.postgresql.com.cn/docs/8.3/static/sql-createrule.html http://wiki.postgresql.org/wiki/Updatable_views http://wiki.postgresql.org/wiki/Introduction_to_PostgreSQL_Rules_-_Making_entries_which_can't_be_altered Personally, though, if at all possible I'd fix the broken client application. "" is NOT NULL . If you don't fix it, consider at least clearly documenting the wacky behaviour and if possible applying it only to an updatable view rather than the base table. -- Craig Ringer
On Sat, 2008-05-31 at 13:40 -0400, Ken Winter wrote:
> 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 ...
You can replace your table with a view and use rules to transform the
updates and inserts.
Regards,
Jeff Davis
Hi Ken- Have you looked at encode ? http://www.postgresql.org/docs/8.3/interactive/functions-string.html Anyone else? Martin ----- Original Message ----- From: "Ken Winter" <ken@sunward.org> To: "PostgreSQL pg-general List" <pgsql-general@postgresql.org> Sent: Saturday, May 31, 2008 1:40 PM Subject: [GENERAL] Converting empty input strings to Nulls 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general