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.
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();
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
I think sequence in PostgreSQL could solve this case
CREATE SEQUENCE serial START 101;
you could find more details about this here