DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=VVwgFVzXj5hiCNt16sapDAdI5+H0ylcDst665QVdXrSEQC0onR65DbgHpdt+8cXBUnEX60vwgLCdl+x98Kjp125m7Bn7/ildhXMQuA1Ji5MPAWO1Hq5BGwOgdqcNJLbl5BUQasGR9JtIcgwkQr2E+Co9JDZzppZcFrf57EV3Si0=
	;
Message-ID: <20050830033806.92810.qmail@web35212.mail.mud.yahoo.com>
Date: Mon, 29 Aug 2005 20:38:06 -0700 (PDT)
From: "Matt A." <survivedsushi@yahoo.com>
Subject: Re: [SQL] question
To: pgsql-general@postgresql.org
In-Reply-To: <F38541D4-4407-4CEB-A89A-CFE342094916@sitening.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

The issue has been solved thanks to a custom
nullif_int() function. Which if anyone has the same
issue, it was solved with...


CREATE FUNCTION nullif_int(text) RETURNS integer AS
'SELECT nullif($1,'''')::int;' LANGUAGE SQL;


SELECTS were not the issue; INSERT INTO a non-text
column was the issue. 

Thanks anyway,
Matthew

--- "Thomas F. O'Connell" <tfo@sitening.com> wrote:

> Matt,
> 
> In PostgreSQL 8.0.3, I see:
> 
> postgres=# select nullif( '1', '' );
> nullif
> --------
> 1
> (1 row)
> 
> postgres=# select nullif( '', '' ) is null;
> ?column?
> ----------
> t
> (1 row)
> 
> What behavior are you expecting?
> 
> --
> Thomas F. O'Connell
> Co-Founder, Information Architect
> Sitening, LLC
> 
> Strategic Open Source: Open Your i™
> 
> http://www.sitening.com/
> 110 30th Avenue North, Suite 6
> Nashville, TN 37203-6320
> 615-469-5150
> 615-469-5151 (fax)
> 
> On Aug 24, 2005, at 12:05 AM, Matt A. wrote:
> 
> > I have a rating section on a website. It has radio
> > buttons with values 1-5 according to the rating
> level.
> > Lastly there is a null option for n/a. We use null
> for
> > n/a so it's excluded from the AVG() calculations.
> >
> > We used nullif('$value','') on inserts in mssql. 
> We
> > moved to postgres and love it but the nullif()
> doesn't
> > match empty strings to each other to return null
> other
> > than a text type, causing an error. This is a
> major
> > part of our application.
> >
> > AKA nullif('1','') would insert 1 as integer even
> > though wrapped in ''. Also nullif('','') would
> > evaluate NULL and insert the "not a value"
> > accordingly.
> >
> > Is there a workaround for this so it doesn't break
> our
> > rating system? We cannot always enter a value for
> a
> > integer column. Is there any other way to
> accomplish
> > this? Please help.
> >
> >
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! Mail - You care about security. So do we.
> > http://promotions.yahoo.com/new_mail
> >
> > ---------------------------(end of  
> > broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >
> 
> 


____________________________________________________
Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs