1

In case you're not clear what a set is it allows a user to insert a value, check if a value is in the list and find an intersect (if value exist in two sets). Also a set will only hold a value once.

How do I use a set in SQL? I'm using sqlite but will be moving to postgresql. The most obvious option to me was have a Set table of SetID, SetValue with those two values being the primary key. However I have a feeling this wouldn't be efficient as SetID would be repeated many times. The other option is to have a table int SetId, blob SetArray but I'll need to implement set logic by hand.

What's the best way to do sets in SQL? For either sqlite or postgresql?

  • 3
    The first option of a table with two columns `SetID` and `SetValue` is by far the best in my opinion. The overhead of repeating the same ID numerous times is minimal and on balance usually significantly less than the cost of splitting blobs. The Primary key, or at least a unique index would ensure a value can only exist in a set once, then `EXISTS` and `INTERSECT/EXCEPT` operators can be used to identify overlap and differences in sets, then for exact matches see [this answer](http://stackoverflow.com/q/3661766/1048425) – GarethD Jul 08 '16 at 11:56
  • @GarethD: That's what I was thinking. Your comment should be an answer. I didn't know about the EXCEPT operator that's good stuff –  Jul 08 '16 at 12:05
  • I am not convinced that there is enough substance to the comment to make it an answer. I have no references, or tests to demonstrate that one way is better than another, and my answering based on my opinion alone may deter a better, substantiated answer from elsewhere. – GarethD Jul 08 '16 at 12:10

1 Answers1

0

A couple options:

  1. Trigger to see of value exists and if it does, return NULL (accomplishes exactly what you are doing)
  2. Just use unique constraints and handle errors

Both these move the table from bags to sets but they do so in different ways, one by giving it more select-like semantics (for a slight performance cost) and the other, more traditional SQL way, to force data validation on entry.

If you want to select as a set, use select distinct instead.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182