question

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:

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

On Aug 24, 2005, at 1:05 AM, Matt A. wrote:

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.

I *certainly* hope you're not passing $value in straight from your
web form directly into the SQL. You're opening yourself up for SQL
injection attacks.

Why not just have your app that reads the form generate the proper
value to insert? That is the safe route.

Vivek Khera, Ph.D.
+1-301-869-4449 x806