From 602c32207ac0085489dcdd436a6866d35455fc89 Mon Sep 17 00:00:00 2001
From: Dirk Engling <erdgeist@erdgeist.org>
Date: Sat, 23 Feb 2019 23:27:40 +0100
Subject: Add code for database import

---
 src/postprocess/postgres.txt | 41 +++++++++++++++++++++++++++++++++++++++++
 1 file changed, 41 insertions(+)
 create mode 100644 src/postprocess/postgres.txt

(limited to 'src/postprocess/postgres.txt')

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 @@
+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';
-- 
cgit v1.2.3