Re: Type conversions and nulls

How so? Do you have an example to show?

Looking at the docs, coalesce works as

coalesce(<value1>,<value2>)

and will return the first thing that is not null.

What I want is an '=' that compares nulls as equal (rather than as
not-equal, which is the normal case). Ie, an '=' that acts as

(column = <value>) or (column is null and <value> is null)

The "transform_null_equals" does exactly what I want, except that casting
seems to break it in some manner.

Regards,
Ed

On Tue, 11 May 2004, scott.marlowe wrote:

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