path: root/schema.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 14.6 (Ubuntu 14.6-0ubuntu0.22.04.1)
-- Dumped by pg_dump version 14.6 (Ubuntu 14.6-0ubuntu0.22.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;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: elvia; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.elvia (
    meteringpointid bigint NOT NULL,
    starttime timestamp with time zone NOT NULL,
    endtime timestamp with time zone NOT NULL,
    value numeric,
    unit character varying(10),
    production boolean,
    inserted timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: elvia_gridtariff; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.elvia_gridtariff (
    meteringpointid bigint NOT NULL,
    starttime timestamp with time zone NOT NULL,
    endtime timestamp with time zone NOT NULL,
    shorttime character varying(9),
    ispublicholiday boolean DEFAULT false NOT NULL,
    price numeric,
    priceexvat numeric,
    inserted timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: entsoe; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.entsoe (
    starttime timestamp with time zone NOT NULL,
    zone character varying(4) NOT NULL,
    price numeric NOT NULL,
    currency character varying(4) DEFAULT 'EUR'::character varying NOT NULL,
    unitmeasure character varying(4) DEFAULT 'MWH'::character varying NOT NULL,
    inserted timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: location; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.location (
    id smallint NOT NULL,
    name character varying(255) NOT NULL,
    description text,
    lon real,
    lat real,
    created timestamp with time zone DEFAULT now() NOT NULL,
    updated timestamp with time zone
);


--
-- Name: location_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

ALTER TABLE public.location ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.location_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Name: mqtt_han; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.mqtt_han (
    id bigint NOT NULL,
    name text,
    current real,
    power real,
    voltage real,
    linkquality smallint,
    "time" timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: mqtt_han_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.mqtt_han_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: mqtt_han_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.mqtt_han_id_seq OWNED BY public.mqtt_han.id;


--
-- Name: mqtt_temps; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.mqtt_temps (
    id integer NOT NULL,
    name text NOT NULL,
    temperature real,
    humidity real,
    battery smallint,
    linkquality smallint,
    voltage integer,
    "time" timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: mqtt_temps_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

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


--
-- Name: mqtt_temps_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.mqtt_temps_id_seq OWNED BY public.mqtt_temps.id;


--
-- Name: nbex; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.nbex (
    startdate date NOT NULL,
    base_cur character varying(3) DEFAULT 'EUR'::character varying NOT NULL,
    quote_cur character varying(3) DEFAULT 'NOK'::character varying NOT NULL,
    rate numeric NOT NULL,
    inserted timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: neohub; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.neohub (
    id integer NOT NULL,
    "timestamp" timestamp with time zone DEFAULT now() NOT NULL,
    "time" time without time zone,
    device_id smallint,
    away boolean,
    heat_mode boolean,
    heat_on boolean NOT NULL,
    current_floor_temperature real,
    target_temperature real,
    temperature real
);


--
-- Name: neohub_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

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


--
-- Name: neohub_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.neohub_id_seq OWNED BY public.neohub.id;


--
-- Name: room; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.room (
    id smallint NOT NULL,
    location_id smallint NOT NULL,
    name character varying(255) NOT NULL,
    description text,
    created timestamp with time zone DEFAULT now() NOT NULL,
    updated timestamp with time zone
);


--
-- Name: room_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

ALTER TABLE public.room ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.room_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Name: tibber_consumption; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.tibber_consumption (
    starttime timestamp with time zone NOT NULL,
    endtime timestamp with time zone NOT NULL,
    consumption numeric NOT NULL,
    consumptionunit character varying(10) DEFAULT 'kWh'::character varying,
    cost numeric NOT NULL,
    unitprice numeric NOT NULL,
    unitpricevat numeric NOT NULL,
    inserted timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: tibber_prices; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.tibber_prices (
    startsat timestamp with time zone NOT NULL,
    total numeric NOT NULL,
    energy numeric NOT NULL,
    tax numeric NOT NULL,
    level character varying(16),
    inserted timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: yr; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.yr (
    "time" timestamp with time zone NOT NULL,
    location smallint NOT NULL,
    air_temperature real,
    air_pressure_at_sea_level real,
    cloud_area_fraction real,
    relative_humidity real,
    wind_from_direction real,
    wind_speed real,
    inserted timestamp with time zone DEFAULT now() NOT NULL,
    updated timestamp with time zone
);


--
-- Name: mqtt_han id; Type: DEFAULT; Schema: public; Owner: -
--

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


--
-- Name: mqtt_temps id; Type: DEFAULT; Schema: public; Owner: -
--

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


--
-- Name: neohub id; Type: DEFAULT; Schema: public; Owner: -
--

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


--
-- Name: elvia el_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.elvia
    ADD CONSTRAINT el_pkey PRIMARY KEY (starttime, endtime);


--
-- Name: elvia_gridtariff elvia_gridtariff_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.elvia_gridtariff
    ADD CONSTRAINT elvia_gridtariff_pkey PRIMARY KEY (meteringpointid, starttime, endtime);


--
-- Name: entsoe entsoe_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.entsoe
    ADD CONSTRAINT entsoe_pkey PRIMARY KEY (starttime, zone);


--
-- Name: location location_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.location
    ADD CONSTRAINT location_pkey PRIMARY KEY (id);


--
-- Name: mqtt_han mqtt_han_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.mqtt_han
    ADD CONSTRAINT mqtt_han_pkey PRIMARY KEY (id);


--
-- Name: mqtt_temps mqtt_temps_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.mqtt_temps
    ADD CONSTRAINT mqtt_temps_pkey PRIMARY KEY (id);


--
-- Name: nbex nbexchange_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.nbex
    ADD CONSTRAINT nbexchange_pkey PRIMARY KEY (startdate, base_cur, quote_cur);


--
-- Name: neohub neohub_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.neohub
    ADD CONSTRAINT neohub_pkey PRIMARY KEY (id);


--
-- Name: room room_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.room
    ADD CONSTRAINT room_pkey PRIMARY KEY (id);


--
-- Name: tibber_consumption tibber_consumption_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.tibber_consumption
    ADD CONSTRAINT tibber_consumption_pkey PRIMARY KEY (starttime, endtime);


--
-- Name: tibber_prices tibber_prices_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.tibber_prices
    ADD CONSTRAINT tibber_prices_pkey PRIMARY KEY (startsat);


--
-- Name: yr yr_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.yr
    ADD CONSTRAINT yr_pkey PRIMARY KEY ("time", location);


--
-- Name: room fk_location; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.room
    ADD CONSTRAINT fk_location FOREIGN KEY (location_id) REFERENCES public.location(id);


--
-- PostgreSQL database dump complete
--