On Tue, Aug 31, 2010 at 7:56 PM, Raymond C. Rodgers <sinful622@xxxxxxxxx> wrote: > 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. You have it backwards, mysql is broken, postgresql is not. Anyways, you can do it in postgres like this: alter table mydemo alter cat_order type int using cat_order + 1; merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
