Is there a way to dynamically update/increment column values when a new value is added?

user_6469368 0
Viewed 399

Apologies if the title doesn't communicate the question properly.

If I have a Postgres table called Person with the following columns and data:

name | age | order |
John | 42  | 1     |
Sam  | 27  | 2     |
Phil | 19  | 3     |

The order column is a Float that determines the order by which the client should display a list of persons.

Is there an optimal performant way to insert a Person with an existing order but delegating the DB to autoincrement the values in the order column? If so, how?

For example, if I insert a new person (Emma, 56, 2) I want the result to be

name | age | order |
John | 42  | 1     |
Sam  | 27  | 3     |
Phil | 19  | 4     |
Emma | 56  | 2     |

Note Sam and Phil now have their orders incremented.

Possible solution: I can take advantage of float and get the average of the previous order value and current order. So in the above example, it would be 1 (for John) + 2 (for Emma in the insert) / 2. The new order for Emma will be 1.5 which fits nicely as shown below:

name | age | order |
John | 42  | 1     |
Sam  | 27  | 2     |
Phil | 19  | 3     |
Emma | 56  | 1.5   |

But this requires multiple DB calls hence the question.

Edit: While I went with George Joseph's answer in the final implementation, the correct answer that actually answered the question was clamp's. Hence I marked his answer as accepted.

sql-insert postgresql sql
3 Answers

If you want to update the existing entries here is how:

First check if the new value conflicts with an existing one. If so, increment existing values by 1 (this would still worl with integers).

I use sort_order instead of order because the latter is reserved in SQL.

CREATE FUNCTION person_check_sort_order() RETURNS TRIGGER AS 
$def$
BEGIN
PERFORM * FROM person WHERE sort_order = new.sort_order;
IF FOUND THEN
UPDATE person SET sort_order = sort_order + 1 WHERE sort_order >= new.sort_order;
END IF;
RETURN new;
END;
$def$
LANGUAGE plpgsql;

CREATE TRIGGER person_insert_trg BEFORE INSERT ON person
FOR EACH ROW EXECUTE PROCEDURE person_check_sort_order();

Or you can do it recursively. This will work with updates too and stops when it reaches a gap in the order numbers (assuming integer values):

CREATE OR REPLACE FUNCTION person_check_sort_order() RETURNS TRIGGER AS 
$def$
BEGIN
PERFORM * FROM person WHERE sort_order = new.sort_order;
IF FOUND THEN
UPDATE person SET sort_order = sort_order + 1 WHERE sort_order = new.sort_order;
END IF;
RETURN new;
END;
$def$
LANGUAGE plpgsql;
--DROP TRIGGER person_insert_trg ON person;
CREATE TRIGGER person_insert_trg BEFORE INSERT OR UPDATE OF sort_order ON person
FOR EACH ROW EXECUTE PROCEDURE person_check_sort_order();
user_106678390

Here is how you can work on the possible solution you have used in the example

You would pass the parameters, param_name,param_age and param_order

 insert 
   into t 
with data
  as (select (max(order) +max(:param_order))/2 as possible_solution 
        from t
        where order<:param_order 
      )
select :param_name,:param_age,d.possible_solution 
  from data d
user_71370900
user_119093950
Related