X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 380C4D6E7E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Aug 2005 03:45:30 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 49195-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Aug 2005 06:45:26 +0000 (GMT)
Received: from mail.travelamericas.com (unknown [206.130.134.147])
	by svr1.postgresql.org (Postfix) with SMTP id 98872D7EFB
	for <pgsql-sql@postgresql.org>; Sat, 27 Aug 2005 03:45:24 -0300 (ADT)
Received: (qmail 13004 invoked from network); 27 Aug 2005 06:45:26 -0000
Received: from unknown (HELO ?10.0.0.253?) (10.0.0.253)
	by verkiel.travelamericas.com with SMTP; 27 Aug 2005 06:45:26 -0000
Message-ID: <43100C05.7020309@travelamericas.com>
Date: Fri, 26 Aug 2005 23:45:25 -0700
From: Chris Travers <chris@travelamericas.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: "Matt L." <survivedsushi@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: nullif('','') on insert
References: <20050821014023.92296.qmail@web35209.mail.mud.yahoo.com>
In-Reply-To: <20050821014023.92296.qmail@web35209.mail.mud.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.042 required=5 tests=[AWL=-0.008,
	FORGED_RCVD_HELO=0.05]
X-Spam-Level: 
X-Archive-Number: 200508/260
X-Sequence-Number: 22488

Matt L. wrote:

>I need to test whether or not a value is null on
>insert. 
>
>Example: insert into table (column) values
>nullif('',''));
>ERROR: column "column" is of type boolean but
>expression is of type text.
>  
>
Your problem is that NULL's are typed in PostgreSQL.

Try this:

SELECT NULL;
SELECT NULL::BOOL;
SELECT NULL::BOOL::TEXT;

to see what I mean.  This is an exact illustration of your problem.

>It works in MSSQL (probably against not standards) but
>nonetheless I need to make it work.
>
>I assume it's returning 'NULL' w/ quotes?
>
Nope.  It is returning a text string which is valued at NULL.  It cannot 
convert a text string to a BOOL (even if the string is a NULL) so it 
gives you an error.

> I don't know
>where to look to alter it. I looked into functions but
>all I see is how to write "AS queries" or point to
>various snippets. I'd rather just alter the nullif
>function. 
>  
>
SELECT NULLIF('' = '', TRUE);

Does this work?  You could write a wrapper function if necessary.....

Best Wishes,
Chris Travers
Metatron Technology Consulting