Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE my_table (waypoints int, path_id int, "order" int);
CREATE OR REPLACE FUNCTION consecutive_check()
    RETURNS trigger
    LANGUAGE plpgsql
AS $function$
BEGIN
    IF NEW."order" = 1 OR EXISTS (SELECT 1 FROM my_table WHERE path_id = NEW.path_id AND "order" = (NEW."order" - 1)) THEN
        RETURN NEW;
    END IF;

    RAISE EXCEPTION 'Previous waypoint not available for path_id = %', NEW.path_id;
END;
$function$;

CREATE TRIGGER no_holes_path
    BEFORE INSERT
    ON my_table
    FOR EACH ROW
    EXECUTE PROCEDURE consecutive_check()
;
INSERT INTO my_table (waypoints, path_id, "order")
VALUES
    (89, 1, 1),
    (16, 1, 2),
    (17, 1, 3),
    (19, 1, 4),
    (4, 1, 5),
    (75, 2, 1),
    (28, 2, 2),
    (2, 2, 3)
;
8 rows affected

INSERT INTO my_table (waypoints, path_id, "order") VALUES (23, 1, 6);
1 rows affected

INSERT INTO my_table (waypoints, path_id, "order") VALUES (7, 1, 8);
ERROR:  Previous waypoint not available for path_id = 1
CONTEXT:  PL/pgSQL function consecutive_check() line 7 at RAISE


INSERT INTO my_table (waypoints, path_id, "order") VALUES (42, 3, 2);
ERROR:  Previous waypoint not available for path_id = 3
CONTEXT:  PL/pgSQL function consecutive_check() line 7 at RAISE


INSERT INTO my_table (waypoints, path_id, "order") VALUES (13, 3, 1);
1 rows affected