PostgreSQL FULLTEXT SEARCH (Ranking)
PostgreSQL FULLTEXT SEARCH (Ranking) is a SQL statement in the PostgreSQL Specific category. PostgreSQL full-text search with ranking functions: ts_rank, ts_rank_cd, and weighted vectors. The syntax is SELECT ts_rank(to_tsvector('english', body), to_tsquery('cats & dogs')) AS rank;. It returns ranked search results. A typical example: CREATE INDEX articles_fts ON articles USING GIN(to_tsvector('english', title || ' ' || body)); -- Weighted search (A=title, B=body): SELECT title, ts_rank( setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', body), 'B'), to_tsquery('english', 'database & optimization') ) AS rank FROM articles WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'database & optimization') ORDER BY rank DESC LIMIT 10; -- Title matches weighted higher than body matches -- ts_headline for snippets: SELECT ts_headline('english',… A close relative is SERIAL, which postgreSQL auto-increment type. Creates an INTEGER column with a sequence. Equivalent to AUTO_INCREMENT in MySQL. A close relative is RETURNING, which postgreSQL extension that returns values from INSERT/UPDATE/DELETE statements (like OUTPUT in SQL Server).