> -----Original Message----- > From: Raymond C. Rodgers [mailto:sinful622@xxxxxxxxx] > Sent: Tuesday, August 31, 2010 7:56 PM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: Table update problem works on MySQL but not Postgres > > Let me stress that this is not a bug in PostgreSQL; if > anything at all, it's only a lack of a stupid feature. > > I'm working on a project for a client where I have a table > for arbitrary categories to be applied to their data, and > they need to be able to set the order in which the categories > appear. A simplified version of the table as I created is as follows: > > create table mydemo (cat_id int not null, cat_name > varchar(25) not null, cat_order int not null, primary > key(cat_id,cat_order)); > > During my coding, I unwittingly backed myself into a corner, > fully expecting to issue queries such as: > > update mydemo set cat_order = cat_order + 1 where client_id = > 1 and cat_order >= 0 > > in order to insert categories at the top of the sorted list > for example. As you can probably guess, this query doesn't > work very well. > On both MySQL and PostgreSQL I get a constraint violation. > That makes sense; I screwed up. > > But out of pure curiosity to see if I could circumvent this > issue, I added an order clause, making that query this instead: > > update mydemo set cat_order = cat_order + 1 where client_id = > 1 and cat_order >= 0 order by cat_order desc > > This is where the interesting thing happens: On MySQL the > query actually works as intended, but it doesn't on > PostgreSQL. As I said, I'm sure this is not a bug in > PostgreSQL, but the lack of a stupid user trick. > While my project is on MySQL, and I could theoretically leave > my code as is to take advantage of this trick, I'm sure I'd > be a complete idiot to leave it instead of fixing it. > > However, I wanted to share this little tidbit with the > PostgreSQL community. > > Raymond > What you need for your update to work is "deferred" unique constraints. I think, this feature appears in 9.0. Regards, Igor Neyman -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
