PostgreSQL: Extremely fast text search using tsvector

-- Example table: create table patient ( id serial primary key, firstname varchar(100), lastname varchar(100) ); insert into patient(firstname, lastname) values ('MARCO ANTONIO', 'PEREZ SANDERS'); -- Create field tsvector field with text columns (preferable using insert and update trigger); alter table patient add tsv_name tsvector; -- initially fill field. update patient set tsv_name = to_tsvector(firstname || ' ' || lastname); -- create index for fast search: create index tsv_name_idx on patient using gin(tsv_name); -- then you can search as: select * from patient where tsv_name @@ to_tsquery('MARCO & PEREZ'); select * from patient where tsv_name @@ to_tsquery('MARCO & SANDERS'); select * from patient where tsv_name @@ to_tsquery('ANTONIO & PEREZ'); select * from m_paciente where tsv_nombre @@ to_tsquery(array_to_string(string_to_array('LEIRY SEVERINO',' '),':* & ')||':*'); -- other: select * from patient where fullname ~ all(array['(?=^LEI.*|\s+LEI\w*)','(?=^ME.*|\s+ME\w*)']);
Fast search using an indexed tsvector field.

Be the first to comment

You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.