Thread: BUG #1839: insert into table (column) values (nullif('',''));

The following bug has been logged online:

Bug reference:      1839
Logged by:          Matt
Email address:      survivedsushi@yahoo.com
PostgreSQL version: 8.0.3
Operating system:   linux
Description:        insert into table (column) values (nullif('',''));
Details:

insert into table (column) values (nullif('',''));
ERROR: column "column" is of type boolean but
expression is of type text.

inserting NULL works. nullif('','') should return NULL
if both values are equal? It works in MSSQL.

Is there different function to accomplish a insert
nullif('','') test.

Thanks
Matt

On Sun, 21 Aug 2005, Matt wrote:

>
> The following bug has been logged online:
>
> Bug reference:      1839
> Logged by:          Matt
> Email address:      survivedsushi@yahoo.com
> PostgreSQL version: 8.0.3
> Operating system:   linux
> Description:        insert into table (column) values (nullif('',''));
> Details:
>
> insert into table (column) values (nullif('',''));
> ERROR: column "column" is of type boolean but
> expression is of type text.
>
> inserting NULL works. nullif('','') should return NULL
> if both values are equal? It works in MSSQL.

It does return a NULL AFAICS, however it looks like it returns a NULL cast
to a textual type (since if they were different, it would return its first
argument according to the docs).

On Sun, Aug 21, 2005 at 06:17:28PM +0100, Matt wrote:
>
> insert into table (column) values (nullif('',''));
> ERROR: column "column" is of type boolean but
> expression is of type text.
>
> inserting NULL works. nullif('','') should return NULL
> if both values are equal? It works in MSSQL.

NULLIF's return type is derived from the argument types; for more
information see "UNION, CASE, and ARRAY Constructs" in the "Type
Conversion" chapter of the documentation (NULLIF is a CASE construct):

http://www.postgresql.org/docs/8.0/static/typeconv-union-case.html

The type resolution behavior is defined in the SQL standard (9.3
"Set operation result data types" in SQL92; 9.3 "Data types of
results of aggregations" in SQL:1999).

> Is there different function to accomplish a insert
> nullif('','') test.

What are the possible values of NULLIF's arguments?  It's not
clear what should happen if they're *not* equal.  What value
should the boolean column receive in the following case?

INSERT INTO table (column) VALUES (NULLIF('abc', ''));

Can you tell us more about what the NULLIF is trying to achieve?

--
Michael Fuhr

"Matt" <survivedsushi@yahoo.com> writes:
> insert into table (column) values (nullif('',''));
> ERROR: column "column" is of type boolean but
> expression is of type text.

Seems reasonable to me.  What type would you expect '' to be?

> It works in MSSQL.

If you think silent conversions from text to boolean are a good idea,
use MSSQL.  (Or create a cast to make PG do it ... but implicit
conversions across datatype categories are generally a bad idea in
my experience.  They tend to fire in cases where you didn't expect it.)

            regards, tom lane