SQL Table Array Values as non null

[ianmrose-cm]

I have several table definitions which use arrays and I noticed that the diesel-cli migration command always generates (at least for me) the array's contained type as an Option of that type. I did some research on whether or not SQL DBs typically have the NOT NULL capability for values within an array and didn't come up with much, so at this point I am assuming there is no such feature which is why diesel-cli generates the schema using Option for arrays. However, from a practical standpoint I have no value in null entries for these arrays, so in order to simplify & de-dupe my Rust struct models I would like to ignore any values which are null.

Does Diesel have any support for treating array's as having non-null (non-option) types? If not, would it be something Diesel could consider adding, or should this be handled outside Diesel's scope as an extension?

I have a top-level Rust structure which contains all the columns of the DB as fields which I would like to use as both Queryable and Insertable to save on boilerplate, so that is why I am looking into this. It isn't a major problem, but it would cut my struct definitions in half right now because I don't really need anything else, my usage for the SQL DB and Diesel is quite basic.

[weiznich]

While you personally don't have any null entries in your array column, it is really possible to get them. Even by accident, for example by updating the n+2 array element. As postgresql offers no way to signal that this cannot happen we have decided that array elements are always considered to be nullable. The migration guide covers this topic and lists some possible ways to get the old behavior back.

[urkle]

@weiznich can an example deserialize_as be thrown into the docs on how to actually accomplish this task? the "patch_file" approach is rather unreliable IMHO, and it would be nice to have some generic deserialize_as/serialize_as for array types where I do not want to allow null's (and would simply want them thrown out on deserialize)

[weiznich]

@urkle I'm happy to merge a PR for that.

[McDic]

Following SQL will create a table with an array column with non-nullable entries.

CREATE TABLE some_table (
    id INT PRIMARY KEY,
    some_data BIGINT[] NOT NULL,
    CONSTRAINT check_data CHECK(num_nulls(VARIADIC some_data) = 0)
);

I agree that making Diesel able to understand such checks from raw SQL code would be tricky. I guess I will use custom type and deserializations described here.

[onx2]

I'm running into a similar scenario and I was wondering if Diesel could read a check constraint to know that the type should be Option<Vec<String>>:

CREATE TABLE some_table (
  ...,
  some_col TEXT[] NULL 
        check (
            some_col <> '{}' and
            array_position(some_col, null) is null
        )
);

Using this constraint I can guarantee these two updates fail:

... SET some_col = '{}';
... SET some_col = '{null}';

I'm okay with using the deserialize functionality you guys are talking about, but I figured I would throw out a way you can guarantee Option<Vec<String>> at least from a Postgres perspective. It would be cool if Diesel could somehow read the check constraint and infer the new type but I see how that might be totally unreasonable.

[weiznich]

That would require parsing and evaluating these conditions, which essentially means parsing and evaluating the Postgres SQL dialect. At least in my opinion that's something out of scope for diesel.

[onx2]

I think this is working... @weiznich Does this look right for a custom deserialize_as for Option<Vec<Option<String>>> to Option<Vec<String>>?

#[derive(Debug, Clone, PartialEq)]
pub struct OptionalVec(Option<Vec<String>>);

impl From<Option<Vec<Option<String>>>> for OptionalVec {
    fn from(o: Option<Vec<Option<String>>>) -> Self {
        match o {
            None => OptionalVec(None),
            Some(items) => OptionalVec(items.into_iter().filter(|item| item.is_some()).collect()),
        }
    }
}

#[derive(Queryable, Debug, Clone, PartialEq)]
pub struct User {
    pub id: Uuid,
    #[diesel(deserialize_as = Option<Vec<Option<String>>>)]
    pub some_col: OptionalVec,
}

[weiznich]

That seems to be reasonable.