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. Example in my database with two different fields. However, once I'm getting this with node-pg, I get a string instead of a JSON empty array.
PostgreSQL returns no particular error, and in pgadmin I'm getting in a JSONB type column. 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. SELECT COALESCE(ak.singers, tojsonb(''::text)) AS singers. 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). If you choose to reuse employee you might want to scrub away the bad data later.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. It also is not clear to me why you are inserting back into the employee table which has non usable data, rather than inserting into a new table. This assumes that your timestamp data is formatted as follows: DD/MM/YYYY HH:MI:SS PM (e.g. Select 14, coalesce(to_timestamp(dojo, 'DD/MM/YYYY HH:MI:SS PM'), current_timestamp) Something like the following is what I believe you intend to do: insert into employee (eid, dojo) Instead, you should be using to_timestamp(), which can parse a string and return a timestamp. The output of to_char is a string, and the way you are using it would cause Postgres to reject it. As others have pointed out, one solution would be to use current_timestamp as a placeholder.Īnother problem you have is that you are incorrectly using to_char to format your timestamp data. This won't work, because empty string is not a valid timestamp. First, you are trying to insert an empty string '' to handle NULL values in the dojo column. Your current query has severals problems, two of which I think my answer can resolve.
0 kommentar(er)
