[GENERAL] is a unique key on null field bad?
So, we are trying to track down some problems we're having with an implementation of slony on our database. I've posted to the slony list about this issue, but I wanted to get a more generic response from the perspective of postgresql. Is it a 'bad thing' to have a unique key on a field that is often times null? This application has been running along just fine for a couple of years now, but when we try to implement a slony replication solution, this one table consistently has inconsistent data between the primary node and the slave. The problem we are having with slony seems to be related to a table that has just such a key, so we are trying to figure out if this is causing the problem. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] is a unique key on null field bad?
On 20/02/2008, Geoffrey [EMAIL PROTECTED] wrote: So, we are trying to track down some problems we're having with an implementation of slony on our database. I've posted to the slony list about this issue, but I wanted to get a more generic response from the perspective of postgresql. Is it a 'bad thing' to have a unique key on a field that is often times null? This application has been running along just fine for a couple of years now, but when we try to implement a slony replication solution, this one table consistently has inconsistent data between the primary node and the slave. The problem we are having with slony seems to be related to a table that has just such a key, so we are trying to figure out if this is causing the problem. Its not a problem as such, but it will not exactly be unique as there could be multiple records with null values in that table. So it can't be the primary key, (Hence why Slony has a problem) However it you want to ensure that the field is either Unique or Null (ie not known) then this is a good way of doing it for example with Car Number Plates where the details are not known yet but must be unique once they are known... Regards Peter.
Re: [GENERAL] is a unique key on null field bad?
Peter Childs wrote: On 20/02/2008, Geoffrey [EMAIL PROTECTED] wrote: So, we are trying to track down some problems we're having with an implementation of slony on our database. I've posted to the slony list about this issue, but I wanted to get a more generic response from the perspective of postgresql. Is it a 'bad thing' to have a unique key on a field that is often times null? This application has been running along just fine for a couple of years now, but when we try to implement a slony replication solution, this one table consistently has inconsistent data between the primary node and the slave. The problem we are having with slony seems to be related to a table that has just such a key, so we are trying to figure out if this is causing the problem. Its not a problem as such, but it will not exactly be unique as there could be multiple records with null values in that table. So it can't be the primary key, (Hence why Slony has a problem) We aren't using this as the primary key, so would this still pose a problem for slony? (indexes on this table) Indexes: tract_pkey primary key, btree (recid) tract_order_num_key unique, btree (order_num) tract_assigned btree (assigned) tract_code btree (code) tract_comments btree (comments) tract_compound_1 btree (code, old_order_num) tract_date_avail btree (date_avail) tract_dest_state btree (dest_state) tract_dest_zone btree (dest_zone) tract_driver btree (driver) tract_orig_state btree (orig_state) tract_orig_zone btree (orig_zone) tract_prebooked btree (prebooked) tract_tractor_num btree (tractor_num) tract_trailer_num btree (trailer_num) However it you want to ensure that the field is either Unique or Null (ie not known) then this is a good way of doing it for example with Car Number Plates where the details are not known yet but must be unique once they are known... -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
