473,322 Members | 1,259 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

SET within a function?

Hi all!

I am doing some trigger functions that need to find a tuple in another
table. The problem is that this second table is doing some summarization
work, and I need nulls to equal each other.

Basically, in the trigger I do a:

SELECT INTO ... x
FROM table1
WHERE ...(some straightforward x = old.x)...
AND (x1 = old.x1 OR (x1 is null and old.x1 is null))
AND (x2 = old.x2 OR (x2 is null and old.x2 is null))
AND (x3 = old.x3 OR (x3 is null and old.x3 is null));

The problem is that an index is used to perform the straightforward stuff,
and then the x1,x2,x3 is done via an index scan, rather than directly.
Unfortunately for the data set I have, it can be clustered pretty badly
around the straightforward stuff, and so the scan can take multiple
seconds per call.

I think if I could do a 'SET TRANSFORM_NULL_EQUALS TO ON' then this might
fix the issue (don't know, haven't tried it yet). My question is: can this
be done within a function such that at the end of the function, the value
is reset back to value upon entering (kind of like 'SET LOCAL' except for
just the length of the function call). Is this possible?

Thanks!
Ed

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #1
24 1701
On Mon, Oct 13, 2003 at 21:16:33 -0400,
Edmund Dengler <ed*****@eSentire.com> wrote:

I think if I could do a 'SET TRANSFORM_NULL_EQUALS TO ON' then this might
fix the issue (don't know, haven't tried it yet). My question is: can this
be done within a function such that at the end of the function, the value
is reset back to value upon entering (kind of like 'SET LOCAL' except for
just the length of the function call). Is this possible?


I don't think that will do what you want. That setting is used to
rewrite = null as is null, not to change things so that nulls match each
other.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #2
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)?

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.

Regards!
Ed

On Mon, 13 Oct 2003, Bruno Wolff III wrote:
On Mon, Oct 13, 2003 at 21:16:33 -0400,
Edmund Dengler <ed*****@eSentire.com> wrote:

I think if I could do a 'SET TRANSFORM_NULL_EQUALS TO ON' then this might
fix the issue (don't know, haven't tried it yet). My question is: can this
be done within a function such that at the end of the function, the value
is reset back to value upon entering (kind of like 'SET LOCAL' except for
just the length of the function call). Is this possible?


I don't think that will do what you want. That setting is used to
rewrite = null as is null, not to change things so that nulls match each
other.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #3
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=e...cari.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
ma*****@mascari.com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #4
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=e...cari.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
ma*****@mascari.com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #5
> 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)?

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.


I ended up writing an "equivalent" function for the project I'm working
on. It goes like this in plpgsql:

IF $1 IS NULL THEN
RETURN $2 IS NULL;
ELSIF $2 IS NULL THEN
-- We already know $1 is not null.
RETURN FALSE;
ELSE
-- Both args are not null.
RETURN $1 = $2;
END IF;

That's the basic idea. I put a wrapper around this to generate a copy of
it for all the data types used in my database.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #6
The problem I would face is that this still needs to be a sequential scan
in the table rather than an index lookup.

Regards,
Ed

On Tue, 14 Oct 2003, Arthur Ward 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)?

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.


I ended up writing an "equivalent" function for the project I'm working
on. It goes like this in plpgsql:

IF $1 IS NULL THEN
RETURN $2 IS NULL;
ELSIF $2 IS NULL THEN
-- We already know $1 is not null.
RETURN FALSE;
ELSE
-- Both args are not null.
RETURN $1 = $2;
END IF;

That's the basic idea. I put a wrapper around this to generate a copy of
it for all the data types used in my database.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #7
Edmund Dengler wrote:
The problem I would face is that this still needs to be a sequential scan
in the table rather than an index lookup.


IIRC, NULL values aren't indexed, only actual values, which is an
implementation detail but yet-another reason why NULL-elimination
through normalization is a good idea:

http://www.hughdarwen.freeola.com/Th...hout-nulls.pdf
Mike Mascari
ma*****@mascari.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #8
> 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)?

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.


I ended up writing an "equivalent" function for the project I'm working
on. It goes like this in plpgsql:

IF $1 IS NULL THEN
RETURN $2 IS NULL;
ELSIF $2 IS NULL THEN
-- We already know $1 is not null.
RETURN FALSE;
ELSE
-- Both args are not null.
RETURN $1 = $2;
END IF;

That's the basic idea. I put a wrapper around this to generate a copy of
it for all the data types used in my database.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #9
The problem I would face is that this still needs to be a sequential scan
in the table rather than an index lookup.

Regards,
Ed

On Tue, 14 Oct 2003, Arthur Ward 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)?

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.


I ended up writing an "equivalent" function for the project I'm working
on. It goes like this in plpgsql:

IF $1 IS NULL THEN
RETURN $2 IS NULL;
ELSIF $2 IS NULL THEN
-- We already know $1 is not null.
RETURN FALSE;
ELSE
-- Both args are not null.
RETURN $1 = $2;
END IF;

That's the basic idea. I put a wrapper around this to generate a copy of
it for all the data types used in my database.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #10
Edmund Dengler wrote:
The problem I would face is that this still needs to be a sequential scan
in the table rather than an index lookup.


IIRC, NULL values aren't indexed, only actual values, which is an
implementation detail but yet-another reason why NULL-elimination
through normalization is a good idea:

http://www.hughdarwen.freeola.com/Th...hout-nulls.pdf
Mike Mascari
ma*****@mascari.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #11
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=e...cari.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
ma*****@mascari.com


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #12
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=e...cari.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
ma*****@mascari.com


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org


--
Darren Ferguson
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #13
This just returns us to the problem that the use of a function causes a
sequential scan (as the select has to essentially apply the function to
each row). I would need to store a dummy value into the field (it is an
int, so I could store -1, but it breaks my sense of aesthetics to do this
simply to get around the sequential scan).

Could I use a functional index, maybe?

Regards,
Ed

On Tue, 14 Oct 2003 da****@crystalballinc.com wrote:
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=e...cari.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
ma*****@mascari.com


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org


--
Darren Ferguson


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #14
Edmund Dengler wrote:
This just returns us to the problem that the use of a function causes a
sequential scan (as the select has to essentially apply the function to
each row). I would need to store a dummy value into the field (it is an
int, so I could store -1, but it breaks my sense of aesthetics to do this
simply to get around the sequential scan).

Could I use a functional index, maybe?


Yes, but I think you have to write a little wrapper:

CREATE TABLE foo (
key integer not null,
value text);

CREATE FUNCTION toValue(text) RETURNS text AS '

SELECT COALESCE($1, '''');

' LANGUAGE 'SQL' IMMUTABLE;

CREATE INDEX i_foo1 ON foo(toValue(value));

And always be sure to use the function in the query:

SELECT *
FROM foo
WHERE toValue(value) = '';

For fun:

SET enable_seqscan to off;

EXPLAIN SELECT * FROM foo WHERE toValue(value) = 'Mike';

should produce an Index Scan....

HTH,

Mike Mascari
ma*****@mascari.com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #15
Edmund Dengler <ed*****@eSentire.com> writes:
... I have no real choice in this as there is no way to specify that
NULL == NULL.


The conventional wisdom on this is that if you think you need NULL ==
NULL to yield true, then you are misusing NULL, and you'd better
reconsider your data representation. The standard semantics for NULL
really do not support any other interpretation of NULL than "I don't
know what this value is". If you are trying to use NULL to mean
something else, you will face nothing but misery. Choose another
representation for whatever you do mean.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #16
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=e...cari.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
ma*****@mascari.com


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #17
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=e...cari.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
ma*****@mascari.com


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org


--
Darren Ferguson
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #18
This just returns us to the problem that the use of a function causes a
sequential scan (as the select has to essentially apply the function to
each row). I would need to store a dummy value into the field (it is an
int, so I could store -1, but it breaks my sense of aesthetics to do this
simply to get around the sequential scan).

Could I use a functional index, maybe?

Regards,
Ed

On Tue, 14 Oct 2003 da****@crystalballinc.com wrote:
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=e...cari.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
ma*****@mascari.com


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org


--
Darren Ferguson


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #19
Edmund Dengler wrote:
This just returns us to the problem that the use of a function causes a
sequential scan (as the select has to essentially apply the function to
each row). I would need to store a dummy value into the field (it is an
int, so I could store -1, but it breaks my sense of aesthetics to do this
simply to get around the sequential scan).

Could I use a functional index, maybe?


Yes, but I think you have to write a little wrapper:

CREATE TABLE foo (
key integer not null,
value text);

CREATE FUNCTION toValue(text) RETURNS text AS '

SELECT COALESCE($1, '''');

' LANGUAGE 'SQL' IMMUTABLE;

CREATE INDEX i_foo1 ON foo(toValue(value));

And always be sure to use the function in the query:

SELECT *
FROM foo
WHERE toValue(value) = '';

For fun:

SET enable_seqscan to off;

EXPLAIN SELECT * FROM foo WHERE toValue(value) = 'Mike';

should produce an Index Scan....

HTH,

Mike Mascari
ma*****@mascari.com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #20
Edmund Dengler <ed*****@eSentire.com> writes:
... I have no real choice in this as there is no way to specify that
NULL == NULL.


The conventional wisdom on this is that if you think you need NULL ==
NULL to yield true, then you are misusing NULL, and you'd better
reconsider your data representation. The standard semantics for NULL
really do not support any other interpretation of NULL than "I don't
know what this value is". If you are trying to use NULL to mean
something else, you will face nothing but misery. Choose another
representation for whatever you do mean.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #21
I guess it comes back to the semantics of NULL. As has been pointed out in
many a database course, what we mean by NULL changes, and how we want to
use NULL changes on circumstances.

Normally, when I am comparing rows, I do want NULL <> NULL. In
this specific instance, no value has been assigned to the specific
column for this row, so NULL is appropriate. However, there are cases
where I am trying to explicitely test for existence of a specific row
in the table, and in this case, I _do_ want a NULL == NULL type of
comparison. I could try and specify a dummy value (in this case, I could
put in -1), but then I am trying to create a second class of NULLs, and
this is usually not considered good design.

Note that as a prime example of how postgresql itself is not "consistent"
(in the strictest sense) is GROUP BY which treats NULL == NULL
(interesting side bar, is there a way to cause GROUP BY to treat NULLs as
not equal to each other?). In a theoretical question, how is this
justified if NULL should not equal to NULL (other than "it is in the
spec")?

Also, is there a particular reason for not having a strict equality
operator (or is it simply because it is not in the specification)?
Performance? No support from the back-end? Something else?

Regards,
Ed

On Wed, 15 Oct 2003, Tom Lane wrote:
Edmund Dengler <ed*****@eSentire.com> writes:
... I have no real choice in this as there is no way to specify that
NULL == NULL.


The conventional wisdom on this is that if you think you need NULL ==
NULL to yield true, then you are misusing NULL, and you'd better
reconsider your data representation. The standard semantics for NULL
really do not support any other interpretation of NULL than "I don't
know what this value is". If you are trying to use NULL to mean
something else, you will face nothing but misery. Choose another
representation for whatever you do mean.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #22
Edmund Dengler <ed*****@eSentire.com> writes:
Normally, when I am comparing rows, I do want NULL <> NULL.
No, you still haven't got the point. NULL is not equal to NULL, and
it is not not-equal-to NULL either. The result of the comparison is
NULL, not true or false. This is consistent with the interpretation
of NULL as "I don't know the value". If you don't know what the value
is, you also don't know whether it is equal to some other value.
Note that as a prime example of how postgresql itself is not "consistent"
(in the strictest sense) is GROUP BY which treats NULL == NULL
Shrug ... the standard tells us to do that. SQL has never been held up
as a model of consistency.
Also, is there a particular reason for not having a strict equality
operator (or is it simply because it is not in the specification)?


The existing operators *are* strict (which is defined as NULL in yields
NULL out). You could build a set of non-strict comparison operators if
you had a mind to. IIRC you would lose some potential hashtable
optimizations, but in the main it would work.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #23
Tom Lane wrote:
Edmund Dengler <ed*****@eSentire.com> writes:

Normally, when I am comparing rows, I do want NULL <> NULL.


No, you still haven't got the point. NULL is not equal to NULL, and
it is not not-equal-to NULL either. The result of the comparison is
NULL, not true or false. This is consistent with the interpretation
of NULL as "I don't know the value". If you don't know what the value
is, you also don't know whether it is equal to some other value.


In these cases, it is recommended to either find a value which is out of
range, normally, and use that in place of NULL. For examples:

-1
10^32-1
"."
the_oldest_possible_date BC
the_furthest_away_date AD

Another way is to put an additional column in, but I think this still
has problems if you are trying to get a query to return values in a
column that has NULLs and you are querying against the column that has
the NULLs.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #24
On Wed, 15 Oct 2003, Edmund Dengler wrote:
Note that as a prime example of how postgresql itself is not "consistent"
(in the strictest sense) is GROUP BY which treats NULL == NULL
(interesting side bar, is there a way to cause GROUP BY to treat NULLs as
not equal to each other?). In a theoretical question, how is this
justified if NULL should not equal to NULL (other than "it is in the
spec")?


Because it's not defined in terms of equality. ;) GROUP BY is defined by
value "distinct"ness, where distinct has a very specific definition in the
spec (which treats two NULL values as not distinct).

You might actually be able to find some way to use that to your advantage,
but I'm not sure how.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #25

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Deane Barker | last post by:
I have a function that selects a file to include, then includes is. The file is including within the function, like so: function include_file($file_name) { require $file_name; return; }
2
by: Chris Haynes | last post by:
Hello all, I have a structure: typedef struct UVstruct { float u, v; } uv; Inside a function (A) i declare a pointer to an instance of this structure:
4
by: Ralph Noble | last post by:
Does anyone know of a string function in Access that will allow me to count the number of instances one string occurs within another? Or if there is some sort of word count function? If there is,...
8
by: jody.florian | last post by:
Hi, I'm trying to use preg_replace_callback within a method. The preg_replace_callback() & mycallback() pair will only be used by this method, and this method will probably only be called once...
9
by: 47computers | last post by:
Pretty new to PHP, I recently started learning about error trapping. As of right now, I include the following into a page in my website: -------BEGIN PASTE-------- error_reporting(E_ERROR |...
1
by: Elmo Watson | last post by:
I've inherited a project that has quite a few Table rows - inside each cell, within the row are a couple of textboxes ("in" and "out"), along with a "total" label. Also - this is all within a...
0
by: robgallen | last post by:
I have 2 user controls within a master page, and I would like one of them to call a function in the other. All the examples I have seen involve a page communicating with the Master page, or with...
4
by: Harlequin | last post by:
I have a question concerning the need to trigger events within a "child" subform which is itself enbedded within a master "parent" form and which is accessible via a tab in the parent form. Becuase...
5
by: hurricane_number_one | last post by:
I'm trying to have a class, which uses threads be able to raise events to the form that created it. I've seen solutions around the net for this, but I didn't really like their implementation. ...
7
by: vunet | last post by:
I am still not clear about how to reference an object within another object to pass first object to a function: var Parent = { myFunc : function(){ alert("Parent = "+this) }, Child : { //how...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.