-
Notifications
You must be signed in to change notification settings - Fork 0
/
postgres-vector-search-script.txt
28 lines (27 loc) · 1.09 KB
/
postgres-vector-search-script.txt
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
{\rtf1\ansi\ansicpg1252\cocoartf1038\cocoasubrtf360
{\fonttbl\f0\fswiss\fcharset0 Helvetica;}
{\colortbl;\red255\green255\blue255;}
\paperw11900\paperh16840\margl1440\margr1440\vieww12960\viewh10060\viewkind0
\pard\tx566\tx1133\tx1700\tx2267\tx2834\tx3401\tx3968\tx4535\tx5102\tx5669\tx6236\tx6803\ql\qnatural\pardirnatural
\f0\fs24 \cf0 ALTER TABLE post ADD COLUMN title_tsv tsvector;\
ALTER TABLE post ADD COLUMN body_tsv tsvector;\
\
CREATE TRIGGER tsvectortitleupdate BEFORE INSERT OR UPDATE ON post \
FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(title_tsv, 'pg_catalog.english', title);\
\
CREATE TRIGGER tsvectorbodyupdate BEFORE INSERT OR UPDATE ON post \
FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(body_tsv, 'pg_catalog.english', body);\
\
CREATE INDEX title_tsv ON post USING gin(title_tsv);\
CREATE INDEX body_tsv ON post USING gin(body_tsv);\
\
UPDATE post SET title_tsv=to_tsvector(title);\
UPDATE post SET body_tsv=to_tsvector(body);\
\
SELECT * FROM post WHERE title_tsv @@ plainto_tsquery('first');\
--SELECT * FROM post WHERE body_tsv @@ plainto_tsquery('django');\
\
\
\
\
}