- install pgAdmin
- refresh knowledge in basic SQL:
Host: wwc-workshop-read.cjfbgrnzzler.ap-southeast-2.rds.amazonaws.com
Port: 5432 (default)
Username: wwc
Password: Generic1
SELECT * FROM products WHERE id=1
SELECT * FROM products WHERE id=1
UPDATE products SET description='illegal modification' WHERE id=1
The following are the queries which will be demonstrated during the demo. You can execute them from your laptop:
SELECT * FROM products WHERE title ILIKE 'fair' OR description ILIKE 'fair'
SELECT * FROM products WHERE title ILIKE 'fair%' OR description ILIKE 'fair%'
SELECT * FROM products WHERE title ILIKE '%fair%' OR description ILIKE '%fair%'
SELECT * FROM products WHERE to_tsvector('english', title || ' ' || description) @@ to_tsquery('english', 'fair')
EXPLAIN ANALYZE SELECT * FROM products WHERE to_tsvector('english', title || ' ' || description) @@ to_tsquery('english', 'fair')
- Subword:
:*
, e.g.to_tsquery('english', 'cat:*')
- Two and more words to be found:
&
, e.g.to_tsquery('english', 'cat & dog')
- One of the given words to be found:
|
, e.g.to_tsquery('english', 'cat | dog')
- Rows with the given word to be excluded:
!
, e.g.to_tsquery('english', '!black & cat')
Find records with both words "cat" and "adaptive" in either title or description.
Expected result: 16 rows
Find all records which have words starting with "bot" in the title.
Expected result: 8 rows
Find records containing the word "machine", but not containing "gift" in either title or description.
Expected result: 2 rows
Execute the query to find out the stem of the word "programming":
select to_tsvector('english', 'programming')
Then perform full text search with this word as a search phrase to see all the matching forms of this word.
Expected result: 4 rows, 2 forms of the word "program"
Stop words are words that are very common, appear in almost every document, and have no discrimination value. Therefore, they can be ignored in the context of full text searching. For example, every English text contains words like a and the, so it is useless to store them in an index. The words "the" and "will" are included into the stop words list. For both "the" and "will" do the following:
- Find all records starting with this word (not subword!) in description using ILIKE
Expected result: 1 row for both words
- Check this word is ignored when searching with full text search
Expected result: 0 rows for both words
Read about PostgreSQL full text search ranking at https://www.postgresql.org/docs/9.1/static/textsearch-controls.html#TEXTSEARCH-RANKING.