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 [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?
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?
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?
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
[GENERAL] 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 [EMAIL PROTECTED])
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
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?
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=enlr=ie=UTF-8oe=UTF-8frame=rightth=26ef31219ae11442seekm=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])
