diff options
author | Dirk Engling <erdgeist@erdgeist.org> | 2019-02-23 23:27:40 +0100 |
---|---|---|
committer | Dirk Engling <erdgeist@erdgeist.org> | 2019-02-23 23:27:40 +0100 |
commit | 602c32207ac0085489dcdd436a6866d35455fc89 (patch) | |
tree | f8ebd07c3f0681194a48e4ef972bde326b7ecafe /src/postprocess/postgres.txt | |
parent | 380b982004e35e14f6b2ad5c5d82f33c6a3c2b4e (diff) |
Add code for database import
Diffstat (limited to 'src/postprocess/postgres.txt')
-rw-r--r-- | src/postprocess/postgres.txt | 41 |
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 @@ | |||
1 | CREATE 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 '{}' | ||
18 | ); | ||
19 | |||
20 | 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'; | ||
21 | |||
22 | CREATE MATERIALZED VIEW nachname_view AS SELECT id, nachname FROM telefonbuch CROSS JOIN UNNEST(nachname) WITH ORDINALITY u(name, name_id) WITH data; | ||
23 | |||
24 | CREATE INDEX idx_hausnummer ON public.telefonbuch USING btree (hausnummer); | ||
25 | CREATE INDEX idx_multilinegroup ON public.telefonbuch USING btree (multilinegroup); | ||
26 | CREATE INDEX idx_nachname ON public.telefonbuch USING btree (nachname); | ||
27 | CREATE INDEX idx_ort ON public.telefonbuch USING btree (ort); | ||
28 | CREATE INDEX idx_rufnummer ON public.telefonbuch USING btree (rufnummer); | ||
29 | CREATE INDEX idx_strasse ON public.telefonbuch USING btree (strasse); | ||
30 | CREATE INDEX idx_vorname ON public.telefonbuch USING btree (vorname); | ||
31 | CREATE INDEX idx_vorwahl ON public.telefonbuch USING btree (vorwahl); | ||
32 | CREATE INDEX idx_zip ON public.telefonbuch USING btree (zip); | ||
33 | CREATE INDEX idx_zusaetze ON public.telefonbuch USING btree (zusaetze); | ||
34 | |||
35 | CREATE INDEX trgm_idx_nachname ON public.telefonbuch USING gin (nachname gin_trgm_ops); | ||
36 | CREATE INDEX trgm_idx_rufnummer ON public.telefonbuch USING gin (rufnummer gin_trgm_ops); | ||
37 | CREATE INDEX trgm_idx_verweise ON public.telefonbuch USING gin (verweise gin_trgm_ops); | ||
38 | CREATE INDEX trgm_idx_vorname ON public.telefonbuch USING gin (vorname gin_trgm_ops); | ||
39 | CREATE INDEX trgm_idx_zusaetze ON public.telefonbuch USING gin (zusaetze gin_trgm_ops); | ||
40 | |||
41 | SELECT *, ST_AsText(coords) FROM Telefonbuch WHERE nachname = 'Engling'; | ||