Re: [GENERAL] SET within a function?
Tom Lane wrote: Edmund Dengler <[EMAIL PROTECTED]> 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
Re: [GENERAL] SET within a function?
Edmund Dengler <[EMAIL PROTECTED]> 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
Re: [GENERAL] SET within a function?
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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SET within a function?
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/TheThirdManifesto.web/Missing-info-without-nulls.pdf Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SET within a function?
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
Re: [GENERAL] SET within a function?
> 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SET within a function?
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] SET within a function?
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 <[EMAIL PROTECTED]> 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
Re: [GENERAL] SET within a function?
On Mon, Oct 13, 2003 at 21:16:33 -0400, Edmund Dengler <[EMAIL PROTECTED]> 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
