Results 1 to 4 of 4
  1. #1
    Join Date
    May 2010

    Unanswered: Transactions Query

    Hi everyone,

    I'm implementing a car rental system and I'm trying to build a query that checks the slot time before inserting a new booking.

    Instead of making different querys, I want to do transactions but I don't know how. I would do something like this:

    1. SELECT car.plate_nr,model,passenger,manual,type,id_parkin g
      FROM car LEFT JOIN booking on car.plate_nr = booking.plate_nr
      WHERE (start_date, end_date) OVERLAPS (timestamp '2010-05-25 8:00',timestamp '2010-05-25 15:00')
    2. if the previous query returns 0 then do
      INSERT INTO booking (plate_nr, start_date, end_date, destination, username) RETURNING id_booking;
    3. Otherwise return -1

    can anyone help me? thanks

  2. #2
    Join Date
    Aug 2009
    Olympia, WA
    You don't really need anything complex. You can do this with a single sql statement.

    INSERT INTO booking(...)
    SELECT ...
    FROM car c
    WHERE  (
      SELECT id_booking
      FROM booking b
      WHERE c.plate_nr = b.plate_nr
        AND (b.start_date, b.end_date) OVERLAPS (timestamp '2010-05-25 8:00',timestamp '2010-05-25 15:00')
    ) IS NULL
    RETURNING id_booking;
    Oh, and you probably want to use timestamptz unless you live somewhere that doesn't have daylight savings time, all of your locations are w/in a single timezone and your company will never expand to span multiple timezones.

  3. #3
    Join Date
    May 2010

    thank you for your response, but it does not work. I replaced the ... with my values but I get this error:

    ERROR: syntax error at or near "SELECT"
    LINE 2: SELECT * FROM car c

    Last edited by ilfolle; 05-26-10 at 08:09.

  4. #4
    Join Date
    May 2008
    Quote Originally Posted by ilfolle View Post
    SELECT * FROM car c
    You either need to specify the columns explicitly:
    INSERT INTO booking (a, b, c)
    SELECT a, b, c
    FROM car
    Or if the column layout of the two tables is the same:
    INSERT INTO booking
    SELECT *
    FROM car

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts