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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Matthew Peter

The following bug has been logged online:

Bug reference:      1839
Logged by:          Matt
Email address:      [hidden email]
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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

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

Stephan Szabo
On Sun, 21 Aug 2005, Matt wrote:

>
> The following bug has been logged online:
>
> Bug reference:      1839
> Logged by:          Matt
> Email address:      [hidden email]
> 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).

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

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

Michael Fuhr
In reply to this post by Matthew Peter
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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
In reply to this post by Matthew Peter
"Matt" <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly