Giriş
Tabloda tsvector tipinden bir sütun olur. Bu sütun için GIN indeks yaratırız.
CREATE INDEX indexname ON mytable USING GIN(tsvector_column_name);
1. tsvector tipindeki sütunu doldurmak için INSERT işleminden sonra bir tane de UPDATE gerekiyor
2. Bu sütunu sorgulamak için plainto_tsquery() veya to_tsquery() kullanılır
- plainto_tsquery() daha basit sorgulamalar içindir
- to_tsquery() daha karmaşık sorgulamalar içindir. AND, OR, NOT, * (wildcard), ? (wildcard), : (prefix), ~ (fuzzy match) gibi şeyleri destekler
Örnek
Şöyle yaparız
SELECT * FROM mytable WHERE tsvector_column_name @@ plainto_tsquery('my first story');
Örnek
ALTER TABLE storm_events ADD COLUMN episode_narrative_tsv tsvector;
UPDATE storm_events SET episode_narrative_tsv = to_tsvector(episode_narrative);
CREATE INDEX tsv_idx ON storm_events USING GIN(episode_narrative_tsv);
Örnek
-- Create the table
CREATE TABLE blog_posts (
id SERIAL PRIMARY KEY,
content TEXT
);
-- Insert sample data
INSERT INTO blog_posts (content)
VALUES
('Welcome to my blog on alternative of Elasticsearch'),
('In this blog post, we explore the power of ts_vector and GIN indexing'),
('PostgreSQL offers robust full-text search capabilities'),
('Searching for keywords in PostgreSQL has never been easier');
- Create `ts_vector` column and GIN index
ALTER TABLE blog_posts ADD COLUMN document tsvector;
UPDATE blog_posts SET document = to_tsvector('english', content);
CREATE INDEX gin_index ON blog_posts USING GIN(document);
-- Perform full-text search
SELECT * FROM blog_posts
WHERE document @@ to_tsquery('english', 'power & search');
Örnek
Elimizde şöyle tablolar olsun. Burada users ve users'a ait stories isimli iki tablo var. stories içinde tsvector tipinden ismi fulltext olan bir sütun var --Create Users table
CREATE TABLE IF NOT EXISTS users
(
id BIGSERIAL NOT NULL,
name CHARACTER VARYING(100) NOT NULL,
rating INTEGER,
PRIMARY KEY (id)
);
CREATE INDEX usr_rating_idx ON users USING BTREE (rating ASC NULLS LAST)
TABLESPACE pg_default;
--Create Stories table
CREATE TABLE IF NOT EXISTS stories
(
id BIGSERIAL NOT NULL,
create_date TIMESTAMP WITHOUT TIME ZONE NOT NULL,
num_views BIGINT NOT NULL,
title TEXT NOT NULL,
body TEXT NOT NULL,
fulltext TSVECTOR,
user_id BIGINT,
PRIMARY KEY (id),
CONSTRAINT user_id_fk FOREIGN KEY (user_id)
REFERENCES users (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID
);
CREATE INDEX str_bt_idx ON stories USING BTREE
(create_date ASC NULLS LAST, num_views ASC NULLS LAST, user_id ASC NULLS LAST);
CREATE INDEX fulltext_search_idx ON stories USING GIN (fulltext);
stories tablosuna satır eklemek
şöyle.
INSERT INTO stories (create_date, num_views, title, body, user_id)
VALUES (?, ?, ?, ?, ?)
UPDATE stories SET fulltext = to_tsvector(title || ' ' || body) where id = ?
Sorgulamak için şöyle
yaparız. plainto_tsquery() metodu ile string'i aramak istenile tipe çeviririz.
SELECT
s.id id,
create_date,
num_views,
title,
body,
user_id,
name user_name,
rating user_rating
FROM stories s INNER JOIN users u
ON s.user_id = u.id
WHERE true
AND rating BETWEEN 0 AND 100
AND num_views BETWEEN 0 AND 100
AND create_date BETWEEN '2023-08-28' AND '2023-08-29'
AND fulltext @@ plainto_tsquery('Second story')
ORDER BY create_date DESC OFFSET 0 LIMIT 100
;
