How to Properly Use LIKE Queries with Parameterized Values in Go's pq PostgreSQL Driver?

Go postgresql LIKE Query with Parameterized Value

In Go, when working with the pq driver for PostgreSQL, it is necessary to ensure correct formatting of queries involving LIKE clauses. A common issue encountered when attempting to use the LIKE operator is a syntax error indicating an invalid character at or near the percentage symbol ("%").

To resolve this issue, the LIKE pattern must be enclosed in single quotes when passed as a parameter. This ensures that the special characters are interpreted correctly by the PostgreSQL database. The updated query below includes this correction:

query := `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;`

In this query, the LIKE pattern is enclosed in single quotes and concatenated with the $1 parameter using the || operator. This ensures that the database interprets the pattern as a string and not as a special character.

With this modification, the query should execute successfully in PostgreSQL. It will find products whose names match the specified pattern and order the results in descending order of their rating.

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