1. 6
  1. 3

    I have a highly-downvoted answer on Stack Overflow arguing that you should avoid using the array datatype in Postgres because it violates 1NF. I see there is room for debate on whether or not that is true, but from a pragmatic standpoint, most ORMs and database abstraction layers I’ve used have trouble with array and nested table datatypes because the implementation is inconsistent across databases. The closer you cleave to the commonalities between databases, the less you tend to suffer, and since there is a 1:1 mapping from nested arrays to a tabular representation, you’re not really gaining anything meaningful by using the nested array implementation.

    I am in general happy to marry my applications to Postgres, but the benefit should outweigh the drawback.

    1. 2

      Interesting that you begin with “because it violates 1NF” and then spend the rest of the comment expanding on a completely different reason.

      Personally I probably wouldn’t be using arrays (unless it’s in really, really sweet spot), but at some point I realized that they conveniently keep the order of items, and that made me much warmer towards the entire idea.

      1. 2

        What I was trying to convey is that this used to seem very clear-cut to me but that I now see that it isn’t clear cut, however I would avoid doing it for pragmatic reasons aside from whether it is or is not 1NF.