1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
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';
|
