Thread

1. Type conversions and nulls

   Edmund Dengler <edmundd@esentire.com> — 2004-05-11T22:57:43Z

   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
   
   

2. Re: Type conversions and nulls

   scott.marlowe <scott.marlowe@ihs.com> — 2004-05-11T23:16:11Z

   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
   > 
   
   
   

3. Re: Type conversions and nulls

   Edmund Dengler <edmundd@esentire.com> — 2004-05-11T23:29:17Z

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

4. Re: Type conversions and nulls

   Stephan Szabo <sszabo@megazone.bigpanda.com> — 2004-05-11T23:38:46Z

   On Tue, 11 May 2004, Edmund Dengler wrote:
   
   > 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.
   
   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.
   
   

5. Re: Type conversions and nulls

   Edmund Dengler <edmundd@esentire.com> — 2004-05-11T23:50:19Z

   Ahh, thanks. So it is simply a syntactic transform, and not really a
   "proper" internal comparison operator change.
   
   Regards,
   Ed
   
   On Tue, 11 May 2004, Stephan Szabo wrote:
   
   > 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.
   >
   
   

6. Re: Type conversions and nulls

   Tom Lane <tgl@sss.pgh.pa.us> — 2004-05-12T03:15:56Z

   Edmund Dengler <edmundd@eSentire.com> writes:
   > What I want is an '=' that compares nulls as equal (rather than as
   > not-equal, which is the normal case).
   
   IS DISTINCT FROM may help you here.  It's a not-equals operator rather
   than an equals operator, but it does what you want.
   
   (Unless what you wanted included being able to use an index...)
   
   			regards, tom lane