PostgreSQL SELECT when integer unique - invalid input syntax

Question

  cur.execute("""SELECT  name, number 
    FROM store
    WHERE number=%s OR name =%s""",
    (number, name))

My problem is that number is INTEGER UNIQUE and when I try to select by number everything is ok, but if I try to select by name I received an error:

invalid input syntax for integer

You should be using parameters, rather than munging the string! — Gordon Linoff, Mar 20 '19 at 18:37
Also note that the [`invalid input syntax for integer` error may occur](https://stackoverflow.com/a/18298348/190597) if an empty string is passed as an argument instead of an integer. If that is the problem, `int(number)` will raise a ValueError on the Python side. — unutbu, Mar 20 '19 at 18:43
@glaadys: We want to help you but "it doesn't work" is never informative enough. Instead, please tell us what error message you see. — unutbu, Mar 20 '19 at 18:45
It is my problem actually, because in GUI tkinter I would like to have an option to search my database only by providing the name OR number, not providing both — glaadys, Mar 20 '19 at 18:46
On the Python side I entry a string, my database has a column with int unique number, and text name. When I am inserting data, everything works but the problem is when I try to select from database (through GUI tkinter) item which has entered name without providing number — glaadys, Mar 20 '19 at 18:49

unutbu · Answer 1 · 2019-03-20T19:08:59.357

It sounds like number is sometimes not an integer, such as an empty string. You could handle this on the Python side by assigning number to None whenever int(number) is invalid:

try:
    number = int(number)
except ValueError:
    number = None

cur.execute("""SELECT  name, number 
    FROM store
    WHERE number=%s OR name =%s""",
    (number, name))

cur.execute will handle None as NULL.

in GUI tkinter I would like to have an option to search my database only by providing the name OR number, not providing both

If you want to search by number (first) or by name (second), but never both, then use

import psycopg2
try:
    cur.execute("""SELECT  name, number 
        FROM store
        WHERE number=%s""", (number, ))

except psycopg2.DataError:
    cur.execute("""SELECT  name, number 
        FROM store
        WHERE name=%s""", (name, ))

PostgreSQL SELECT when integer unique - invalid input syntax

1 Answers1