On Tue, Aug 31, 2010 at 7:56 PM, Raymond C. Rodgers <sinful622@gmail.com> 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