[Doc]: Item types supported for arrays

[natalie-o-perret]

Steps to reproduce

I've been reading about array, and I was wondering if there was a list of the item types supported for array and ranges:

• https://www.npgsql.org/doc/types/basic.html
• https://www.postgresql.org/docs/current/arrays.html

I can't find any... I mean the documentation can make you imagine that you can actually define an array or arrays or arrays of IP Address... which I doubt can work. It's very likely only 1D arrays, right?

But reading a further it gets a little fuzzy:

• https://www.postgresql.org/docs/13/extend-type-system.html
• https://www.postgresql.org/docs/13/datatype.html
• https://stackoverflow.com/questions/34403844/postgresql-multidimensional-arrays
• https://stackoverflow.com/questions/29186515/is-there-a-way-to-use-arrays-in-entity-framework-postgresql

Some talk about multi dimensional arrays... others just 1D arrays of a given base type... it's all confusing, so I would like to what is supported

The issue

No clear documentation about what types are supported as array items.

Further technical details

Npgsql version: 5.0.1.1
PostgreSQL version: PostgreSQL 10.5 (Ubuntu 10.5-2.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit
Operating system: (Client) Microsoft Windows 10 Enterprise

[roji]

We should indeed provide a bit of documentation.

At the ADO level, Npgsql supports all the array capabilities of PostgreSQL itself, so in general what you find in the PostgreSQL docs is supported. Note that PostgreSQL does not support arrays of arrays, but does support multi-dimensional arrays (you can see some examples in the docs). You can use either .NET arrays or generic Lists to map to PostgreSQL arrays.

Note, however, that the EF Core provider's multi-dimensional arrays support is very limited - basically stick to one-dimensional arrays.

[natalie-o-perret]

Note that PostgreSQL does not support arrays of arrays, but does support multi-dimensional arrays (you can see some examples in the docs). You can use either .NET arrays or generic Lists to map to PostgreSQL arrays.

@roji Hey thanks for your answer! Much appreciated!

One thing though, when you're saying:

Note that PostgreSQL does not support arrays of arrays, but does support multi-dimensional arrays (you can see some examples in the docs)

How does it work in npgsql to benefit from the multidimensional array support then?

NpgsqlDbType.Array | NpgsqlDbType.Array | NpgsqlDbType.SmallInt

[roji]

Nope, just NpgsqlDbType.Array | NpgsqlDbType.SmallInt. That corresponds to an array of any dimensions in PostgreSQL.

[natalie-o-perret]

Nope, just NpgsqlDbType.Array | NpgsqlDbType.SmallInt. That corresponds to an array of any dimensions in PostgreSQL.

@roji Alright, that's a little bit counterintuitive... just to make this super-duper clear, with NpgsqlDbType.Array | NpgsqlDbType.SmallInt, you can pass resp. int[], int [,], int[,,], int[,,,] and even bigger dimensions?

[roji]

Yes. I know it may seem a bit odd, but the way PostgreSQL handles type arrays, all arrays of a given element type - regardless of dimension - are of a single type; this is why a single NpgsqlDbType value maps to them all.

[roji]

BTW regardless of PostgreSQL, NpgsqlDbType.Array | NpgsqlDbType.Array | NpgsqlDbType.SmallInt doesn't really make any sense in C# - ORing something will itself doesn't produce any useful effect.

[natalie-o-perret]

@roji alrite alrite, thanks! It makes sense now.