Skip to content

Instantly share code, notes, and snippets.

@luigisaggese
Created October 30, 2013 08:05
Show Gist options
  • Save luigisaggese/7228772 to your computer and use it in GitHub Desktop.
Save luigisaggese/7228772 to your computer and use it in GitHub Desktop.
Table and search for fulltext
CREATE TABLE tsv_table (
email varchar(500),
what varchar(4096),
tsv tsvector
)
WITH (OIDS=FALSE)
;
CREATE INDEX ix_tsv_table_what ON tsv_table USING gin (tsv);
CREATE TRIGGER tr_tsv_table_beforeinsertupdate BEFORE INSERT OR UPDATE ON tsv_table
FOR EACH ROW
-- here you could specify your config language as
-- http://www.postgresql.org/docs/devel/static/textsearch-features.html#TEXTSEARCH-UPDATE-TRIGGERS
EXECUTE PROCEDURE tsvector_update_trigger('tsv', 'pg_catalog.english', 'tsv');
--This is my fulltext search
SELECT email, what
FROM tsv_table
WHERE tsv @@ to_tsquery('pg_catalog.english',<query_search_key>);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment