使用PgAdmin III创建friend表后，查询含'grand'的keywords列数据失败求助

Hey there! Let's break down why your query isn't returning results and fix it up step by step.

The Issue with Your Original Query #

Your current statement:

SELECT * FROM friend where keywords LIKE ' grand ';

This is looking for an exact match of the string grand (with spaces before and after). That means it will only return rows where the keywords column is exactly that phrase—no extra characters before, after, or in between. Chances are your actual data has 'grand' embedded in longer text, or without those surrounding spaces, so it's not matching anything.

Fix 1: Use Wildcards for Partial Matches #

PostgreSQL uses % as a wildcard to represent any sequence of characters (including zero characters). To find any row where keywords contains 'grand' anywhere in the string, use this:

SELECT * FROM friend WHERE keywords LIKE '%grand%';

This will match strings like "grand adventure", "my grand friend", "grandparent", or even just "grand" itself.

Fix 2: Case-Insensitive Matching #

If your data might have variations like 'Grand', 'GRAND', or 'gRand', swap LIKE for ILIKE to do case-insensitive searching:

SELECT * FROM friend WHERE keywords ILIKE '%grand%';

Fix 3: Exact Word Matching (Avoid Partial Words) #

If you want to match the whole word 'grand' and not longer words like 'grandmother' or 'grandpa', use PostgreSQL's regex matching with word boundaries:

SELECT * FROM friend WHERE keywords ~* '\mgrand\M';

• ~* triggers a case-insensitive regex match
• \m marks the start of a word
• \M marks the end of a word

Bonus: Optimize for Large Datasets #

If you run these kinds of text searches often and have a lot of rows, consider optimizing with a text search index. For example:

-- Add a generated tsvector column for faster searches
ALTER TABLE friend ADD COLUMN keywords_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', keywords)) STORED;

-- Create a GIN index on the tsvector column
CREATE INDEX idx_friend_keywords ON friend USING GIN (keywords_tsv);

-- Query using the indexed column
SELECT * FROM friend WHERE keywords_tsv @@ to_tsquery('english', 'grand');

内容的提问来源于stack exchange，提问作者gus