Query BYTEA in Postgres using sqlx

Question

I have the following table in Postgres DB:

CREATE TABLE resources_index (
    hash varchar NOT NULL,
    "name" varchar NOT NULL,
    viewentry_id varchar NOT NULL,
    value bytea NULL
);

I'd like to execute the following prepared statement using sqlx:

SELECT
      t0.name,
      t0.value
    FROM
      (
        SELECT
          name,
          value
        FROM
          resources_index
        WHERE
          hash = $1
          AND value :: BYTEA LIKE $2 :: BYTEA
      ) AS t0
    LIMIT
      $3

Problem occurs in the following line: AND value :: BYTEA LIKE $2 :: BYTEA

Postgres DB error response is: ERROR: operator does not exist: character varying = bytea

Query is executed as:

let mut q = sqlx::query_as::<sqlx::Postgres, IndexRow>(&query);
    for v in values.iter() {
        log::debug!("bind value {}", v);
        q = q.bind(v.as_bytes());
    }
    q.bind(limit).fetch_all(pool).await

For me it looks like v is casted to VARCHAR though it should be BYTEA.

Do you have any idea whether this is a bug or am I doing it the wrong way?

score 0 · Answer 1 · answered Jul 08 '21 at 05:41

Resolved it using the inbuilt decode function.

SELECT
      t0.name,
      t0.value
    FROM
      (
        SELECT
          name,
          value
        FROM
          resources_index
        WHERE
          hash = $1
          AND value LIKE decode($2, 'escape')
      ) AS t0
    LIMIT
      $3

Query BYTEA in Postgres using sqlx

1 Answers1