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

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