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[]
Let's play a game 🎰
Hey! Do you know postgreSQL arrays ?
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 ? 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...
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 TABLEdeclaring 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[]
What do you think about that ? If you have more info about it I'll be happy to hear about it!
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 exeeds the maximum of array dimensions which is 6. But no worries you can still define your type with more than six dimensions, this one is valid:
CREATE TABLE users (
teams text[3][5][4][10][][][][][][][][]
);
my pro tips for you is to suffix your arrays like this:
teams text[7][1][3][7][0][5]
so you have "soleil" (french word for sun) when you turn your screen! 😎
Top comments (0)