What are the proper use-cases for the PostgreSQL Array Datatype?

I totally agree with @marc. Arrays are to be used when you are absolutely sure you don't need to create any relationship between the items in the array with any other table. It should be used for a tightly coupled one to many relationship.
A typical example is creating a multichoice questions system. Since other questions don't need to be aware of the options of a question, the options can be stored in an array.
e.g

CREATE TABLE Question (
  id integer PRIMARY KEY,
  question TEXT,
  options VARCHAR(255)[],
  answer VARCHAR(255)
)  

This is much better than creating a question_options table and getting the options with a join.


One incredibly handy use case is tagging:

CREATE TABLE posts (
    title TEXT,
    tags TEXT[]
);

-- Select all posts with tag 'kitty'
SELECT * FROM posts WHERE tags @> '{kitty}';

An array should not be used similar to a relation. It should rather contain indexed values that relate to one row very tightly. For example if you had a table with the results of a football match, than you would not need to do

id team1 team2 goals1 goals2

but would do

id team[2] goals[2]

Because in this example, most would also consider normalizing this into two tables would be silly.

So all in all I would use it in cases where you are not interested in making relations and where you else would add fields like field1 field2 field3.


The Postgresql documentation gives good examples:

  CREATE TABLE sal_emp (
     name            text,
     pay_by_quarter  integer[],
     schedule        text[][]
 );

The above command will create a table named sal_emp with a column of type text (name), a one-dimensional array of type integer (pay_by_quarter), which represents the employee's salary by quarter, and a two-dimensional array of text (schedule), which represents the employee's weekly schedule.

Or, if you prefer:

 CREATE TABLE tictactoe (
     squares   integer[3][3] );

Tags:

Postgresql