Question 264481 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Insert a record in postgres database table without replacing the existing records

I have a table named employees which has 3 columns ( id , firstname, lastname) which already has 20 records . I want to insert a new record with the id=15 and shift the existing rows with id > 14 without replacing the existing record with id=15.

i tried to move the rows like following but that did not work

UPDATE employees
SET id = id + 1
WHERE id >= 15;


INSERT INTO employees (id , firstname, lastname)
VALUES (15, 'john', 'doe' );

UPDATE, NOTE : the id is used as a foreign key in another table called product, so if we modify the id’s in the employees table we have to modify it in product table too I think

i got the following error The key “(id)=(17)” already exists

Thank you

Tags:

4

Answers


  1. 0

    It’s a bit of a hack but the trick I’ve used is to update twice. e.g.

    UPDATE employees
SET id = id + 101
WHERE id >= 15;

INSERT INTO employees (id , firstname, lastname)
VALUES (15, 'john', 'doe' );

UPDATE employees
SET id = id - 10
WHERE id >= 16;

    Another alternative is to remove the constraint and then add it back again but that’s even uglier.

    Whatever the approach doing it in a single transaction is probably a good idea.

    Login or Signup to reply.
  2. 0

    The attribute id must be primary key, thus the error. Also, One Must Not fiddle with the Primary Key Values because if they are used in FK_Constraints or as Foreign Keys, such manipulation of Primary Key value can be disastrous.

    Notwithstanding, you one can achieve what you are trying using something known as Common Table Expression. You may look at following sql

    BEGIN;
WITH updt_employees AS (
    SELECT (id + 1) AS id, firstname, lastname
    FROM employees
    WHERE id >= 15
)
INSERT INTO employees (id, firstname, lastname)
SELECT 15, 'John', 'Doe'
UNION ALL
SELECT * FROM updt_employees;

COMMIT;
    Login or Signup to reply.
  3. 0

    The last one is maybe good, but

    1. Insert 1 new ROW
    2. Update Primary Table backwards
    3. Update Row with id 15
    4. fix foreign Tables

    like that:

    INSERT INTO employees VALUES (SELECT MAX(id) FROM emloyees, '','');

WITH ue AS (
SELECT id, firstname, lastname
FROM employees
WHERE id >= 15 AND id < (SELECT MAX(id) FROM emloyees)
ORDER BY id DESC
)
UPDATE employees e
SET firstname = ue.firstname, lastname = ue.lastname
WHERE e.id = ue.id + 1;

UPDATE employees e
SET firstname = 'John', lastname = 'Doe'
WHERE e.id = 15;

UPDATE foreign_table_x SET foreign_key = foreign_key + 1
WHERE foreign_key >= 15;
    Login or Signup to reply.
  4. 0

    Updating tables that reference this ID can be handled automatically if you set up the foreign key columns with an on update cascade clause: demo

    create table tasks(
  id int generated by default as identity primary key,
  employee_id int references employees(id) 
      on delete set null 
      on update cascade,                               --this
  description text);

    To get rid of the id collision error, you can use an ordered batch update:

    WITH update_batch AS (
  SELECT id,
         id+1 AS new_id 
  FROM employees WHERE id >= 15 
  ORDER BY id DESC) --this makes sure the last one moves first, making room
UPDATE employees AS old
SET id = new_id
FROM update_batch AS new 
WHERE old.id=new.id;

    You got the error because your primary key constraint is by default not deferrable, so the uniqueness of the id is checked row-by-row during the update operation. Trying to update the first row with id=15 it already sees id=16 is occupied by the next row. If you force it to process things in reverse, each row first makes room for the next one to take over its id.

    It’s also possible to make your primary key constraint deferrable which makes PostgreSQL check for constraint violations at the end of transaction instead. Problem is, a column with a deferrable unique constraint cannot be used for foreign keys: demo2.

    @Frank Heikens raised a good point. You’re putting id column on double duty: one of uniquely identifying rows, another of defining order of some sort. By dispatching these functions to different columns you wouldn’t have to cascade identity changes all over the place whenever you just want to change order: demo

    ALTER TABLE employees ADD COLUMN my_order int UNIQUE DEFERRABLE;
UPDATE employees SET my_order=id;

UPDATE employees
SET my_order = my_order + 1
WHERE my_order >= 15;

INSERT INTO employees (id, my_order, firstname, lastname)
VALUES (21, 15,'john', 'doe' );

    You can reorder things all you want an all previous foreign key associations remain untouched.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search