ERROR: operator does not exist: character varying = bytea

Question

I have a table in postgres with the following structure

CREATE TABLE rpaul."HK_LOGIN_DETAILS"
(
  "HK_LOGIN_DETAILS_ID" bigint NOT NULL,
  "HK_LOGIN_DETAILS_USERNAME" character varying(10) NOT NULL,
  "HK_LOGIN_DETAILS_PASSWORD" character varying(50) NOT NULL,
  CONSTRAINT "HK_LOGIN_DETAILS_PK" PRIMARY KEY ("HK_LOGIN_DETAILS_ID" ),
  CONSTRAINT "HK_LOGIN_DETAILS_UK" UNIQUE ("HK_LOGIN_DETAILS_USERNAME" )
)

And hibernate mapping for this table is as mentioned below

<hibernate-mapping package="net.rpaul.projects.homekeeping.domain.login">
    <class name="LoginDetails" table="`HK_LOGIN_DETAILS`">
        <id name="id" column="`HK_LOGIN_DETAILS_ID`" type="long">
            <generator class="assigned" />
        </id>
        <property name="userName" type="string" column="`HK_LOGIN_DETAILS_USERNAME`" not-null="true" />
        <property name="password" type="string" column="`HK_LOGIN_DETAILS_PASSWORD`" not-null="true" />
    </class>
</hibernate-mapping>

In the LoginDetails.java, I have declared id field as long, userName and password fields as String. Still when I try to execute the following

List list =  getHibernateTemplate().find("from LoginDetails ld where ld.userName = ?", userName);

I get

ERROR: operator does not exist: character varying = bytea

I am not getting what has went wrong. Any help would be appreciated.

Luettaluevano

Answer 1

I think you should check that your variable "userName" is not null. I experienced this message in cases like that.

Tawny

It can be null in my case. What to do if it is the case? – Titrate

precheck for null wherever you are querying usingthe variable – Vaquero

I got this problem once and I solved setting the variable as empty string('') before querying, in case it was null. – Nonaggression

Thank you very much @GuilhermeAlencar, I was struggling to make it work. That's not right though, null doesn't work, empty string works like it's null. Strange. – Eldoneldora

Answer 2

The question is old but still sharing my solution I've used in Spring Boot if anyone needed.

You can use the below WHERE conditions for handling NULL values or making the parameters optional in the Postgres Query.

SELECT * FROM table 
WHERE 
          (?1 is null OR column1 = cast(?1 AS text)) 
          AND 
          (?2 is null OR column2 = cast(?2 AS text))

Here whole WHERE condition will be TRUE if column1 and column2 are passed as NULL. column1 and column2 will be considered in the query if not NULL.

?1 is null OR column1 = ?1 : will check if passed value is null, then whole where condition will be true (will not check for second condition [column1 = null] if null - PG Optimization)

cast(?1 AS text) : can be useful if for some reason value is passed as bytea. If the passed value is real null, it will still give the "character varying bytea" error if not casted.

Haman

This approach works only for nativeQuery=false – Esthonia

Answer 3

It seems that Hibernate is for some reason sending the type-parameter as bytea (or rather, probably java.sql.Types.BLOB), instead of leaving it for the server to infer or setting it to text (java.sql.Types.STRING). Here is similar issue with solution JPA lower() function on parameter

Albinaalbinism

Answer 4

For Native Query we can use

SELECT * FROM table 
WHERE 
          (:paramName is null OR column1 = cast(:paramName AS text)) 

and then

query.setParamter(paramName,value);

Alixaliza

Answer 5

Troubleshooting checklist :

#1 : Depending on the database type you are using you would want to find the the column names and their respective data types using this SQL command :

SELECT
   table_name,
   column_name,
   data_type
FROM
   information_schema.columns
WHERE
   table_name = 'the-name-of-the-table-in-the-database';

Expected results would give you three columns; and more especially the 'data_type' column.

Make sure your Pojo class and the respective data types match appropriately.

Take note : bigint (data type) in the table inside the database can match with a Long seamlessly. integer with int. character varying with String or a major java class, eg. a class storing Enums, and so on.

After confirming the above, do the next check -> troubleshooting :

#2 : The Main checks on this troubleshooting is to check that all the data types match perfectly. And do pay attention to the parameters passed to the query.

Passing an enum or or any other data type or an enum type that is not conforming to the SQL data types could trigger the 'is not mapped' error(s) even if the pojo class matches perfectly with the table structure in the database.

pojo example : UserAccountBalance.class

import io.swagger.v3.oas.annotations.media.Schema;
import lombok.*;

@Builder//Lombok
@AllArgsConstructor(access = AccessLevel.PRIVATE)
@NoArgsConstructor(access = AccessLevel.PUBLIC)
@Data//Lombok
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
@Schema
@Entity(name = "user_account_balance")
@Table(name = "user_account_balance")
public class UserAccountBalance {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private long id;

    @NotNull
    @Column(name = "username", nullable = false)
    private String userName;

    @NotNull
    @Column(name="currency_code", nullable = false)
    @Enumerated(EnumType.STRING)
    private CurrencyCode currencyCode;

    @NotNull
    @Column(name = "balance", nullable = false)
    private BigDecimal balance;

//Could be placed into and AuditModel class
    @Column(name = "datecreated", nullable = false, updatable = false)
    @JsonIgnore
    @DateCreated
    @CreationTimestamp
    private LocalDateTime dateCreated;

    @Column(name = "date_updated", nullable = false, updatable = false)
    @JsonIgnore
    @DateUpdated
    private LocalDateTime dateUpdated;

    @NotNull
    @Column(name = "active")
    @JsonIgnore
    private int active;

    @Column(name = "deleted")
    @JsonIgnore
    private int deleted;

}

Repository class :

//Option 1 : UserAccountBalanceRepository.class

@Repository
public abstract class UserAccountBalanceRepository implements CrudRepository<UserAccountBalance, Long> {

    private final EntityManager entityManager;

    public UserAccountBalanceRepository(@CurrentSession EntityManager entityManager){
        this.entityManager = entityManager;
    }

    @Transactional(readOnly = true)
    @Query(
            value="SELECT uab.*" +
                    " FROM public.user_account_balance uab" +
                    " WHERE (currency_code =cast(:currencyCode AS text)" +
                    " AND userName =:userName" +
                    " AND active =:active)",
            countQuery = "SELECT uab.*" +
                    " FROM public.user_account_balance uab" +
                    " WHERE (currency_code = cast(:currencyCode AS text)" +
                    " AND userName =:userName" +
                    " AND active =:active)",
            nativeQuery = true
    )
    public abstract Optional<UserAccountBalance> findByUserAccountBalance_UserName_And_CurrencyCode(
            String userName,
            CurrencyCode currencyCode,
            int active
    );

}

//Option 2 : UserAccountBalanceRepository.class

@Repository
public abstract class UserAccountBalanceRepository implements CrudRepository<UserAccountBalance, Long> {

    private final EntityManager entityManager;

    public UserAccountBalanceRepository(@CurrentSession EntityManager entityManager){
        this.entityManager = entityManager;
    }

    @Transactional(readOnly = true)
    @Query(
            value="SELECT uab.*" +
                    " FROM public.user_account_balance uab" +
                    " WHERE (currency_code =:currencyCode" +
                    " AND userName =:userName" +
                    " AND active =:active)",
            countQuery = "SELECT uab.*" +
                    " FROM public.user_account_balance uab" +
                    " WHERE (currency_code = :currencyCode" +
                    " AND userName =:userName" +
                    " AND active =:active)",
            nativeQuery = true
    )
    public abstract Optional<UserAccountBalance> findByUserAccountBalance_UserName_And_CurrencyCode(
            String userName,
            String currencyCode,/*this is what truly worked out for me perfectly*/
            int active
    );

}

#3. Test and test again. If problem still persist please have patience and look through all your variables and classes again.

#4. If troubleshooting using option #3 still does not help, consider taking a little walk, take some little rest and have a fresh set of eyes to look at it all over from troubleshooting #1.

I hope this helps. Cheers and peace.

Femme

Answer 6

What you can do this first cast null parameter as text and then COALESCE it with '' empty strings so your code becomes

List list = getHibernateTemplate().find("from LoginDetails ld where COALESCE(ld.userName,'') =COALESCE(cast(? as TEXT),'')", userName);

I have tried this work around with Postgress and JPA

Doorplate

Answer 7

The SQL adapter received from your application a Python bytes object, and rendered that to the database as a PostgreSQL bytea(rray) value.

Postgresql doesn't have an equality comparison between Pg bytearrays and Pg varchars. They aren't the same kind of thing. The first is opaque uninterpretable data, and the second is supposed to be text.

What you probably mean to do is decode that bytearray or Python bytes object to a Python str Unicode object, and send that in your query instead. (It is, of course, being converted from Unicode into some encoding, but you can trust the database adapter to handle that part.)

That assumes you know what encoding that bytes thing is.

However, if it really is not Unicode text you are trying to send to the database, then you should choose a different data type in your DDL and table definition, one that isn't text-ish, like Pg bytea.

Magdala