defferable update & unique

Ben-Nes Michael wrote:

Hi All

Does 7.4 support defferable updates on unique column ?

example

unique INT colum named u_test has the following values: 1, 2, 3, 4, 5

can i do update table set u_test = u_test + 1;

No, this is on our TODO list. Also, I can't think of a workaround. :-(

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Wed, 19 Nov 2003, Ben-Nes Michael wrote:

Does 7.4 support defferable updates on unique column ?

Not yet, and I'm not sure if any work is actively being done on it
(although some discussions on ways to handle it have happened fairly
recently)

"Ben-Nes Michael" <miki@canaan.co.il> writes:

Does 7.4 support defferable updates on unique column ?

no

--
greg

On Wed, 19 Nov 2003, Bruce Momjian wrote:

Ben-Nes Michael wrote:

Hi All

Does 7.4 support defferable updates on unique column ?

example

unique INT colum named u_test has the following values: 1, 2, 3, 4, 5

can i do update table set u_test = u_test + 1;

No, this is on our TODO list. Also, I can't think of a workaround. :-(

If negative values aren't allowed normally, I think you can do it in an
ugly fashion through two updates, something like:

update table set u_test=-u_test-1;
update table set u_test=-u_test;

Stephan Szabo wrote:

On Wed, 19 Nov 2003, Bruce Momjian wrote:

Ben-Nes Michael wrote:

Hi All

Does 7.4 support defferable updates on unique column ?

example

unique INT colum named u_test has the following values: 1, 2, 3, 4, 5

can i do update table set u_test = u_test + 1;

No, this is on our TODO list. Also, I can't think of a workaround. :-(

If negative values aren't allowed normally, I think you can do it in an
ugly fashion through two updates, something like:

update table set u_test=-u_test-1;
update table set u_test=-u_test;

Excellent idea. You might want to add a VACUUM in there:

update table set u_test=-u_test-1;
VACUUM
update table set u_test=-u_test;
VACUUM

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

i found a workaround, but relativly slow.

The column is not unique and when the update is finished i fire up a trigger
that check for uniqueness.

lets wait for pg7.5 :)

--------------------------
Canaan Surfing Ltd.
Internet Service Providers
Ben-Nes Michael - Manager
Tel: 972-4-6991122
Fax: 972-4-6990098
http://www.canaan.net.il
--------------------------
----- Original Message -----
From: "Bruce Momjian" <pgman@candle.pha.pa.us>
To: "Ben-Nes Michael" <miki@canaan.co.il>
Cc: "postgresql" <pgsql-general@postgresql.org>
Sent: Wednesday, November 19, 2003 6:39 PM
Subject: Re: [GENERAL] defferable update & unique

Ben-Nes Michael wrote:

Hi All

Does 7.4 support defferable updates on unique column ?

example

unique INT colum named u_test has the following values: 1, 2, 3, 4, 5

can i do update table set u_test = u_test + 1;

No, this is on our TODO list. Also, I can't think of a workaround. :-(

-- 
Bruce Momjian                        |  http://candle.pha.pa.us
pgman@candle.pha.pa.us               |  (610) 359-1001
+  If your life is a hard drive,     |  13 Roberts Road
+  Christ can be your backup.        |  Newtown Square, Pennsylvania

19073