Thread: WHERE condition lost from conditional unique index
I am attempting to apply an Unique Index in Postgresql with mixed results. I am generating the index via a Ruby on Rails migration. The up
statement looks like:
execute("create unique index formatted_address_unit_number_and_status on properties (formatted_address, unit_number) where status = 'published';")
On my local development the index works as intended. If I take a look at the properties
table by running \d properties
, the migration correctly added index like so:
"formatted_address_unit_number_and_status" UNIQUE,btree (formatted_address, unit_number) WHERE status::text = 'published'::text
However, when my Jenkins CI server runs the migration, I only see the following in the properties
table:
"formatted_address_unit_number_and_status" UNIQUE, btree (formatted_address, unit_number)
Why would the migration work on my development but not on Jenkins? Am I applying the unique index correctly to begin with?
jake fagan wrote > Why would the migration work on my development but not on Jenkins? The only obvious problem I can come up with is that your development environment and the source for Jenkins do not have the same codebase. Did you ever attempt to create this particular index without the where clause - and now just recently converted it into a partial? I doubt that Jenkins is silently ignoring the WHERE clause...an incorrect source file is more likely. > Am I applying the unique index correctly to begin with? Yes, though it is a "partial unique index" since you are using a where clause. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/WHERE-condition-lost-from-conditional-unique-index-tp5806355p5806365.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.