You can try COALESCE function where by if you have a null you can make it
have a default value and that default value will be a value the field
never reaches
i.e. COALESCE(NULL,'') = COALESCE(NULL,'')
HTH
Darren
On Tue, 14 Oct 2003, Edmund Dengler wrote:
> An issue is that I am trying to avoid having another table (to stop the
> need for a join (performance reasons)). The NULLs are relatively rare, but
> since they can appear, and in certain pathological cases the sequential
> scan can take seconds to run, I was hoping for a work-around. But it looks
> like I have no real choice in this as there is no way to specify that
> NULL == NULL.
>
> Another question: if I have a multi-column index, and one of the values of
> a tuple is NULL, is that row not indexed? If it is, how does this jibe
> with the "NULLs are not indexed" statements?
>
> Thanks!
> Ed
>
> On Tue, 14 Oct 2003, Mike Mascari wrote:
>
> > Edmund Dengler wrote:
> >
> > > Is the rewrite only for the literal 'X = NULL' or will it do a test
> > > against a value such as 'X = OLD.X' (and rewrite is OLD.X is NULL)?
> >
> > It is a parse time transformation:
> >
> >
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&frame=right&th=26ef31219ae11442&seekm=3DF52206.5060507%40mascari.com#link6
> >
> > > Is there any way to match NULLS to each other (as I am looking for a
> > > literal row, not using NULL as the UNKNOWN). I suppose I could put in a
> > > dummy value for the 'Not a valid value', but it seems to be quite awkward
> > > when I really do want the NULL.
> >
> > Normalization would have you eliminate the NULL by having another
> > relation whose candidate key is the same as your original table, but
> > those records whose attribute is NULL would simply not be present in
> > the child table.
> >
> > Another possible solution is to define your own type with an internal
> > status for 'Not a valid value'...
> >
> > HTH,
> >
> > Mike Mascari
> > mascarm@mascari.com
> >
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Darren Ferguson
