CREATE TABLE telefonbuch (
    presence_flag bigint,
    reverse_flag bigint,
    biz_flag bigint,
    zip character varying(64)[] DEFAULT '{}',
    nachname character varying(256)[] DEFAULT '{}',
    vorname character varying(192)[] DEFAULT '{}',
    zusaetze character varying(512)[] DEFAULT '{}',
    strasse character varying(64)[] DEFAULT '{}',
    hausnummer character varying(32)[] DEFAULT '{}',
    verweise text[] DEFAULT '{}',
    ort character varying(64)[] DEFAULT '{}',
    vorwahl character varying(10)[] DEFAULT '{}',
    rufnummer character varying(32)[] DEFAULT '{}',
    web character varying(256)[] DEFAULT '{}',
    email character varying(256)[] DEFAULT '{}',
    coords character varying(32)[] DEFAULT '{}'
);

COPY Telefonbuch ( presence_flag, reverse_flag, biz_flag, zip, nachname, vorname, zusaetze, strasse, hausnummer, verweise, ort, vorwahl, rufnummer, web, email, coords ) FROM 'telefonbuch.txt' WITH NULL AS 'NULL';

CREATE MATERIALZED VIEW nachname_view AS SELECT id, nachname FROM telefonbuch CROSS JOIN UNNEST(nachname) WITH ORDINALITY u(name, name_id) WITH data;

CREATE INDEX idx_hausnummer ON public.telefonbuch USING btree (hausnummer);
CREATE INDEX idx_multilinegroup ON public.telefonbuch USING btree (multilinegroup);
CREATE INDEX idx_nachname ON public.telefonbuch USING btree (nachname);
CREATE INDEX idx_ort ON public.telefonbuch USING btree (ort);
CREATE INDEX idx_rufnummer ON public.telefonbuch USING btree (rufnummer);
CREATE INDEX idx_strasse ON public.telefonbuch USING btree (strasse);
CREATE INDEX idx_vorname ON public.telefonbuch USING btree (vorname);
CREATE INDEX idx_vorwahl ON public.telefonbuch USING btree (vorwahl);
CREATE INDEX idx_zip ON public.telefonbuch USING btree (zip);
CREATE INDEX idx_zusaetze ON public.telefonbuch USING btree (zusaetze);

CREATE INDEX trgm_idx_nachname ON public.telefonbuch USING gin (nachname gin_trgm_ops);
CREATE INDEX trgm_idx_rufnummer ON public.telefonbuch USING gin (rufnummer gin_trgm_ops);
CREATE INDEX trgm_idx_verweise ON public.telefonbuch USING gin (verweise gin_trgm_ops);
CREATE INDEX trgm_idx_vorname ON public.telefonbuch USING gin (vorname gin_trgm_ops);
CREATE INDEX trgm_idx_zusaetze ON public.telefonbuch USING gin (zusaetze gin_trgm_ops);

SELECT *, ST_AsText(coords) FROM Telefonbuch WHERE nachname = 'Engling';