Skip site navigation (1) Skip section navigation (2)

Re: Converting empty input strings to Nulls

From: "Martin" <mgainty(at)hotmail(dot)com>
To: "Ken Winter" <ken(at)sunward(dot)org>,"PostgreSQL pg-general List" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Converting empty input strings to Nulls
Date: 2008-06-01 00:57:59
Message-ID: BLU142-DAV5FD7E4A86B1F69EF68E85AEB80@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-general
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(at)sunward(dot)org>
To: "PostgreSQL pg-general List" <pgsql-general(at)postgresql(dot)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


In response to

pgsql-general by date

Next:From: Ram RavichandranDate: 2008-06-01 01:22:56
Subject: Defining character sets for indicidual fields
Previous:From: Sushant SinhaDate: 2008-05-31 23:58:41
Subject: Re: [GENERAL] Fragments in tsearch2 headline

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group