Let's say I have the following field and values:
ArrayField
[1,2,3]
[3,4,5]
[null,2,3,4]
null
[]
[null]
Would postgres save all these values as-is. Or would any of these values be invalid or converted to something else -- for example having null as a field value (is that converted to [])?
In Postgres, all of your expressions are valid excepted for the last one: '{null,}', which would raise error:
malformed array literal: "{null,}"
It is also worth noting that there is a difference between null (and undefined value) and {} (an empty array). Say you want to write to a column has a not null constraint, null would fail while {} would be allowed.
-- create the table
create table t (array_field int[])
-- insert values
insert into t values
('{1,2,3}'),
('{3,4,5}'),
('{null,2,3,4}'),
(null),
('{}')
;
-- 5 rows affected
-- won't work
insert into t values ('{null,}');
-- ERROR: malformed array literal: "{null,}"
-- LINE 1: insert into t values ('{null,}')
-- ^
-- DETAIL: Unexpected "}" character.
-- check the results
select array_field, array_length(array_field, 1) from t
array_field | array_length
:----------- | -----------:
{1,2,3} | 3
{3,4,5} | 3
{NULL,2,3,4} | 4
null | null
{} | null
[] would be different than null in a practical usage (not just in terms of storage value)?
PostgreSQL will not silently modify your arrays. If you stored [NULL], it won't become []. Your last example ([NULL,]) is syntactically incorrect.
jsonborinteger[]?[]...........nulland an empty array[]are two different things