PostgreSQL query - typecasting

Question

Performing PostgreSQL query

when searching from two lists for matches in a database i receive the following error:

    ProgrammingError: operator does not exist: character varying ~~ text[]
LINE 1: ...FROM public."Phosphosite_table" WHERE "GENE_NAME" LIKE ARRAY...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

my code is the following:

start = time.time()

input_file = "az20.tsv"

names = []
residuelist = []
kinase = []
fclist = []

with open(input_file, "r") as data:
    data = csv.reader(data, delimiter='\t')
    next(data, None)
    for row in data:
        data = row[0:6]
        if "(" in data[0]:
            name = data[0].split("(")[0]
            residue1 = data[0].split("(")[1]
            residue = residue1.split(")")[0]
            fc = data[3]
        else:
            pass
        if "_" in name: 
            name = name.split("_")[0]  
        if residue != "None":
            names.append(str(name))
            residuelist.append(str(residue))
            fclist.append(fc)

genename = names
location = residuelist

connection = pg.connect(HAHAHA)

cur = connection.cursor()

cur.execute('SELECT "KINASE_NAME" FROM public."Phosphosite_table" WHERE "GENE_NAME" LIKE %s and "RESIDUE" LIKE %s',\
            (genename, location))

query = cur.fetchall()

print query

connection.close()

end = time.time()
print str((end - start)/60) + " Minutes"

I have done some research and it appears that PostgreSQL does not perform any typecasting. However, I thought it would be a comparison of a string against a string, which, I changed before appending to my list. Does anyone have any advice?

Connor

user330315 · Accepted Answer · 2019-02-12 16:05:24Z

1

However, I thought it would be a comparison of a string against a string

The part character varying ~~ text[] of the error message tells you, that you are comparing a string ("character varying") with an array ("text[]").

If you want to compare a single value with all elements of an array you need to use the ANY operator:

WHERE "GENE_NAME" LIKE any(%s)

assuming that %s is passed as native Postgres array from your code (which seems to be the case given the error message).

answered Feb 12, 2019 at 16:05

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL query - typecasting

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related