CODEWITHSUNDEEP
sqlpostgresqlnullpsycopg2
91DarioDev
91DarioDev

Reputation: 1700

Handle query using NOT IN and NULL

my application permits users to get matched and i don't want they get matched more times in an interval after their match ended.

i store on redis with (zadd) the matches and the unix timestamp.

in my postgres query so i would like to be able to do:

SELECT user_id FROM users WHERE user_id NOT IN %s

that's just the subquery and there are many many other conditions in the where, but the problem is when redis returns an empty list because the user hasn't been active during that interval, NOT IN () returns an error.

what's the best way to handle it? using case and when? also i was thinking to put by default a fake negative user_id in the list redis returns, but i don't like that much this hack

edit: a sample of my query is: 

UPDATE users SET status = %s WHERE user_id IN (SELECT user_id FROM users WHERE user_id NOT IN % LIMIT 1)

The query worked fine but today i added the part user_id NOT in %s inside the subquery. I pass there the tuple that redis returns (or better redis returns a list and i convert it in a tuple). It contains just integers like [1234, 3456, 678]. It works fine when the tuple is not empty (at least one element inside) but if the tuple is empty i get this error:

psycopg2.ProgrammingError: ERROR:  syntax error at or near ")"
LINE 1: ...ocked = 0 AND bot_lang = 'en' AND user_id NOT IN () ORDER BY...

indeed i solved this adding by default a fake user_id inside the tuple so that it is never empty, but i don't like this hack and i would like to be able find a more nice solution.

Upvotes: 2

Views: 91

Answers (3)

Abelisto
Abelisto

Reputation: 15624

Instead of user_id NOT IN (%s) you could to use user_id <> ALL(ARRAY[%s]::int[]) or NOT user_id = ANY(ARRAY[%s]::int[]) (note that I suggest to exclude parenthesizes from the parameter value)

So, for non-empty list:

select 1 <> all(array[1,2]::int[]);

and for empty list

select 1 <> all(array[]::int[]);

both works fine.

Update: for psycopg2 the better way is to let it to resolve the format of parameters. For arrays it could be just:

a = [1,2,3]
cursor.execute("... NOT user_id = ANY(%s) ...",(a,))

Upvotes: 2

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

You can try exception handling here. Something like this - 

select coalesce(username,0) from user where user NOT IN ('ranjeet', 'jha') 
or user IS NULL;

Upvotes: 0

ranjeet jha
ranjeet jha

Reputation: 11

Simpl tsql query can be writen as:

select * from table_name where coulmnName NOT IN ('value1','value2','value3')

for example:

select username from user where user NOT IN ('ranjeet', 'jha')

where user is a table_name, username is columnname, and 'ranjeet' and 'jha' two provided values.

Upvotes: 0

Related Questions