How to insert one record after every log record in PostgreSQL

Question

I have a PostgreSQL table that store logs data with each row is one log and fields are logname, start, end. I want to visualize the logs, and due to a limit of Grafana the visualize tool I choose, I feel the need to insert one new record with stimestamp one second after each log, let's say with logname 'Idle'. For better explanation:

Start	Logname	End
10:00:00	log-in	10:00:01
10:30:00	call	11:00:00
10:55:00	some action (still in call)	11:05:00

Then I want to manually add logname 'Idle' one second after each available logs

Start	Logname	End
10:00:00	log-in	10:00:01
10:30:02 (1 second after the end time of the previous log)	idle	10:30:03
10:30:00	call	11:00:00
10:55:00	some action	11:05:00
11:05:01 (1 second after)	idle	11:05:01

I think of manually calculating each row and then inserting them, but how should I do it in terms of a PostgreSQL query?

Update Thanks to Akina & dwhitemv, I see it's illogical to insert a new record after every available record. Instead, the question should be inserting a new record into time ranges only if there are no available records or overlapping records. Aside from inserting, a new problem is how to check if there is a record(s) in the time range. I updated the tables above. I feel this makes it complicated since using NOT EXISTS & ON CONFLICT can't really check if records exist between 11:00 and 11:05?

• user234725: So you want to find all the time ranges where no record exists?
• Lacie: Oh, you can put it that way. At first, I intended to insert a new Idle record after every record, which will double the size of the table, regardless of whether there is already a log record in the next 1 second or even a time overlap of 2 log records. Now look at Akina's and your responses, it doesn't make sense to insert an Idle to the time ranges where records already exist. Post updated to clarify.
• user234725: I read you right then. Your question falls into the class of problems known as gaps and islands. I have another solution in my head I will try to complete & submit. Another thought I had was to use a view to generate the idle ranges on demand.

Answer 1

Something like

INSERT INTO logs (Start, Logname, End)
SELECT End + INTERVAL '1 second', -- previous End + 1 second
 'Idle', -- static literal value
 End + INTERVAL '2 second' -- current Start + 1 second
FROM logs
ON CONFLICT (Start) DO NOTHING; -- skip insertion if the row exists already

The query does not check the overlapping which may occur if 2 source rows are adjacent (End_1 = Start_2). If you'd check this then use according WHERE NOT EXISTS instread of ON CONFLICT.

• Lacie: can you explain more about how to use WHERE NOT EXISTS? Usually, it's WHERE NOT EXISTS (SELECT ....) & I'm not sure how to achieve it in my case
• Akina: @Elly INSERT INTO logs SELECT .. FROM logs t1 WHERE NOT EXISTS (SELECT NULL FROM logs t2 WHERE {t1&t2 overlapping detection} ).
• Lacie: Thank you! I combined your solution and another solution I found, and although I haven't done a complete sanity check yet, seems like it works.