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
