Re: Table update problem works on MySQL but not Postgres

> -----Original Message-----
> From: Raymond C. Rodgers [mailto:sinful622(at)gmail(dot)com]
> Sent: Tuesday, August 31, 2010 7:56 PM
> To: pgsql-general(at)postgresql(dot)org
> 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