Re: Type conversions and nulls

I think coalesce may help you here.

On Tue, 11 May 2004, Edmund Dengler wrote:

> Howdy all!
> 
> Just checking on whether this is the expected behaviour. I am transferring
> data from multiple databases to single one, and I want to ensure that I
> only have unique rows for some tables. Unfortunately, some of the rows
> have nulls for various columns, and I want to compare them for exact
> equality.
> 
> => create table tmp (
>      bigint a,
>      bigint b,
>      primary key (a, b)
>    );
> 
> To test for existence, I would naively use:
> 
> => select count(1) from tmp
>    where a = <value>
>      and b = <value>;
> 
> What I should use is:
> 
> => select count(1) from tmp
>    where ((a = <value>) or (a is null and <value> is null))
>      and ((b = <value>) or (b is null and <value> is null));
> 
> Looking in the manual, I see I can get what I want by running:
> 
> => set transform_null_equals to on;
> 
> And I can go back to using my naive script and everything works.
> 
> However, as <values> are integers, I need to convert them to bigint's so
> that the index can be used (Postgresql 7.4.2 automatic casts, unless this
> has been fixed). So I wrote my script to do the following
> 
> => select count(1) from tmp
>    where a = <value>::bigint
>      and b = <value>::bigint;
> 
> And now the nulls don't match! As a further test, I did:
> 
> => select null = null, null = null::bigint, null::bigint = null::bigint;
>  ?column? | ?column? | ?column?
> ----------+----------+----------
>  t        | t        |
> (1 row)
> 
> So, is there a way to do the casts such that this works? Other
> alternatives? I did a search but couldn't find an answer on the archives.
> 
> Regards!
> Ed
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>