0

I have an array column which is part of unique validation in rails.

So a table with column a,b,c,d,e .

Validation: validates_uniqueness_of :d, scope: [:a, :b]

Now since I have multiple rails servers , sometimes there would be duplicate rows in the table because there is no DB level constraint. More on it: http://robots.thoughtbot.com/the-perils-of-uniqueness-validations/

So, I am trying to add a unique index on the table like this: add_index :table, ["a", "b","d"], unique: true ,using: :gin

I am using gin . See http://www.postgresql.org/docs/current/static/gin-intro.html Can PostgreSQL index array columns?

However,

seems like I am still able to add duplicate rows after doing db:migrate . I am assuming this is because the unique index on array did not work .

Any pointers ?

Thanks!

  • I am using Rails4
  • Normalizing the table is not an option currently
Community
  • 1
  • 1
codeObserver
  • 6,521
  • 16
  • 76
  • 121

1 Answers1

1

Best I'm aware, Postgres will only enforce a unique constraint using a btree index. So, your index creation code is either silently failing or silently ignoring the unique part of its definition.

Another consideration to have in mind is that array[1,2,3] <> array[3,2,1] insofar as Postgres is concerned. To ensure proper uniqueness validation, you might also want to enforce a constraint on the array so as to reject unsorted values.

(Better yet, you should probably normalize your data properly, to avoid using such an array to begin with, but I digress... that is another topic altogether.)

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154