On Thu, Aug 25, 2005 at 04:10:27PM -0700, Matt A. wrote:
> Anyway, I am new to postgres and looking to casts. I
> have wrote this question several times before and on
> this version I forgot to add the exact rules of why
> it's breaking and what i need it to do. I will try to
> be more clear...
>
> I use the rules to insert 0/1/null inserts into
> booleans, integers, text, etc. fields.
>
> Example: insert into table (bool_column) values
> nullif('',''));
> ERROR: column "bool_column" is of type boolean but
> expression is of type text.
First rule, the type of a functions is determined by its arguments.
Neither of the arguments in your example are boolean so it doesn't know
you want a boolean. nullif itself works on any type.
kleptog=# create temp table x (t text, b bool, i integer);
CREATE TABLE
kleptog=# insert into x values (nullif('1','1'), nullif(true,true),
nullif(4,4) );
INSERT 114774 1
kleptog=# select * from x;
t | b | i
---+---+---
| |
(1 row)
All nulls...
Your choice are to indicate in the arguments what type you want. In
this case the arguments arn't booleans so that won't work. Your other
option is to cast the result, which you can't because text -> bool is
not a valid typecast.
>
> Where '' == '' should evaluate to NULL as OPAQUE
> (depreciated) or similiar return instead of TEXT cast.
>
> So if I could alter the cast from text to return
> either INTEGER or TEXT, that would super! I'm not sure
> if it's possible but if so I'm willing to do what it
> takes to accomplish it.
Functions don't return OPAQUE, they can't because a function knows
exactly what it's returning. In your case it's returning TEXT because
that's what the arguments default to if it doesn't know any better.
Technically, nullif takes arguments (anyelement,anyelement) which means
it can take any type, as long as they're the same.
Hope this clarifies it for you.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
