recorder:
  db_url: "postgresql://homeassistant:homeassistant@homeassistant/homeassistant"

CREATE EXTENSION pg_ivm;
SELECT 
create_immv('states_view', 'SELECT
CASE WHEN state~E''^\\d+$'' THEN state::integer ELSE 0 END as state_num,
CASE WHEN state~E''^[+-]?([0-9]*[.])?[0-9]+$'' THEN state::float ELSE 0.0 END as state_float,
CASE WHEN NOT (state~E''^[+-]?([0-9]*[.])?[0-9]+$'' OR state~E''^\\d+$''  ) THEN state ELSE '''' END 
AS state_str,
to_timestamp(last_updated_ts) as ts,
sm.entity_id
FROM states
INNER JOIN states_meta sm
using(metadata_id)');
create unique index on states_view (ts);
alter table states_view ALTER COLUMN ts SET NOT NULL;
ALTER TABLE states_view REPLICA IDENTITY USING INDEX states_view_ts_idx;

CREATE DATABASE homeassistant
ENGINE = MaterializedPostgreSQL('HA_IP:5432', 'homeassistant', 'homeassistant', 'homeassistant')