CODEWITHSUNDEEP
pythonsqlpostgresqlpostgresql-9.1exists
pythoncoder
pythoncoder

Reputation: 675

How to use exists condition in select query in postgresql?

I want to check whether the emp_id and date has been already there in both bill_item and bill_item_temp tables or not? if not exists then i have to do insert?

s = CONN.cursor()                 
query = """insert into bill_item (id,product_name,price,create_date,emp_id)
           select %s,%s,%s,%s,%s where not exists ( select 1 from bill_item where emp_id = %s and 
           select 1 from bill_item_ref where emp_id = %s);"""
s.execute(query,(id,product_name,price,create_date,emp_id,));

table: bill_item

id product_name price create_date                   emp_id
1  rice         10    2021-03-09 10:10:10.231321    12345
2  tea          10    2021-03-09 08:10:10.231321    22345

table: bill_item_temp

id product_name   price create_date                   emp_id
1  drinks         10    2021-03-09 10:10:10.231321    67345
2  snacks         10    2021-03-09 08:10:10.231321    92345

Upvotes: 2

Views: 881

Answers (1)

Mushif Ali Nawaz
Mushif Ali Nawaz

Reputation: 3866

The FROM clause is missing in this query:

select %s,%s,%s,%s,%s where not exists(...)

And the SELECT statement has incorrect syntax here:

where not exists ( select 1 from bill_item where emp_id = %s and 
                   select 1 from bill_item_ref where emp_id = %s);

You can't have AND between two SELECT statements.

Either use UNION/UNION ALL or use separate EXISTS for individual SELECT statement.

This is how you can use UNION ALL:

where not exists ( select 1 from bill_item where emp_id = %s UNION ALL 
                   select 1 from bill_item_ref where emp_id = %s);

And this is how you can use separate EXISTS for individual SELECT statement:

where not exists (select 1 from bill_item where emp_id = %s) and
      not exists (select 1 from bill_item_ref where emp_id = %s);

Upvotes: 1

Related Questions