path: root/src/postprocess/postgres.txt
diff options
Diffstat (limited to 'src/postprocess/postgres.txt')
1 files changed, 41 insertions, 0 deletions
diff --git a/src/postprocess/postgres.txt b/src/postprocess/postgres.txt
new file mode 100644
index 0000000..d1b4669
--- /dev/null
+++ b/src/postprocess/postgres.txt
@@ -0,0 +1,41 @@
1CREATE TABLE telefonbuch (
2 presence_flag bigint,
3 reverse_flag bigint,
4 biz_flag bigint,
5 zip character varying(64)[] DEFAULT '{}',
6 nachname character varying(256)[] DEFAULT '{}',
7 vorname character varying(192)[] DEFAULT '{}',
8 zusaetze character varying(512)[] DEFAULT '{}',
9 strasse character varying(64)[] DEFAULT '{}',
10 hausnummer character varying(32)[] DEFAULT '{}',
11 verweise text[] DEFAULT '{}',
12 ort character varying(64)[] DEFAULT '{}',
13 vorwahl character varying(10)[] DEFAULT '{}',
14 rufnummer character varying(32)[] DEFAULT '{}',
15 web character varying(256)[] DEFAULT '{}',
16 email character varying(256)[] DEFAULT '{}',
17 coords character varying(32)[] DEFAULT '{}'
20COPY 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';
22CREATE MATERIALZED VIEW nachname_view AS SELECT id, nachname FROM telefonbuch CROSS JOIN UNNEST(nachname) WITH ORDINALITY u(name, name_id) WITH data;
24CREATE INDEX idx_hausnummer ON public.telefonbuch USING btree (hausnummer);
25CREATE INDEX idx_multilinegroup ON public.telefonbuch USING btree (multilinegroup);
26CREATE INDEX idx_nachname ON public.telefonbuch USING btree (nachname);
27CREATE INDEX idx_ort ON public.telefonbuch USING btree (ort);
28CREATE INDEX idx_rufnummer ON public.telefonbuch USING btree (rufnummer);
29CREATE INDEX idx_strasse ON public.telefonbuch USING btree (strasse);
30CREATE INDEX idx_vorname ON public.telefonbuch USING btree (vorname);
31CREATE INDEX idx_vorwahl ON public.telefonbuch USING btree (vorwahl);
32CREATE INDEX idx_zip ON public.telefonbuch USING btree (zip);
33CREATE INDEX idx_zusaetze ON public.telefonbuch USING btree (zusaetze);
35CREATE INDEX trgm_idx_nachname ON public.telefonbuch USING gin (nachname gin_trgm_ops);
36CREATE INDEX trgm_idx_rufnummer ON public.telefonbuch USING gin (rufnummer gin_trgm_ops);
37CREATE INDEX trgm_idx_verweise ON public.telefonbuch USING gin (verweise gin_trgm_ops);
38CREATE INDEX trgm_idx_vorname ON public.telefonbuch USING gin (vorname gin_trgm_ops);
39CREATE INDEX trgm_idx_zusaetze ON public.telefonbuch USING gin (zusaetze gin_trgm_ops);
41SELECT *, ST_AsText(coords) FROM Telefonbuch WHERE nachname = 'Engling';