How to Correctly Use LIKE Queries with Go's pq Driver and PostgreSQL?

Go PostgreSQL LIKE Query Syntax

When working with PostgreSQL and Go's pq driver, you may encounter error messages regarding syntax when executing LIKE queries. Let's explore the issue and provide a solution.

The query in the provided code is:

SELECT p.id, p.name, p.description, p.price, p.image, p.rate
FROM products AS p
WHERE LOWER(p.name) LIKE %% ORDER BY p.rate DESC

Executing this query directly in PostgreSQL works, but in Go, it raises an error:

pq: syntax error at or near "%"

The issue lies in the incorrect syntax for the LIKE operator. The placeholder should be enclosed in single quotes:

SELECT p.id, p.name, p.description, p.price, p.image, p.rate
FROM products AS p
WHERE LOWER(p.name) LIKE '%' ||  || '%'
ORDER BY p.rate DESC

Using single quotes ensures the placeholder is treated as a literal string rather than a part of the query itself.

To avoid further syntax issues, consider using named placeholders instead of positional ones:

query := `SELECT p.id, p.name, p.description, p.price, p.image, p.rate
        FROM products AS p
        WHERE LOWER(p.name) LIKE '%' || :name || '%'
        ORDER BY p.rate DESC`

product_rows, err := db.Query(query, map[string]interface{}{"name": name})

Using named placeholders increases code readability and reduces the risk of syntax errors.

The above is the detailed content of How to Correctly Use LIKE Queries with Go's pq Driver and PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!