Home Assistant Postgresql schema as running with 2020.12.1

my_ha_schema_postgresql.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 12.3 (Debian 12.3-1.pgdg100+1)
-- Dumped by pg_dump version 12.5 (Ubuntu 12.5-0ubuntu0.20.04.1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: homeassistant; Type: DATABASE; Schema: -; Owner: postgres
--

CREATE DATABASE homeassistant WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8';


ALTER DATABASE homeassistant OWNER TO postgres;

\connect homeassistant

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: isnumeric(text); Type: FUNCTION; Schema: public; Owner: user
--

CREATE FUNCTION public.isnumeric(text) RETURNS boolean
    LANGUAGE plpgsql IMMUTABLE STRICT
    AS $_$
DECLARE x NUMERIC;
BEGIN
    x = $1::NUMERIC;
    RETURN TRUE;
EXCEPTION WHEN others THEN
    RETURN FALSE;
END;
$_$;


ALTER FUNCTION public.isnumeric(text) OWNER TO "user";

--
-- Name: trigger_on_events(); Type: FUNCTION; Schema: public; Owner: user
--

CREATE FUNCTION public.trigger_on_events() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin

    insert into events_archive
    select new.*;
    return new;
end; $$;


ALTER FUNCTION public.trigger_on_events() OWNER TO "user";

--
-- Name: trigger_on_states(); Type: FUNCTION; Schema: public; Owner: user
--

CREATE FUNCTION public.trigger_on_states() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin

    insert into states_archive
    select new.*;
    return new;
end; $$;


ALTER FUNCTION public.trigger_on_states() OWNER TO "user";

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: events; Type: TABLE; Schema: public; Owner: user
--

CREATE TABLE public.events (
    event_id integer NOT NULL,
    event_type character varying(32),
    event_data text,
    origin character varying(32),
    time_fired timestamp with time zone,
    created timestamp with time zone,
    context_id character varying(36),
    context_user_id character varying(36),
    context_parent_id character varying(36)
);


ALTER TABLE public.events OWNER TO "user";

--
-- Name: events_archive; Type: TABLE; Schema: public; Owner: user
--

CREATE TABLE public.events_archive (
    event_id integer,
    event_type character varying(32),
    event_data text,
    origin character varying(32),
    time_fired timestamp with time zone,
    created timestamp with time zone,
    context_id character varying(36),
    context_user_id character varying(36),
    context_parent_id character varying(36)
);


ALTER TABLE public.events_archive OWNER TO "user";

--
-- Name: events_event_id_seq; Type: SEQUENCE; Schema: public; Owner: user
--

CREATE SEQUENCE public.events_event_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.events_event_id_seq OWNER TO "user";

--
-- Name: events_event_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: user
--

ALTER SEQUENCE public.events_event_id_seq OWNED BY public.events.event_id;


--
-- Name: mikrotik_device_history; Type: TABLE; Schema: public; Owner: user
--

CREATE TABLE public.mikrotik_device_history (
    id integer NOT NULL,
    mac_address macaddr,
    ip_address cidr,
    device_name character varying(40),
    host character varying(20),
    interface character varying(20),
    signal smallint,
    wifi_info jsonb,
    last_scene timestamp without time zone
);


ALTER TABLE public.mikrotik_device_history OWNER TO "user";

--
-- Name: mikrotik_device_history_id_seq; Type: SEQUENCE; Schema: public; Owner: user
--

CREATE SEQUENCE public.mikrotik_device_history_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.mikrotik_device_history_id_seq OWNER TO "user";

--
-- Name: mikrotik_device_history_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: user
--

ALTER SEQUENCE public.mikrotik_device_history_id_seq OWNED BY public.mikrotik_device_history.id;


--
-- Name: recorder_runs; Type: TABLE; Schema: public; Owner: user
--

CREATE TABLE public.recorder_runs (
    run_id integer NOT NULL,
    start timestamp with time zone,
    "end" timestamp with time zone,
    closed_incorrect boolean,
    created timestamp with time zone
);


ALTER TABLE public.recorder_runs OWNER TO "user";

--
-- Name: recorder_runs_run_id_seq; Type: SEQUENCE; Schema: public; Owner: user
--

CREATE SEQUENCE public.recorder_runs_run_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.recorder_runs_run_id_seq OWNER TO "user";

--
-- Name: recorder_runs_run_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: user
--

ALTER SEQUENCE public.recorder_runs_run_id_seq OWNED BY public.recorder_runs.run_id;


--
-- Name: schema_changes; Type: TABLE; Schema: public; Owner: user
--

CREATE TABLE public.schema_changes (
    change_id integer NOT NULL,
    schema_version integer,
    changed timestamp with time zone
);


ALTER TABLE public.schema_changes OWNER TO "user";

--
-- Name: schema_changes_change_id_seq; Type: SEQUENCE; Schema: public; Owner: user
--

CREATE SEQUENCE public.schema_changes_change_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.schema_changes_change_id_seq OWNER TO "user";

--
-- Name: schema_changes_change_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: user
--

ALTER SEQUENCE public.schema_changes_change_id_seq OWNED BY public.schema_changes.change_id;


--
-- Name: states; Type: TABLE; Schema: public; Owner: user
--

CREATE TABLE public.states (
    state_id integer NOT NULL,
    domain character varying(64),
    entity_id character varying(255),
    state character varying(255),
    attributes text,
    event_id integer,
    last_changed timestamp with time zone,
    last_updated timestamp with time zone,
    created timestamp with time zone,
    old_state_id integer
);


ALTER TABLE public.states OWNER TO "user";

--
-- Name: states_archive; Type: TABLE; Schema: public; Owner: user
--

CREATE TABLE public.states_archive (
    state_id integer,
    domain character varying(64),
    entity_id character varying(255),
    state character varying(255),
    attributes text,
    event_id integer,
    last_changed timestamp with time zone,
    last_updated timestamp with time zone,
    created timestamp with time zone,
    old_state_id integer
);


ALTER TABLE public.states_archive OWNER TO "user";

--
-- Name: states_archive_01; Type: VIEW; Schema: public; Owner: user
--

CREATE VIEW public.states_archive_01 AS
 SELECT states_archive.last_updated,
    states_archive.entity_id,
    states_archive.state,
    states_archive.state_id,
    states_archive.attributes
   FROM public.states_archive
  ORDER BY states_archive.last_updated DESC;


ALTER TABLE public.states_archive_01 OWNER TO "user";

--
-- Name: states_state_id_seq; Type: SEQUENCE; Schema: public; Owner: user
--

CREATE SEQUENCE public.states_state_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.states_state_id_seq OWNER TO "user";

--
-- Name: states_state_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: user
--

ALTER SEQUENCE public.states_state_id_seq OWNED BY public.states.state_id;


--
-- Name: states_test; Type: TABLE; Schema: public; Owner: user
--

CREATE UNLOGGED TABLE public.states_test (
    state_id integer,
    domain character varying(64),
    entity_id character varying(255),
    state character varying(255),
    attributes text,
    event_id integer,
    last_changed timestamp with time zone,
    last_updated timestamp with time zone,
    created timestamp with time zone,
    old_state_id integer
);


ALTER TABLE public.states_test OWNER TO "user";

--
-- Name: events event_id; Type: DEFAULT; Schema: public; Owner: user
--

ALTER TABLE ONLY public.events ALTER COLUMN event_id SET DEFAULT nextval('public.events_event_id_seq'::regclass);


--
-- Name: mikrotik_device_history id; Type: DEFAULT; Schema: public; Owner: user
--

ALTER TABLE ONLY public.mikrotik_device_history ALTER COLUMN id SET DEFAULT nextval('public.mikrotik_device_history_id_seq'::regclass);


--
-- Name: recorder_runs run_id; Type: DEFAULT; Schema: public; Owner: user
--

ALTER TABLE ONLY public.recorder_runs ALTER COLUMN run_id SET DEFAULT nextval('public.recorder_runs_run_id_seq'::regclass);


--
-- Name: schema_changes change_id; Type: DEFAULT; Schema: public; Owner: user
--

ALTER TABLE ONLY public.schema_changes ALTER COLUMN change_id SET DEFAULT nextval('public.schema_changes_change_id_seq'::regclass);


--
-- Name: states state_id; Type: DEFAULT; Schema: public; Owner: user
--

ALTER TABLE ONLY public.states ALTER COLUMN state_id SET DEFAULT nextval('public.states_state_id_seq'::regclass);


--
-- Name: events events_pkey; Type: CONSTRAINT; Schema: public; Owner: user
--

ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_pkey PRIMARY KEY (event_id);


--
-- Name: mikrotik_device_history mikrotik_device_history_pkey; Type: CONSTRAINT; Schema: public; Owner: user
--

ALTER TABLE ONLY public.mikrotik_device_history
    ADD CONSTRAINT mikrotik_device_history_pkey PRIMARY KEY (id);


--
-- Name: recorder_runs recorder_runs_pkey; Type: CONSTRAINT; Schema: public; Owner: user
--

ALTER TABLE ONLY public.recorder_runs
    ADD CONSTRAINT recorder_runs_pkey PRIMARY KEY (run_id);


--
-- Name: schema_changes schema_changes_pkey; Type: CONSTRAINT; Schema: public; Owner: user
--

ALTER TABLE ONLY public.schema_changes
    ADD CONSTRAINT schema_changes_pkey PRIMARY KEY (change_id);


--
-- Name: states states_pkey; Type: CONSTRAINT; Schema: public; Owner: user
--

ALTER TABLE ONLY public.states
    ADD CONSTRAINT states_pkey PRIMARY KEY (state_id);


--
-- Name: ix_events_archive_time_fired; Type: INDEX; Schema: public; Owner: user
--

CREATE INDEX ix_events_archive_time_fired ON public.events_archive USING btree (time_fired DESC);


--
-- Name: ix_events_context_id; Type: INDEX; Schema: public; Owner: user
--

CREATE INDEX ix_events_context_id ON public.events USING btree (context_id);


--
-- Name: ix_events_context_parent_id; Type: INDEX; Schema: public; Owner: user
--

CREATE INDEX ix_events_context_parent_id ON public.events USING btree (context_parent_id);


--
-- Name: ix_events_context_user_id; Type: INDEX; Schema: public; Owner: user
--

CREATE INDEX ix_events_context_user_id ON public.events USING btree (context_user_id);


--
-- Name: ix_events_event_type_time_fired; Type: INDEX; Schema: public; Owner: user
--

CREATE INDEX ix_events_event_type_time_fired ON public.events USING btree (event_type, time_fired);


--
-- Name: ix_events_time_fired; Type: INDEX; Schema: public; Owner: user
--

CREATE INDEX ix_events_time_fired ON public.events USING btree (time_fired);


--
-- Name: ix_recorder_runs_start_end; Type: INDEX; Schema: public; Owner: user
--

CREATE INDEX ix_recorder_runs_start_end ON public.recorder_runs USING btree (start, "end");


--
-- Name: ix_states_archive_last_updated; Type: INDEX; Schema: public; Owner: user
--

CREATE INDEX ix_states_archive_last_updated ON public.states_archive USING btree (last_updated DESC);


--
-- Name: ix_states_entity_id_last_updated; Type: INDEX; Schema: public; Owner: user
--

CREATE INDEX ix_states_entity_id_last_updated ON public.states USING btree (entity_id, last_updated);


--
-- Name: ix_states_event_id; Type: INDEX; Schema: public; Owner: user
--

CREATE INDEX ix_states_event_id ON public.states USING btree (event_id);


--
-- Name: ix_states_last_updated; Type: INDEX; Schema: public; Owner: user
--

CREATE INDEX ix_states_last_updated ON public.states USING btree (last_updated);


--
-- Name: events trigger_on_events; Type: TRIGGER; Schema: public; Owner: user
--

CREATE TRIGGER trigger_on_events BEFORE INSERT ON public.events FOR EACH ROW EXECUTE FUNCTION public.trigger_on_events();


--
-- Name: states trigger_on_states; Type: TRIGGER; Schema: public; Owner: user
--

CREATE TRIGGER trigger_on_states BEFORE INSERT ON public.states FOR EACH ROW EXECUTE FUNCTION public.trigger_on_states();


--
-- Name: states states_event_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: user
--

ALTER TABLE ONLY public.states
    ADD CONSTRAINT states_event_id_fkey FOREIGN KEY (event_id) REFERENCES public.events(event_id);


--
-- Name: DATABASE homeassistant; Type: ACL; Schema: -; Owner: postgres
--

GRANT ALL ON DATABASE homeassistant TO "user";


--
-- PostgreSQL database dump complete
--