How to use exists condition in select query in postgresql?

Question

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

Unrelated to your problem, but: Postgres 9.1 is no longer supported you should plan an upgrade as soon as possible. — user330315
– user330315, Commented Mar 9, 2021 at 7:15
could you give a alternate solution for how to check the 2 select queries by using exists at the same time — pythoncoder
– pythoncoder, Commented Mar 9, 2021 at 7:21

Mushif Ali Nawaz · Accepted Answer · 2021-03-09 07:41:03Z

1

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);

edited Mar 9, 2021 at 7:41

answered Mar 9, 2021 at 7:16

Mushif Ali Nawaz

3,9163 gold badges21 silver badges33 bronze badges

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

6 Comments

pythoncoder Over a year ago

Could you please give me the code by using union.. i am a fresher i dono how to use

Mushif Ali Nawaz Over a year ago

@pythoncoder That depends on your use case here. Do you want both entries to be present? What should happen if there is an entry in bill_item but it's missing in bill_item_ref (and vice versa)?

pythoncoder Over a year ago

either one of the tables has the record means (exists) means then insertion should not happen.

Mushif Ali Nawaz Over a year ago

@pythoncoder I have updated my answer to include UNION ALL.

Mushif Ali Nawaz Over a year ago

This is a Python exception. Can you try executing this query directly in Postgres?

|

Collectives™ on Stack Overflow

How to use exists condition in select query in postgresql?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related