| ID | CONTENT | ISNULL | ISEMPTY | BLANK | Note: Remember to change the default null output from '' to (null) psql -P 'null=(null)' SELECTĬASE WHEN content IS NULL THEN 1 ELSE 0 END AS isnull,ĬASE WHEN content = '' THEN 1 ELSE 0 END AS isempty,ĬASE WHEN content = ' ' THEN 1 ELSE 0 END AS blank Here we have a value that's explicitly NULL, an empty string, a string with a single space in it, and another with a 1-character string. INSERT INTO test (id, content) VALUES (4, 'x') INSERT INTO test (id, content) VALUES (3, ' ') INSERT INTO test (id, content) VALUES (2, '') INSERT INTO test (id, content) VALUES (1, NULL) We'll demonstrate this behaviour with a simple table and some data: CREATE TABLE test ( In Oracle, NULLs and empty strings are equivalent when it comes to values stored in the database. Oracle and PostgreSQL behave similarly in many cases, but one way they differ is in their treatment of NULLs and empty strings. Concatenating NULL values with non-NULL characters results in that character in Oracle, but NULL in PostgreSQL. Oracle reads empty strings as NULLs, while PostgreSQL treats them as empty. LEFT JOIN cte_tags ON cte_tags.object_id = objects.SUMMARY: This article discusses the differences between how Oracle and PostgreSQL evaluate NULL characters and empty strings. or use array-to-array concatenation: WITH cte_tags AS ( If you really want an empty array instead of NULL in your results, you can use the COALESCE function.: WITH cte_tags AS ( Instead of an array with a single element of NULL, you will now get NULL instead of an array. LEFT JOIN cte_tags ON cte_tags.object_id = objects.id INNER JOIN tags ON tags.id = taggings.tag_id Perhaps this answer is a little late, but I wanted to share with you that another querying strategy is possible as well: performing the aggregation in a a separate (common) table expression. But you need to apply that to the array elements instead of the array, which, in your case, would not provide a solution. Incidentally, the statement in the documentation about using coalesce() is a bit crummy: what is meant is that if you do not want NULL as a result, you can use coalesce() to turn that into a 0 or some other output of your choosing. The trick here is that the first (and only) element in a array has a length of 0, so if any data is returned from tags you return the aggregate, otherwise construct an empty array of the right type. This assumes that the tags are of text type (or any of its variants) modify the cast as required. LEFT JOIN tags ON tags.id = taggings.tag_id LEFT JOIN taggings ON objects.id = taggings.object_id If you want to convert that to an empty array, then you need to do some minor magic: SELECT objects.id,ĬASE WHEN length((array_agg(tags.tag))) > 0 The documentation says that an array containing NULL is returned. So in your case, just replace array_agg(tags.tag) You can identify an object with no tags (or in general, tell when a LEFT JOIN found no matches) by checking whether the field on the other side of the join condition is null. Your application logic and/or integrity constraints may not allow this second case, but that's all the more reason not to suppress a null tag if it does manage to sneak in. You might be tempted to blindly replace with in the output, but then you lose the ability to distiguish between objects with no tags and tagged objects where tags.tag is null. This does not apply to your query, because of the way a LEFT JOIN behaves - when it finds zero matching rows, it returns one row, filled with nulls (and the aggregate of one null row is an array with one null element). The docs say that when you are aggregating zero rows, then you get a null value, and the note about using COALESCE is addressing this specific case.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |