Thread: benefits of an Array Column?
I was reading the o'reilly practical PostgreSQL book and came across the chapter that mentioned the Array Column. I fail to see the scenarios where this would be an useful feature. How is this better than linking to another table that could serve the same purpose? For instance, the example used in the book metions the "favorite books of employees" by using a column for employee name and array column of favorite book titles for that employee. I could also approach this by having an employee master table and book titles master table then creating a third table between these two tables that would have rows that matched up employees with their favorite titles. Jared
jarednevans@yahoo.com writes: > I fail to see the scenarios where this would be an useful feature. How > is this better than linking to another table that could serve the same > purpose? A relational-database purist would probably tell you you shouldn't ever use an array ;-). If you need the elements of the array to be individually searchable then the linked-table approach is almost certainly better, but if not then I think an array is often a good engineering compromise. > For instance, the example used in the book metions the "favorite books > of employees" by using a column for employee name and array column of > favorite book titles for that employee. I agree, that's a lousy example. Here's one that is maybe a little silly: consider teaching a machine to play tic-tac-toe by giving it a table containing every possible board position and the best move. You could handle this naturally by doing, say, create table positions ( board char(1)[3][3], char(1) tomove, int movex, int movey, primary key(board, tomove) ); insert into positions values('{{X,-,O},{-,X,O},{-,-,-}}', 'X', 3, 3); -- tediously many more inserts ... -- to play: select * from positions where board = '{...}' and tomove = 'X'; In this case an array is a perfectly natural way to represent the data value. Furthermore, it wouldn't make any sense at all to break this down into two linked tables. If you did, the referenced table would have to have entries representing, say, 'X at position 1,1', which is not a particularly interesting thing by itself (whereas books, in the favorite-books example, certainly are individually interesting). Plus the query you want to be fast would be horridly slow, as it would be doing something like a ten-way join to identify the particular positions row you need. I think probably the rule of thumb is that arrays work when you have data items that have an array-like structure, but that substructure isn't interesting from the standpoint of the database structure. regards, tom lane
Tom Lane wrote: > jarednevans@yahoo.com writes: >>I fail to see the scenarios where this would be an useful feature. How >>is this better than linking to another table that could serve the same >>purpose? > I think probably the rule of thumb is that arrays work when you have > data items that have an array-like structure, but that substructure > isn't interesting from the standpoint of the database structure. I agree, but would add that arrays are often very useful as non-persistent structures for processing data, e.g. in PL/pgSQL functions. Joe
Is there not a speed advantage using arrays in the right place instead of tables ? János On Jun 20, 2004, at 3:29 PM, Joe Conway wrote: > Tom Lane wrote: >> jarednevans@yahoo.com writes: >>> I fail to see the scenarios where this would be an useful feature. >>> How >>> is this better than linking to another table that could serve the >>> same >>> purpose? > >> I think probably the rule of thumb is that arrays work when you have >> data items that have an array-like structure, but that substructure >> isn't interesting from the standpoint of the database structure. > > I agree, but would add that arrays are often very useful as > non-persistent structures for processing data, e.g. in PL/pgSQL > functions. > > Joe > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > > ------------------------------------------ "The shortest route between two points is the middleman" Ayn Rand