Here’s my schema definition. Hope it helps.
--
-- PostgreSQL database dump
--
-- Dumped from database version 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1)
-- Dumped by pg_dump version 10.6 (Ubuntu 10.6-0ubuntu0.18.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 client_min_messages = warning;
SET row_security = off;
--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: events; Type: TABLE; Schema: public; Owner: homeassistant
--
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(36),
context_user_id character(36)
);
ALTER TABLE public.events OWNER TO homeassistant;
--
-- Name: events_event_id_seq; Type: SEQUENCE; Schema: public; Owner: homeassistant
--
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 homeassistant;
--
-- Name: events_event_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: homeassistant
--
ALTER SEQUENCE public.events_event_id_seq OWNED BY public.events.event_id;
--
-- Name: recorder_runs; Type: TABLE; Schema: public; Owner: homeassistant
--
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 homeassistant;
--
-- Name: recorder_runs_run_id_seq; Type: SEQUENCE; Schema: public; Owner: homeassistant
--
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 homeassistant;
--
-- Name: recorder_runs_run_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: homeassistant
--
ALTER SEQUENCE public.recorder_runs_run_id_seq OWNED BY public.recorder_runs.run_id;
--
-- Name: schema_changes; Type: TABLE; Schema: public; Owner: homeassistant
--
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 homeassistant;
--
-- Name: schema_changes_change_id_seq; Type: SEQUENCE; Schema: public; Owner: homeassistant
--
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 homeassistant;
--
-- Name: schema_changes_change_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: homeassistant
--
ALTER SEQUENCE public.schema_changes_change_id_seq OWNED BY public.schema_changes.change_id;
--
-- Name: states; Type: TABLE; Schema: public; Owner: homeassistant
--
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,
context_id character(36),
context_user_id character(36)
);
ALTER TABLE public.states OWNER TO homeassistant;
--
-- Name: states_state_id_seq; Type: SEQUENCE; Schema: public; Owner: homeassistant
--
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 homeassistant;
--
-- Name: states_state_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: homeassistant
--
ALTER SEQUENCE public.states_state_id_seq OWNED BY public.states.state_id;
--
-- Name: events event_id; Type: DEFAULT; Schema: public; Owner: homeassistant
--
ALTER TABLE ONLY public.events ALTER COLUMN event_id SET DEFAULT nextval('public.events_event_id_seq'::regclass);
--
-- Name: recorder_runs run_id; Type: DEFAULT; Schema: public; Owner: homeassistant
--
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: homeassistant
--
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: homeassistant
--
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: homeassistant
--
ALTER TABLE ONLY public.events
ADD CONSTRAINT events_pkey PRIMARY KEY (event_id);
--
-- Name: recorder_runs recorder_runs_pkey; Type: CONSTRAINT; Schema: public; Owner: homeassistant
--
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: homeassistant
--
ALTER TABLE ONLY public.schema_changes
ADD CONSTRAINT schema_changes_pkey PRIMARY KEY (change_id);
--
-- Name: states states_pkey; Type: CONSTRAINT; Schema: public; Owner: homeassistant
--
ALTER TABLE ONLY public.states
ADD CONSTRAINT states_pkey PRIMARY KEY (state_id);
--
-- Name: ix_events_context_id; Type: INDEX; Schema: public; Owner: homeassistant
--
CREATE INDEX ix_events_context_id ON public.events USING btree (context_id);
--
-- Name: ix_events_context_user_id; Type: INDEX; Schema: public; Owner: homeassistant
--
CREATE INDEX ix_events_context_user_id ON public.events USING btree (context_user_id);
--
-- Name: ix_events_event_type; Type: INDEX; Schema: public; Owner: homeassistant
--
CREATE INDEX ix_events_event_type ON public.events USING btree (event_type);
--
-- Name: ix_events_time_fired; Type: INDEX; Schema: public; Owner: homeassistant
--
CREATE INDEX ix_events_time_fired ON public.events USING btree (time_fired);
--
-- Name: ix_recorder_runs_start_end; Type: INDEX; Schema: public; Owner: homeassistant
--
CREATE INDEX ix_recorder_runs_start_end ON public.recorder_runs USING btree (start, "end");
--
-- Name: ix_states_context_id; Type: INDEX; Schema: public; Owner: homeassistant
--
CREATE INDEX ix_states_context_id ON public.states USING btree (context_id);
--
-- Name: ix_states_context_user_id; Type: INDEX; Schema: public; Owner: homeassistant
--
CREATE INDEX ix_states_context_user_id ON public.states USING btree (context_user_id);
--
-- Name: ix_states_entity_id; Type: INDEX; Schema: public; Owner: homeassistant
--
CREATE INDEX ix_states_entity_id ON public.states USING btree (entity_id);
--
-- Name: ix_states_entity_id_last_updated; Type: INDEX; Schema: public; Owner: homeassistant
--
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: homeassistant
--
CREATE INDEX ix_states_event_id ON public.states USING btree (event_id);
--
-- Name: ix_states_last_updated; Type: INDEX; Schema: public; Owner: homeassistant
--
CREATE INDEX ix_states_last_updated ON public.states USING btree (last_updated);
--
-- Name: states states_event_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: homeassistant
--
ALTER TABLE ONLY public.states
ADD CONSTRAINT states_event_id_fkey FOREIGN KEY (event_id) REFERENCES public.events(event_id);
--
-- PostgreSQL database dump complete
--