Postgres auto insert with previous values if some data misses

user_3607698 0
Viewed 96

I have data with date range, some date wont come for few days, during the missed window I just want to insert the previous data.

Is there way to take care of this during insert of data it self

For example

create table foo (ID VARCHAR(10), foo_count int, actual_date date);
insert into foo ( values('234534', 100, '2017-01-01'),('234534', 200, '2017-01-02'));   
insert into foo ( values('234534', 300, '2017-01-03') );
insert into foo ( values('234534', 300, '2017-01-08') );

After the last insert I can make sure previous data gets generated

So it should look something like this

 ID        | foo_count       | actual_date
-----------+-----------------+------------
 234534    | 100             | 2017-01-01
 234534    | 200             | 2017-02-01
 234534    | 300             | 2017-03-01
 234534    | 300             | 2017-04-01
 234534    | 300             | 2017-05-01
 234534    | 300             | 2017-06-01
 234534    | 180             | 2017-07-01

I am using JPA to insert it, currently I query the table and see the current date in the table and populate the missing data

spring-data-jpa postgresql hibernate
1 Answers

I would think about a better INSERT statement. Inserting from a SELECT statement would make things easier. The SELECT statement could be used to generate the requested date series.

INSERT INTO foo
SELECT
    --<advanced query>

However, I guess, that's not simply possible since you are not using the JDBC directly or want to use native queries for inserting your data.

In that case, you could install a trigger to your database which could do the magic:

demo:db<>fiddle

Trigger function:

CREATE FUNCTION insert_missing()
    RETURNS TRIGGER AS
$$
DECLARE
    max_record record;
BEGIN
    SELECT                                                       -- 1
        id,
        foo_count,
        actual_date
    FROM
        foo
    ORDER BY actual_date DESC
    LIMIT 1
    INTO max_record;

    IF (NEW.actual_date - 1 > max_record.actual_date) THEN       -- 2
        INSERT INTO foo
        SELECT 
            max_record.id,
            max_record.foo_count,
            generate_series(max_record.actual_date + 1, NEW.actual_date - 1,  interval '1 day');  -- 3
    END IF;

    RETURN NEW;
END;
$$ language 'plpgsql';
  1. Query the record with the current maximum date.
  2. If the maximum date is more than one day before the new date...
  3. ... Insert a date series (from day after current max date until the date before the new one). This can be generated with generate_series().

Afterwards create the ON BEFORE INSERT trigger:

CREATE TRIGGER insert_missing
  BEFORE INSERT
  ON foo
  FOR EACH ROW
  EXECUTE PROCEDURE insert_missing();
user_39842210
Related