Updating the values of Primary key columns in Postgresql

Question

I have the following table

|  ID | Seq No|
+++++++++++++++
| 1   | 12345 |
| 2   | 12345 |
| 3   | 12345 |
| 4   | 12345 |
| 5   | 12345 |

Both the columns are primary key values.

Here I need to increment the value by 1 for ID values greater than 2.

Following is the query I tried to execute

Update tblname
set id = id + 1
where id > 2 and seq_no = 12345

I got the following error when executing the command

ERROR: duplicate key value violates unique constraint "tblname"
DETAIL: Key (id, seq_no)=(3, 12345) already exists.

How do I solve this issue.

PS: As a workaround, I tried adding 10 to the ID value and again subtracted 9 from the ID value.

forpas · Accepted Answer · 2019-08-16 09:07:37Z

3

This workaround involves 2 update statements.
The first updates the ids to the negative values and the second makes them positive again:

Update tblname
set id = -(id + 1)
where id > 2 and seq_no = 12345;

Update tblname
set id = -id
where id < -2 and seq_no = 12345;

See the demo.
Results:

| id  | seq_no |
| --- | ------ |
| 1   | 12345  |
| 2   | 12345  |
| 4   | 12345  |
| 5   | 12345  |
| 6   | 12345  |

answered Aug 16, 2019 at 9:07

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stavros Koureas Over a year ago

Thanks, that was easy and clever!

Nagaraja Thangavelu · Accepted Answer · 2019-08-19 01:43:35Z

1

I found the query using WITH clause and without using any workarounds.

with target_records as (
  select id, seq_no from tblname
  where seq_no = 12345 and id > 2
  order by id desc
)
--select * from target_records;
Update tblname
Set id = target_records.id + 1
from target_records
where tblname.id = target_records.id and tblname.seq_no = 12345;

answered Aug 19, 2019 at 1:43

Nagaraja Thangavelu

1,1781 gold badge15 silver badges33 bronze badges

Collectives™ on Stack Overflow

Updating the values of Primary key columns in Postgresql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related