You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
With the following two records inserted into the foo table, a call ListFoos succeeds.
INSERT INTO foo (id, tags) VALUES ('one', NULL);
INSERT INTO foo (id, tags) VALUES ('two', array['tag']);
However, if a NULL item exists in the tags array, an error will be returned.
INSERT INTO foo (id, tags) VALUES ('one', array[NULL]);
sql: Scan error on column index 1, name "tags": pq: parsing array element index 0: cannot convert nil to string
That's not a problem, right? We'll just mark the tags column as not null. Sadly, that just means that the column must contain an array value. The array itself can still contain NULL values. It does not appear to be easy to prevent NULL values in arrays. This was the [only solution](Declare a column of type 'not-null-string' array in postgresql - Stack Overflow) I could find, and it's pretty gross.
Note that pq.StringArray also does not support NULL array values.
I don't know if this is helpful for anyone. I was also struggling with arrays containing only NULL values.
If you don't need to retain the NULL in your arrays, I found the below to be a decent work around by essentially just omitting NULL values.
This would work for POSTGRES, filtering out NULLs and casting the array type.
SELECTe.id,
e.name,
e.description,
e.datetime,
e.status,
e.datetime< NOW() AS elapsed,
ARRAY_AGG(c.id) FILTER (WHEREc.idIS NOT NULL)::int[] AS category_id,
ARRAY_AGG(c.name) FILTER (WHEREc.idIS NOT NULL)::text[] AS category_name
FROM
events AS e
LEFT JOIN category_links AS l ONe.id=l.event_idLEFT JOIN categories AS c ONl.category_id=c.idGROUP BYe.id,
e.name,
e.description,
e.datetime,
e.status;
Activity
kyleconroy commentedon Dec 16, 2019
A
sql.NullStringslice, paired withpq.Arrayworks correctly.dimmerz92 commentedon Apr 26, 2024
I don't know if this is helpful for anyone. I was also struggling with arrays containing only NULL values.
If you don't need to retain the NULL in your arrays, I found the below to be a decent work around by essentially just omitting NULL values.
This would work for POSTGRES, filtering out NULLs and casting the array type.