Ordering with PostgreSQL trigger when updating and inserting

Question

I'm looking to sort the rows based on a variable. I'm pulling my hair out trying to wrap my head around this kind of trigger. I've only ever written triggers that gets some values from other tables.

Example: I have rows with the columns lets say: name and order.

CREATE TABLE buildorder (
    name VARCHAR NOT NULL,  
    order_buildorder INT,
);

INSERT INTO buildorder (name, order_buildorder) VALUES ('Gateway', 1);
INSERT INTO buildorder (name, order_buildorder) VALUES ('Pylon', 2);

Now I insert without order but the ON INSERT trigger should make it 3 automatically

INSERT INTO buildorder (name) VALUES ('Probe');

But now we update the third buildorder to be in the 1st position

UPDATE buildorder
SET order_buildorder = 1
WHERE name = 'Probe'

All the other rows should now change to reflect this change in ordering. How would I go about writing a trigger for this?

Example result:

[
{name = "Gateway", order_buildorder = 2},
{name = "Pylon", order_buildorder = 3},
{name = "Probe", order_buildorder = 1},
]

Laurenz Albe · Accepted Answer · 2020-09-24 02:17:50Z

4

This is a terrible idea.

Instead, define order_buildorder as a real column.

That way, you can always insert a new row to be ordered between two already existing rows without updating existing rows (just take the arithmetic mean of the values between which you want to insert).

If you need an integral order, generate it on the fly using a window function when you select the data.

edited Sep 24, 2020 at 2:17

answered Oct 1, 2019 at 21:46

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

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

7 Comments

Legion Over a year ago

Thanks this is an intriguing solution. I will probably do this. I'm also exploring the possibility of using which item comes before the current one. Any reason you chose for this solution?

Laurenz Albe Over a year ago

Yes, because you can always find a double precision number between two given ones, so you can insert values everywhere without having to rejiggle everything. PostgreSQL enums are implemented that way.

Legion Over a year ago

Makes sense I decided to do your solution. Thanks

ManUtopiK Over a year ago

Why double precision (8 bytes) and not real (4 bytes)? 6 decimals digits precision is not enough?

Laurenz Albe Over a year ago

@ManUtopiK You are right, real is better. I modified my answer.

|

Collectives™ on Stack Overflow

Ordering with PostgreSQL trigger when updating and inserting

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related