CODEWITHSUNDEEP
sqlpostgresqlgo
sescob27
sescob27

Reputation: 677

Go postgresql LIKE query

I'm working with Go and PostgreSQL (pq driver), I have the following query

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

If I exec this query directly in PostgreSQL it work but in Golang says:

pq: syntax error at or near "%"

How can I fix it? I tried with "\%" but didn't works. thanks.

here is the complete source code

func FindByName(name *string) ([]*Product, error) {
    db, err := db.StablishConnection()
    if err != nil {
            log.Fatal(err)
            panic(err)
    }
    defer db.Close()

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

    product_rows, err := db.Query(query, name)

    if err != nil {
            return nil, err
    }

    if product_rows == nil {
            return nil, errors.New("No Products Named " + *name)
    }

    products := []*Product{}
    for product_rows.Next() {
            product := new(Product)
            err = product_rows.Scan(&product.Id,
                    &product.Name,
                    &product.Description,
                    &product.Price,
                    &product.Image,
                    &product.Rate)
            if err != nil {
                    panic(err)
            }
            products = append(products, product)
    }
    return products, nil
}

Upvotes: 28

Views: 23991

Answers (6)

user2259500
user2259500

Reputation: 19

This works for me

query := "SELECT ProductId,Name,Description,Price,SKU FROM Products WHERE Name LIKE ?"
rows, err := r.db.QueryContext(ctx, query, "%"+name+"%")

Upvotes: -1

Anton Yurchenko
Anton Yurchenko

Reputation: 619

I guess this is the most correct way of doing this:

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

product_rows, err := db.Query(query, name)

if err != nil {
        return nil, err
}

Upvotes: 1

Julien Ricard
Julien Ricard

Reputation: 314

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

product_rows, err := db.Query(query, '%' + name+ '%'))

Upvotes: -2

Mohit
Mohit

Reputation: 7

Dont put qoutes when you are preparing your query. Just provide the value with qoutes and % sign. This will solve the problem. tried and tested.

Solution: query := SELECT p.id, p.name, p.description, p.price, p.image, p.rate FROM products AS p WHERE LOWER(p.name) LIKE $1 ORDER BY p.rate DESC

product_rows, err := db.Query(query, "'%" + name + "%'")

I got my soltion from this thread

Upvotes: -1

TheROX
TheROX

Reputation: 106

According to this issue your query must not contain '%' sign, but "name" parameter must be quoted by '%'

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

product_rows, err := db.Query(query, fmt.Sprintf("%%%s%%", name))

Upvotes: -3

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You need to put the like pattern 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 '%' || $1 || '%'
ORDER BY p.rate DESC;

Upvotes: 53

Related Questions