Difference between an array and single-column in SQL

Question

For databases that support arrays -- for example, Postgres -- what would be the difference between the following two items:

`name`  `field_a` (row array)
Tom      [1, 2, 3]

And:

`name`   `field_a` (single column)
Tom       1
Tom       2
Tom       3

The above would be two 'variations' of combining two tables:

• name

  `name`
   Tom
  

• numbers

  `field_a`
  1
  2
  3
  

If the array version vs the other version are not interchangeable, what are the main differences between the two?

Answer 1

• Array stores the data in a single row and it needs some kind of processing (Different for different databases) before accessing/searching/sorting the particular value. It saves the size of table as repeating data occurs in single row but ultimately had more processing time when it comes to updating/searching/sorting and many more operations on the data stored as array.

• Single values in each row is more preferred in databases as it is easy to find the record, update particular data, sort the data and many more operations.

So according to me only insertion of array is faster than the individual values and it saves some space of the table but all other operations will be time consuming. So it is better to store individual values in each row for database.

Databases are designed to handle single values more easily and operations on single values are faster than the arrays.

Simple example of complexity from your question: Replace 2 with 5 in field_a for name = 'Tom'