Uniq index on array column in postgres (Rails)
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!
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.)
链接地址: http://www.djcxy.com/p/87224.html上一篇: postgres中大型数据库的索引