PostgreSQL多列小写+去重音模糊查询需求及SQL优化问询

Got it, let's adjust your SQL query to meet both of your matching requirements. The key here is to apply accent removal and case insensitivity to both the name and address columns, then check either column for the processed search term.

First, make sure you have the unaccent extension enabled in PostgreSQL (it's not on by default) — run this once if you haven't already:

CREATE EXTENSION IF NOT EXISTS unaccent;

Here's the modified query that handles all your cases:

SELECT * FROM schools 
WHERE 
  -- Check if the processed name contains the processed search term
  unaccent(lower(name)) LIKE '%' || unaccent(lower('your_search_input')) || '%'
  OR 
  -- Check if the processed address contains the processed search term
  unaccent(lower(address)) LIKE '%' || unaccent(lower('your_search_input')) || '%';

Let's break down why this works:

• unaccent() strips out diacritics (like the á in "odborná"), so "stredna odborna" will match "Stredná odborná" perfectly.
• lower() converts everything to lowercase, ensuring case-insensitive matches (so "Stredná" and "stredná" are treated identically).
• The LIKE '%term%' pattern allows partial matches, which is why "bratis" will catch "Bratislava" in the address.
• The OR condition means we look for matches in either the name or address column.

For better security and reusability (especially in apps), use a parameterized query instead of hardcoding the search term. Here's how that looks with a placeholder (PostgreSQL uses $1 for the first parameter):

SELECT * FROM schools 
WHERE 
  unaccent(lower(name)) LIKE '%' || unaccent(lower($1)) || '%'
  OR 
  unaccent(lower(address)) LIKE '%' || unaccent(lower($1)) || '%';

This way, you can pass the user's input directly as the parameter without worrying about SQL injection.

Testing this with your examples:

• Input "stredna odborna": Both rows' names will match after processing, so you get both results.
• Input "bratis": Only row 1's address will contain the processed term, so you get just that row.

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