I recently switched over from the integrated SQLite database to a PostgreSQL database since I already had that running in another VM.
I didn’t want to lose my data and especially my energy dashboard data so I exported the SQLite database and imported it in Postgres.
This all went great and it’s up and running and I can correctly pull all the imported data from my database however it has now stopped logging new data in the energy dashboard.
Sensors get logged no problem.
The core log says the following:
2022-02-02 22:00:10 ERROR (Recorder) [homeassistant.components.recorder.util]
Error executing query: (psycopg2.errors.UniqueViolation) duplicate key value
violates unique constraint "statistics_pkey"
DETAIL: Key (id)=(51) already exists.
[SQL: INSERT INTO statistics (created, start, mean, min, max, last_reset, state,
sum, metadata_id) VALUES (%(created)s, %(start)s, %(mean)s, %(min)s,
%(max)s, %(last_reset)s, %(state)s, %(sum)s, %(metadata_id)s)
RETURNING statistics.id]
It seems that it’s trying to insert records with id = 51 but those are already taken, which makes sense because I imported older data.
Is there a way to let HomeAssistant know that it should skip those id’s and start at say 10 000?
I’ve tried Recorder.purge with repack but that hasn’t helped.
This issue will probably also kick up with sensor data as soon as the new event_id’s have caught up with the inserted rows.
I’m used to the database handling the identity column instead of the application inserting a static value there. Is there a way to let Postgres handle this instead of HA?