current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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
share|improve this question

1 Answer 1

up vote 1 down vote

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.)

share|improve this answer

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.