Re: [GENERAL] Table update problem works on MySQL but not Postgres
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 http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table update problem works on MySQL but not Postgres
On Tue, Aug 31, 2010 at 7:56 PM, 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. > > 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table update problem works on MySQL but not Postgres
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table update problem works on MySQL but not Postgres
On 8/31/2010 8:17 PM, Merlin Moncure wrote: On Tue, Aug 31, 2010 at 7:56 PM, 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. 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table update problem works on MySQL but not Postgres
> -Original Message- > From: Raymond C. Rodgers [mailto:sinful...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table update problem works on MySQL but not Postgres
On 09/01/10 16:13, Igor Neyman wrote: -Original Message- From: Raymond C. Rodgers [mailto:sinful...@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. " -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
