Thread: Table update problem works on MySQL but not Postgres

Table update problem works on MySQL but not Postgres

From
"Raymond C. Rodgers"
Date:
  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

Re: Table update problem works on MySQL but not Postgres

From
David Fetter
Date:
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

Re: Table update problem works on MySQL but not Postgres

From
Merlin Moncure
Date:
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

Re: Table update problem works on MySQL but not Postgres

From
"Joshua D. Drake"
Date:
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

Re: Table update problem works on MySQL but not Postgres

From
"Raymond C. Rodgers"
Date:
  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

Re: Table update problem works on MySQL but not Postgres

From
"Joshua D. Drake"
Date:
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


Re: Table update problem works on MySQL but not Postgres

From
"Igor Neyman"
Date:

> -----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

Re: Table update problem works on MySQL but not Postgres

From
Ivan Voras
Date:
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. "