DEV Community

Adrien Olivon
Adrien Olivon

Posted on

2

Are PostgreSQL's Arrays Strongly Typed ? (AHAH NO)

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[]
Enter fullscreen mode Exit fullscreen mode

Let's play a game 🎰

Hey! Do you know postgreSQL arrays ?

CREATE TABLE users (
    teams text[]
);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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...
Enter fullscreen mode Exit fullscreen mode

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}}}}}}');
Enter fullscreen mode Exit fullscreen mode

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[]
Enter fullscreen mode Exit fullscreen mode

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][][][][][][][][]
);
Enter fullscreen mode Exit fullscreen mode

my pro tips for you is to suffix your arrays like this:

 teams text[7][1][3][7][0][5]
Enter fullscreen mode Exit fullscreen mode

so you have "soleil" (french word for sun) when you turn your screen! 😎

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay