CODEWITHSUNDEEP
postgresqlsql-insert
Gn gnk
Gn gnk

Reputation: 25

How to insert into after the last row in a table?

i have this table below named roombooking: enter image description here

I wrote this code that inserts a new row into roombooking(dont mind the details, just the hotelbookingID):

CREATE OR REPLACE FUNCTION my_function(startdate date , enddate date,idForHotel integer)
RETURNS void AS
$$
BEGIN


INSERT INTO roombooking("hotelbookingID","roomID","bookedforpersonID"
                                     ,checkin,checkout,rate)
                                SELECT rb."hotelbookingID", r."idRoom", p."idPerson"
                                ,startdate-integer'20', startdate-integer'10', rr.rate 
                                FROM(SELECT "hotelbookingID" FROM roombooking
                                    WHERE "hotelbookingID"=
                                    (select "hotelbookingID"
                                     from roombooking
                                    order by "hotelbookingID" desc 
                                    limit 1)+1) rb,
                                (SELECT "idRoom" FROM room 
                                WHERE "idHotel"=idForHotel) r ,
                                (SELECT "idPerson" FROM person
                                ORDER BY random()
                                LIMIT 1) p,
                                (SELECT rate FROM roomrate
                                WHERE "idHotel"=idForHotel) rr;
                         


 END;
 $$
 LANGUAGE 'plpgsql';

The problem here is that i want to insert after the last row based on the last hotelbookingID(it is in asc order)

My function works but as i guess it cant find the last row ,in order to perform the insertion after . (I think that the problem can be spotted here :

SELECT "hotelbookingID" FROM roombooking
                                    WHERE "hotelbookingID"=
                                    (select "hotelbookingID"
                                     from roombooking
                                    order by "hotelbookingID" desc 
                                    limit 1)+1)

Any help would be valuable. Thank you.

Upvotes: 1

Views: 1013

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247260

Any approach that uses a subquery to find the maximum existing id is doomed to suffer from race conditions: if two such INSERTs are running concurrently, they will end up with the same number.

Use an identity column:

ALTER TABLE roombooking
   ALTER id ADD GENERATED ALWAYS AS IDENTITY (START 100000);

where 100000 is a value greater than the maximum id in the table.

Then all you have to do is not insert anything into id, and the column will be populated automatically.

Upvotes: 1

Bergi
Bergi

Reputation: 664980

That WHERE condition makes no sense. There is no row in the roombooking table whose id is 1 + the largest id in the roombooking table.

You simply want to add 1 to the inserted value:

INSERT INTO roombooking("hotelbookingID", …)
SELECT rb."hotelbookingID" + 1, …
--                        ^^^^
FROM (
  SELECT "hotelbookingID"
  FROM roombooking
  ORDER BY "hotelbookingID" DESC
  LIMIT 1 
) rb,
…

That said, I would recommend to simply use a sequence instead (if you don't care about occasional gaps). If you really need a continuous numbering, I wouldn't use order by+limit though. Just use an aggregate, and consider the case where the table is still empty:

INSERT INTO roombooking("hotelbookingID", …)
VALUES ( COALESCE((SELECT max("hotelbookingID") FROM roombooking), 0) + 1, …);

Upvotes: 0

Related Questions