Type conversions and nulls

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

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

Use the full version or if you're generating the query strings on the fly
put only the check that matters (either the equality or the is null as
appropriate for the values). Or if all you care about is true/false,
possibly
select exists(select 1 from tmp where ...)
may be better.

Transform_null_equals is meant convert a very specific x = NULL or NULL =
x into x IS NULL. It doesn't (nor is it meant to) change how nulls
compare.