No operator matches the given name and argument types. You might need to add explicit type casts

Question

I am using PostgreSQL for storing username and their corresponding salted and hashed password but it is continuously giving me this error.

LINE 1: ...egister where USERNAME = 'siddharth' and PASSWORD = '\x24326...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

My Table Name is hassle_free_register and its structure is as follows as :

                                       Table "public.hassle_free_register"
  Column  |          Type          | Collation | Nullable |                        Default
----------+------------------------+-----------+----------+-------------------------------------------------------
 user_id  | integer                |           | not null | nextval('hassle_free_register_user_id_seq'::regclass)
 username | character varying(255) |           | not null |
 password | character varying(255) |           | not null |
Indexes:
    "hassle_free_register_pkey" PRIMARY KEY, btree (user_id)
    "hassle_free_register_username_key" UNIQUE CONSTRAINT, btree (username)

CODE for registering the user :-

   @app.route('/register' ,methods =['POST'])
def register():
   try:
      NAME = request.form['USER_NAME']
      PASSWORD = request.form['USER_PASSWORD']
      if(len(NAME)==0):
         return jsonify({"message" :"USERNAME CANNOT BE EMPTY"}),400
      if(len(PASSWORD)<=8):
         return jsonify({"message" :"PASSWORD LENGTH TOO SHORT"}),400
      if(len(PASSWORD)>=30):
         return jsonify({"message" :"PASSWORD LENGTH TOO LONG"}),400
      HASHEDPASS = bcrypt.hashpw(PASSWORD.encode('utf-8'),bcrypt.gensalt())    
      mycursor.execute("insert into hassle_free_register (USERNAME,PASSWORD) values(%s, %s);",(NAME,HASHEDPASS)) 

      #ERROR IS OCCURING IN THIS LINE
      mycursor.execute("select USER_ID from Hassle_Free_Register where USERNAME = %s and PASSWORD = %s;",(NAME,HASHEDPASS)) 

      data = mycursor.fetchone()
      print(data)
      mycursor.execute("create table {TABLENAME} (PASSWORD_ID int SERIAL NOT NULL PRIMARY KEY,APP_NAME varchar(255) NOT NULL, APP_USERNAME varchar(255) NOT NULL , APP_PASSWORD varchar(255) NOT NULL);".format(TABLENAME = NAME + "_" + str(data[0])))
      mydb.commit()
      return jsonify("REGISTERED SUCCESSFULLY") 
   except TypeError as error:
      print(error)
      return jsonify({"message":str(error)}),403
   except ValueError as error:
      print(error)
      return jsonify({"message":str(error)}),403
   except psycopg2.Error as error:
      print(error)
      return jsonify({"message":str(error)}),403

(EDITED AFTER COMMENTS) I am new to Python and postgrSQL. Please Help !

I ran the query which was giving the error in the SQL shell (psql) but it ran successfully their.

QUERY :-

hassle_free=# select USER_ID from hassle_free_register where USERNAME = 'siddharth' and PASSWORD = '\x24326224313224587174764d532e6265334d6654354e47514a6d73674f6d72327a75723966747a42542e5a2e4f48374e74446d6e76355353752e7461';

RESULT :-

 user_id
---------
      39
(1 row)

Please Help.

Answer 1

To be clear what is happening:

create table pwd_test(id int, password varchar);

import psycopg2
import bcrypt 
pwd = bcrypt.hashpw('test_pwd'.encode('utf-8'),bcrypt.gensalt()) 

pwd
b'$2b$12$DTSJSsuuhwgyMdSOqLmb0.4RAk.smxQERas/i7WcR3NKTPtLQfoPK'
# Note the b'
# From here [Binary adaptation](https://www.psycopg.org/docs/usage.html#adapt-binary)
# This gets converted to bytea

cur.execute('insert into pwd_test values(%s,%s)', [2, pwd])
-- On the INSERT it gets cast to a varchar
select * from pwd_test where id = 2;
 id |                                                          password                                                          
----+----------------------------------------------------------------------------------------------------------------------------
  2 | \x243262243132244454534a53737575687767794d64534f714c6d62302e3452416b2e736d7851455261732f6937576352334e4b5450744c51666f504b

# When you do the comparison you are comparing the varchar value in the table to the bytea type that is pwd.

cur.execute('select * from pwd_test where password = %s', [pwd])                                                                                                          
---------------------------------------------------------------------------
UndefinedFunction                         Traceback (most recent call last)
<ipython-input-15-e2bde44ff261> in <module>
----> 1 cur.execute('select * from pwd_test where password = %s', [pwd])

UndefinedFunction: operator does not exist: character varying = bytea
LINE 1: select * from pwd_test where password = '\x24326224313224445...
                                              ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

# You need to cast the varchar to bytea
cur.execute('select * from pwd_test where password::bytea = %s', [pwd]) 

cur.fetchone()
 id |                                                          password                                                          
----+----------------------------------------------------------------------------------------------------------------------------
  2 | \x243262243132244454534a53737575687767794d64534f714c6d62302e3452416b2e736d7851455261732f6937576352334e4b5450744c51666f504b

So your options are do the cast of password to bytea in the query or change the column type of password to bytea.