Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

As per subject, I have a field which is varchar in which i store some numbers separated by commas. The list grows and shrinks based on clients choices. I then use it in queries to check if a particular value is in that list against

value || ','

It is working well but was thinking of converting it to an integer array.

Would this be a better solution both in the technical/performance way and to properly program the database?

share|improve this question

1 Answer 1

up vote 7 down vote accepted

It's highly likely that the best approach will be a side-table of sometable(main_id, value) where you have a composite index on (main_id, value). This allows very fast lookups to see "for this mainid, does this value exist". This will let you enforce foreign key relationships. Unless you have a good reason, use this conventional relational approach.

Failing that, you can and should use an array field instead of a comma separated list. Using a comma-separated list is just downright horrid from a design point of view. It makes queries harder to write, more error prone, and forces you to do lots of slow and inefficient string manipulation and number parsing just for simple operations, and prevents any kind of integrity checking without very inefficient CHECK constaints or triggers. I think Bill nailed it with:

1,2,3,banana,5

With an array you can use the intarray extension to provide a GiST index that lets you quickly test if the array contains a given value using an indexable @> or <@ operation. You may want to add the btree_gist extension too, so you can create a composite GiST index of main_id, the_values_array, in case your queries are usually of the form:

WHERE main_id = blah AND the_values_array @> ARRAY[42]

(or have two separate indexes and see if it'll do a bitmap index scan).

You can't enforce a foreign key relationship into an array in PostgreSQL yet, though the feature seems to be on the way. You'd need to do it with somewhat complicated triggers in the mean time. Still, it's a lot better than a comma-separated list.

share|improve this answer
1  
+1 the only time a string with a comma-separated list is okay is when you always treat the list itself as an atomic data element. I.e. you never need to search for, sort by, or fetch individual elements using SQL semantics. –  Bill Karwin Dec 31 '13 at 18:26
1  
@BillKarwin Yup ... and even then, sooner or later that's likely to change so you do need to work with it in the DB. It's way easier to string_agg(myfield, ',') a side table to return a comma separated list when the client needs it than to go in the other direction later. Ditto arrays, array_to_string(somearray, ',') is pretty painless in a query. –  Craig Ringer Dec 31 '13 at 20:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.