Are PostgreSQL's Arrays Strongly Typed ? (AHAH NO)
Tags: postgres, tricks, kickflip360
Published: 2022-12-23 16:21:27

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

so following type definitions are equivalent 🙃 :
```sql
 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 ?

```sql
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**

```sql
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:

```sql
❌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](https://www.postgresql.org/docs/current/arrays.html) 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 🙃 :

```sql
 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 :thinking:.

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:
```sql
 teams text[7][1][3][7][0][5]
```
so you have "soleil" (french word for sun) when you turn your screen! :sunglasses: