Re: [SQL] question

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(at)sitening(dot)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