pgx preparedStatement execution does not return results (unlike fmt.Sprintf)

For some reason, the prepareStatement I built in go using pgx is not returning any results/rows (see scenario1). In scenario 2, if I build the same query using Sprintf and $, the results are also not returned.

But, as shown in scenario 3, if I build the query with a Sprintf with %s placeholders and execute it, it will return results/rows.

I would like to build/execute the query using the "logic" of scenario 1 (with prepared statement instead with Sprintf). Any ideas?

I tested the query directly on the DB (with pgadmin) and it has data/results.

Assumption: I suspect it might have something to do with:

  • in the WHERE condition: the uuid data type or with the string in the IN condition
  • the query result that returns a uuid and a jsonb

Details: The DB is postgres and the table t has, among others, the field types:

  • t.str1 and t.properties are string
  • t.my_uuid is uuid.UUID (https://github.com/satori/go.uuid)
  • t.C is jsonb
  • the pgx version I'm using is: github.com/jackc/pgx v3.6.2+incompatible

scenario 1

func prepareQuery(cp *pgx.ConnPool) (string, error) {
    prep := `SELECT DISTINCT
        A,B,C
        FROM table t
        WHERE t.str1 = $1
        AND t.properties IN ($2)
        AND t.my_uuid = $3`

    _, err := cp.Prepare("queryPrep", prep)
    if err != nil {
        return "some error", err
    }
    return prep, nil
}

func x() {
   //...
    q, err := prepareQuery(cPool)
    if err != nil {
        return fmt.Errorf("failed to prepare query %s: %w", q, err)
    }

    rows, err := cPool.Query("queryPrep", "DumyStr", "'prop1','prop2'", myUUID) //myUUID is a valid satori uuid
    if err != nil {
        //return error...
    }

    for rows.Next() {   // zero rows
        //Scan...
    }
   //...
}

scenario 2

func makeQueryStr()string {
    return fmt.Sprintf(`SELECT DISTINCT
        A,B,C
        FROM table t
        WHERE t.str1 = $1
        AND t.properties IN ($2)
        AND t.my_uuid = $3`)
}

func x() {
   //...
    myQuery := makeQueryStr()
    rows, err := cPool.Query(myQuery, "DumyStr", "'prop1','prop2'", myUUID) //myUUID is a valid satori uuid
    if err != nil {
        //return error...
    }

    for rows.Next() {   // zero rows
        //Scan...
    }
   //...
}

scenario 3

func makeQueryStr(par1 string, par2 string, par3 uuid.UUID)string {
    return fmt.Sprintf(`SELECT DISTINCT
        A,B,C
        FROM table t
        WHERE t.str1 = '%s'
        AND t.properties IN (%s)
        AND t.my_uuid = '%s'`, par1, par2, par3)
}

func x() {
   //...
    myQuery := makeQueryStr("DumyStr", "'prop1','prop2'", myUUID) //myUUID is a valid satori uuid
    rows, err := cPool.Query(myQuery)
    if err != nil {
        //return error...
    }

    for rows.Next() {   // 10 rows
        //Scan...
    }
   //...
}
How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum