From: Matt A. <survivedsushi@yahoo.com>
To: pgsql-general@postgresql.org
Subject: Re: [SQL] question
Date: Mon, 29 Aug 2005 20:38:06 -0700 (PDT)
Message-ID: <20050830033806.92810.qmail@web35212.mail.mud.yahoo.com> (raw)
In-Reply-To: <F38541D4-4407-4CEB-A89A-CFE342094916@sitening.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: survivedsushi@yahoo.com, pgsql-general@postgresql.org
Subject: Re: [SQL] question
In-Reply-To: <20050830033806.92810.qmail@web35212.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox