Thread: 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
On Tue, Aug 31, 2010 at 07:56:23PM -0400, Raymond C. Rodgers 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.
PostgreSQL's version involves UPDATE ... FROM. Use an ORDER BY in the
FROM clause like this:
UPDATE mydemo SET cat_order = m.cat_order+1
FROM (
SELECT cat_order, client_id
FROM mydemo
WHERE
client_id = 1 AND
cat_order >= 0
ORDER BY cat_order) m
WHERE
mydemo.cat_order = m.cat_order AND
mydemo.client_id = m.client_id
More details on PostgreSQL's UPDATE are at:
http://www.postgresql.org/docs/current/static/sql-update.html
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
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
On Tue, 2010-08-31 at 20:17 -0400, Merlin Moncure wrote: > > 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. That is what he said. JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
On 8/31/2010 8:17 PM, Merlin Moncure wrote: > 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 Like I said and stressed twice, it's not a problem with PostgreSQL. David's solution is actually better than that, but I accidentally sent just a private reply to him acknowledging that it's good to know that PostgreSQL can also save a stupid programmer's butt... :-) Raymond
On Tue, 2010-08-31 at 20:17 -0400, Merlin Moncure wrote: > > 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. That is what he said. JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
> -----Original Message----- > From: Raymond C. Rodgers [mailto:sinful622@gmail.com] > Sent: Tuesday, August 31, 2010 7:56 PM > To: pgsql-general@postgresql.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
On 09/01/10 16:13, Igor Neyman wrote: > > >> -----Original Message----- >> From: Raymond C. Rodgers [mailto:sinful622@gmail.com] >> Sent: Tuesday, August 31, 2010 7:56 PM >> To: pgsql-general@postgresql.org >> Subject: Table update problem works on MySQL but not Postgres >> 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. > What you need for your update to work is "deferred" unique constraints. > I think, this feature appears in 9.0. Yes: http://www.postgresql.org/docs/9.0/static/sql-set-constraints.html " Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE constraints are affected by this setting. NOT NULL and CHECK constraints are always checked immediately when a row is inserted or modified (not at the end of the statement). Uniqueness and exclusion constraints that have not been declared DEFERRABLE are also checked immediately. " In 8.4 it says: " Currently, only foreign key constraints are affected by this setting. Check and unique constraints are always effectively not deferrable. Triggers that are declared as "constraint triggers" are also affected. "