PostgreSQL's Arrays are a bit weird

TL;DR: “declaring the array size or number of dimensions [...] is simply documentation”

so following type definitions are equivalent 🙃 :

 teams text[][]
 teams text[1][1][1]
 teams text[3][5][4][10]
 teams text[]

---

Question

Given this basic schema of a tables users with a team a column string array:

CREATE TABLE users (
    teams text[]
);

It means that a user has zero or many teams and a team is a String right ?

But do you know how to insert data in it ? try to eliminates instructions that leads to an error

INSERT INTO users VALUES ('Nantes'); -- 1
INSERT INTO users VALUES ('{Rennes}'); -- 2
INSERT INTO users VALUES ('{{Rennes}}'); -- 3
INSERT INTO users VALUES ('{{Rennes, Chateaugiron}}'); -- 4
INSERT INTO users VALUES ('{{Nantes}, {Paris, Levallois}'); -- 5
INSERT INTO users VALUES ('{{Rennes, Chateaugiron}, {Bruc, Cesson}}'); -- 6
INSERT INTO users VALUES ('{{Rennes, Chateaugiron}, {Bruc, Cesson}, {Chateaugiron, Rennes}}'); -- 7
INSERT INTO users VALUES ('{{{{{{Rennes, Chateaugiron}, {Bruc, Cesson}, {Chateaugiron, Rennes}}}}}}'); -- 8
INSERT INTO users VALUES ('{{{{{{{Rennes, Chateaugiron}, {Bruc, Cesson}, {Chateaugiron, Rennes}}}}}}}'); -- 9

Answers

❌The errors are 1, 5 and 9:

❌INSERT INTO users VALUES ('Nantes'); -- 1
ERROR:  malformed array literal: "Nantes"
LINE 1 : INSERT INTO users VALUES ('Nantes');
                                    ^
DETAIL : Array value must start with "{" or dimension information.

✅INSERT INTO users VALUES ('{Rennes}'); -- 2
✅INSERT INTO users VALUES ('{{Rennes}}'); -- 3
✅INSERT INTO users VALUES ('{{Rennes, Chateaugiron}}'); -- 4
❌INSERT INTO users VALUES ('{{Nantes}, {Paris, Levallois}}'); -- 5
ERROR:  malformed array literal: "{{Nantes}, {Paris, Levallois}"
LIGNE 1 : INSERT INTO users VALUES ('{{Nantes}, {Paris, Levallois}');
                                    ^
DETAIL : Multidimensional arrays must have sub-arrays with matching dimensions.

✅INSERT INTO users VALUES ('{{Rennes, Chateaugiron}, {Bruc, Cesson}}'); -- 6
✅INSERT INTO users VALUES ('{{Rennes, Chateaugiron}, {Bruc, Cesson}, {Chateaugiron, Rennes}}'); -- 7
✅INSERT INTO users VALUES ('{{{{{{Rennes, Chateaugiron}, {Bruc, Cesson}, {Chateaugiron, Rennes}}}}}}'); -- 8
❌INSERT INTO users VALUES ('{{{{{{{Rennes, Chateaugiron}, {Bruc, Cesson}, {Chateaugiron, Rennes}}}}}}}'); -- 9
ERROR:  number of array dimensions (7) exceeds the maximum allowed (6)
LIGNE 1 : INSERT INTO users VALUES ('{{{{{{{Rennes, Chateaugiron}, {Br...

So 1) is invalid because 'Nantes' is not an array

Whereas 7) is totally OK : '{{Rennes, Chateaugiron}, {Bruc, Cesson}, {Chateaugiron, Rennes}}'
Postgres does not check the dimensions, so you can give an array of array of array of ... as long as you got the final types (here text) and each final array has the same length Postgres lets you pass.
So this is also valid:

✅INSERT INTO users VALUES ('{{{{{{Rennes, Chateaugiron}}}}}}');

Doc from Postgres says:

The current implementation does not enforce the declared number of dimensions either. Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring the array size or number of dimensions in CREATE TABLE declaring the array size or number of dimensions [...] is simply documentation

In short:

declaring the array size or number of dimensions [...] is simply documentation

so following type definitions are equivalent:

 teams text[][]
 teams text[1][1][1]
 teams text[3][5][4][10]
 teams text[]

---

By the way, 5) is invalid because '{{Nantes}, {Paris, Levallois}}' has 2 arrays with different size {Nantes} size=1, {Paris, Levallois} size=2.
It probably makes sense in implementation details but from a usage perspective it's weird to have strictness here and not before.

9) is invalid because it exceeds the maximum of array dimensions which is 6. But you can still define your type with more than six dimensions, this one is valid:

CREATE TABLE users (
    teams text[3][5][4][10][][][][][][][][]
);