9
CREATE TABLE user (
...
columnName int ARRAY[7][7] DEFAULT null,
...
)

I have to create a table where one of the columns has to store a 7x7 array, but i can't figure it out!

Is it even possible in SQL?

-- UPDATE So, I've decided I am going to cheat it and store it as a TEXT and then with code I'll work with that string

Laura C. Cencerrado
  • 159
  • 1
  • 2
  • 8
  • 4
    Some databases, such as postgres support this, but in terms of relational design it is not a good idea in the long run. – Lucas Jun 05 '15 at 10:34
  • 3
    This is not part of the SQL standard, except for strings composed of characters. Some databases might support arrays or similar constructs. – Gordon Linoff Jun 05 '15 at 10:36
  • 2
    In standard SQL, there's one data type *designed* for holding multiple values - the table. You can construct one with two columns with meaningful names for the indices, and constraint those to only contain values between 0-6 (or 1-7, depending on your outlook), one column with a meaningful name for the *values*, and then additional column(s) to foreign key back to the `user` table. – Damien_The_Unbeliever Jun 05 '15 at 10:45
  • Maybe you want to read [this...](http://stackoverflow.com/questions/1138777/how-to-represent-a-2-d-data-matrix-in-a-database) that's one way to solve your problem... – Aleksandar Miladinovic Jun 05 '15 at 11:00
  • Will your database have any interactionwith this column, accept from storing it? If the answer is yes, then it's a teribble idea. – Zohar Peled Jun 05 '15 at 12:11
  • Which SQL instance are you using? If MSSQL, then you could create your own type. https://msdn.microsoft.com/en-us/library/ms131064.aspx .Beware of the best practices, on the long term could have a performance impact. – Hozikimaru Jun 05 '15 at 19:51
  • Something like [this](http://stackoverflow.com/a/6361461/411282) might work, with row and column seq #s... or I see this is similar to what @Damien_The_Unbeliever suggests, and his suggestion may be simpler. – Joshua Goldberg Oct 21 '16 at 15:14

1 Answers1

6

SQL 2003 (and to a lesser extent SQL 99) provide array types, see for instance here: http://farrago.sourceforge.net/design/CollectionTypes.html I've used them extensively with PostgreSQL to simplify my designs. For clients that don't support array semantics yet, it's possible to provide views which "unnest" the arrays.

Isac Casapu
  • 1,163
  • 13
  • 21