pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

nullif('','') on insert
3+ messages / 3 participants
[nested] [flat]

* nullif('','') on insert
@ 2005-08-21 01:40  Matt L. <survivedsushi@yahoo.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Matt L. @ 2005-08-21 01:40 UTC (permalink / raw)
  To: pgsql-sql

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.

It works in MSSQL (probably against not standards) but
nonetheless I need to make it work.

I assume it's returning 'NULL' w/ quotes? 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. 

Is there a function for this or could someone give me
an example to insert NULL? 

Cheers
Matt


		
__________________________________ 
Yahoo! Mail for Mobile 
Take Yahoo! Mail with you! Check email on your mobile phone. 
http://mobile.yahoo.com/learn/mail 



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: nullif('','') on insert
@ 2005-08-27 06:45  Chris Travers <chris@travelamericas.com>
  parent: Matt L. <survivedsushi@yahoo.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Chris Travers @ 2005-08-27 06:45 UTC (permalink / raw)
  To: Matt L. <survivedsushi@yahoo.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: nullif('','') on insert
@ 2005-08-27 13:56  Bruno Wolff III <bruno@wolff.to>
  parent: Chris Travers <chris@travelamericas.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Bruno Wolff III @ 2005-08-27 13:56 UTC (permalink / raw)
  To: Chris Travers <chris@travelamericas.com>; +Cc: Matt L. <survivedsushi@yahoo.com>; pgsql-sql

> 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.

Note that in 8.1 he will be able to do:
SELECT NULL::TEXT::INT::BOOL
and get the conversion he wants as long as he sticks to using 0 and 1 to
code for false and true.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2005-08-27 13:56 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-21 01:40 nullif('','') on insert Matt L. <survivedsushi@yahoo.com>
2005-08-27 06:45 ` Chris Travers <chris@travelamericas.com>
2005-08-27 13:56   ` Bruno Wolff III <bruno@wolff.to>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox