Converting empty input strings to Nulls

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

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&#39;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