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
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 da...@fetter.org 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 sinful...@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 -- 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. Rodgerssinful...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
