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 > ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
