Skip site navigation (1) Skip section navigation (2)

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

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Matt <survivedsushi(at)yahoo(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1839: insert into table (column) values (nullif('',''));
Date: 2005-08-22 15:07:27
Message-ID: 20050822150727.GA60276@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: zekiDate: 2005-08-22 15:19:05
Subject: Permission denied errors
Previous:From: Tom LaneDate: 2005-08-22 14:23:46
Subject: Re: BUG #1838: IndexSupportInitialze

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group