How to update unique table row numbers before inserting new row at existing position

Question

SQL table:

 id |  name
----+--------
  1 | apple
  2 | orange
  3 | apricot

The id is primary key, unique, could be SERIES. The goal is to insert new row where id equals 2 and shift existing row numbers below, that is 2 and 3, to 3 and 4 position.

I have tried shift rows before inserting new row:

"UPDATE some_table SET id = id + 1 WHERE id >= id"

but an error occurred:

org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "some_table_pkey"
  Detail: Key (id)=(3) already exists.

Is there some effective way to do such an operation?

The table should look like this after update:

 id |  name
----+--------
  1 | apple
  2 | cherry
  3 | orange
  4 | apricot

Why on earth would you want to do that? The primary key value has no meaning whatsoever. Its only job is to be unique. It doesn't matter if a PK value is 1, 42 or 47336497. You should create a separate column to define a customized sort order — user330315
– user330315, Commented Jul 11, 2019 at 10:31
I'm creating the rule set for execution by order 1,2,... and I must have the ability to modify, delete or insert the row by specified number (1,2, etc.). Yes, I can do that by sorting, just trying to find the best solution. — Ernestas Gruodis
– Ernestas Gruodis, Commented Jul 11, 2019 at 11:04

user330315 · Accepted Answer · 2019-07-11 10:36:45Z

2

While I think the attempt is futile, you can achieve that by marking the primary key constraint as deferrable:

CREATE TABLE some_table
(
  id int, 
  name text
);

alter table some_table 
  add constraint pk_some_table 
  primary key (id) 
  deferrable initially immediate; --<< HERE

In that case the PK constraint is evaluated at per statement, not per row.

Online example: https://rextester.com/JSIV60771

answered Jul 11, 2019 at 10:36

user330315

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

1 Comment

Ernestas Gruodis Over a year ago

Glad to know these PostgreSQL options, thanks indeed.

megha shah · Accepted Answer · 2019-07-11 10:31:35Z

0

update Names set id=id+1 where id in (select id from Names where id>=2 order by id desc);

Here first you can update the id's and then you can insert

insert into Names (id,name) values(2,'cheery')

answered Jul 11, 2019 at 10:31

megha shah

216 bronze badges

1 Comment

user330315 Over a year ago

That results in the same error. The order by in the sub-query is completely meaningless for the IN operator.

Collectives™ on Stack Overflow

How to update unique table row numbers before inserting new row at existing position

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related