第4回：Ludiaを体験 (4/4)

ludia=# copy zip from '/home/postgres/KEN_ALL.CSV' with csv;
COPY

ludia=# copy zip2 from '/home/postgres/KEN_ALL.CSV' with csv;
COPY


ludia=# create index idx_word on zip using fulltext(address);
CREATE INDEX
ludia=# create index idx_ngram on zip2 using fulltextb(address);
CREATE INDEX
ludia=# \d zip

Table "public.zip"
Column	|	Type	|	Modifiers
−−−−−−−−−	+	−−−−−−−−−−−−−−	+	−−−−−−−−−−
zipcode	|	character(7)	|
address	|	text	|
Indexes:
"idx_word" fulltext (address)
ludia=# \d zip2
Table "public.zip2"
Column	|	Type	|	Modifiers
−−−−−−−−−	+	−−−−−−−−−−−−−−	+	−−−−−−−−−−
zipcode	|	character(7)	|
address	|	text	|
Indexes:
"idx_word" fulltext (address)

ludia=# select * from zip where address @@ '茨木市春日';
zipcode	|	address
−−−−−−−−−−−−−−−	+	−−−−−−−−−−−−−−−
5670031	|	大阪府茨木市春日
(1 row)
ludia=# select * from zip2 where address @@ '茨木市春日';
zipcode	|	address
−−−−−−−−−−−−−−−	+	−−−−−−−−−−−−−−−
5670031	|	大阪府茨木市春日
(1 row)

ludia=# select * from zip where address @@ '+茨木市 +春日';
zipcode	|	address
−−−−−−−−−−−−−−−	+	−−−−−−−−−−−−−−−
5670031	|	大阪府茨木市春日
(1 row)
ludia=# select * from zip2 where address @@ '+茨木市 +春日';
zipcode	|	address
−−−−−−−−−−−−−−−	+	−−−−−−−−−−−−−−−
5670031	|	大阪府茨木市春日
5670046	|	大阪府茨木市南春日丘
5670048	|	大阪府茨木市北春日丘
(3 row)