Search Postgresql Archives

Table update problem works on MySQL but not Postgres

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux