By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,541 Members | 1,427 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,541 IT Pros & Developers. It's quick & easy.

defferable update & unique

P: n/a
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;

cheers
--------------------------
Canaan Surfing Ltd.
Internet Service Providers
Ben-Nes Michael - Manager
Tel: 972-4-6991122
Fax: 972-4-6990098
http://www.canaan.net.il
--------------------------
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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)

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #2

P: n/a
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
pg***@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #3

P: n/a
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;
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #4

P: n/a

"Ben-Nes Michael" <mi**@canaan.co.il> writes:
Does 7.4 support defferable updates on unique column ?


no

--
greg
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #5

P: n/a
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
pg***@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #6

P: n/a
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
pg***@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #7

P: n/a
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" <pg***@candle.pha.pa.us>
To: "Ben-Nes Michael" <mi**@canaan.co.il>
Cc: "postgresql" <pg***********@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
pg***@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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.