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@xxxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
