![]() Test=# SELECT row(NULL, NULL) IS row(NULL, NULL) The “IS” keyword won’t work if you are comparing the output of two “row” functions: However, there is one thing which might as a surprise to some people. True is only returned if all fields are NULL. This is not true for row(10, NULL) – in this case the query returns false. In short: The entire tuple is considered to be NULL by PostgreSQL: What is important to see is that a row can be compared to a single NULL value. ![]() Test=# SELECT row(NULL, NULL) = row(NULL, NULL) Īs expected the result is NULL because all values on both sides are “undefined” and therefore there is no way the output of this query can ever be true. In general pretty much the same rules will apply in this case. Some of my readers might already have seen the row() function, which can be used to form a tuple on the fly. NULL is key and therefore it is important to also check some of the lesser known aspects and corner cases. ![]() However, there is more to NULL than just simple operations. In this case the result is true because “IS” actually check if both value are indeed NULL.Ĭonsequently the next query is going to return false: To figure out if two values are actually NULL we have to use the following syntax: Is it identical? Again: We have absolutely no idea – the result is undefined. We don’t know how much cash is in your left pocket and we got no idea how much there is in your right pocket. Thus the result of this query has to be NULL. It might be very well so, but we simply don’t know. Is the amount of cash in your pockets the same? We don’t know. ![]() Why is that? Suppose you got 10 bucks in your left pocket and nobody knows how much cash you got in your right pocket. Many people assume that the output of this query is actually “false”, which is not correct. The following example shows a mistake commonly made by many developers: Before digging deeper into NULL it is necessary to take a look at the most basic rules. NULL values in PostgreSQL: Basic rulesįirst of all: NULL is a super useful thing in SQL and people should be aware of the details associated with it. So using the word “unknown” to describe NULL in SQL is really better than to use word “empty”, which can be pretty misleading in my judgement. If we don’t know how much cash you got, you might still be a millionaire. Many books state that NULL means “empty” but I think that is not the ideal way to see things: If you wallet is empty, your financial situation is perfectly defined (= you are broke). After years of software development, some might still wonder: What is a NULL value? What does it really mean and what is its purpose? The general rule is: NULL basically means “undefined”. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |