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

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

Answer

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