CODEWITHSUNDEEP
sqlpostgresqlsql-insertinsert-query
pythoncoder
pythoncoder

Reputation: 675

How to insert into a table?

Table Name: free_meals_bill

punch_date                   employee_id  employee_name product_name
2021-02-22 12:15:50.086471   123456       john          Variety Rice - Curd - Rs.35
2021-02-22 12:19:50.086472   234456       marry         Variety Rice - Curd - Rs.35
2021-02-22 12:22:50.086473   355456       peter         Variety Rice - Curd - Rs.35

Before inserting into "free_meals_bill" table, I want to check that per employee_id only one punch is allowed. For example, if john (employee id 123456) is already in the free_meals_bill then again for the same date, john data should not be insert again into the "free_meals_bill" table.

Query:

 insert into free_meals_bill (punch_date,employee_id,employee_name,product_name)
 Values ('2021-02-22 10:15:50.086471',123456,'john','Variety Rice - Curd - Rs.35')
 SELECT
 employee_id,
 COUNT(*) as count,
 date_trunc('day',punch_date) as day
 FROM bill_item
 WHERE punch_date>= CURRENT_DATE
 GROUP BY employee_id, day 
 HAVING COUNT(*) = 0

Upvotes: 0

Views: 551

Answers (3)

user330315
user330315

Reputation:

You can use a NOT EXISTS condition to check if the to be inserted values already exist:

insert into free_meals_bill (punch_date, employee_id, employee_name, product_name)
select *
from (
   values (date '2021-02-22 10:15:50.086471',123456,'john','Variety Rice - Curd - Rs.35')
) as t(punch_date, employee_id, employee_name, product_name
where not exists (SELECT *
                  FROM free_meals_bill bi
                  WHERE bi.punch_date::date = t.punch_date::date
                    AND bi.employee_id = t.employee_id)

But if you only allow one row per (employee_id, punch_date) you should create a unique constraint or index

create unique index only_one_meal_per_day
  on free_meals_bills ( (punch_date::date), employee_id);

Then you can do:

insert into free_meals_bill (punch_date, employee_id, employee_name, product_name)
values (date '2021-02-22 10:15:50.086471',123456,'john','Variety Rice - Curd - Rs.35')
on conflict ((punch_date::date), employee_id)
do nothing;

Upvotes: 1

S-Man
S-Man

Reputation: 23666

demo:db<>fiddle

  1. You cannot use VALUES and SELECT in one INSERT statement. The SELECT statement replaces the VALUES part

  2. You can use EXISTS to check for occurrences.

    INSERT INTO free_meals_bill (punch_date,employee_id,employee_name,product_name)
SELECT
    *
FROM bill_item
WHERE punch_date >= CURRENT_DATE
    AND NOT EXISTS (
       SELECT 1 FROM free_meals_bill WHERE employee_id = bill_item.employee_id
    );

Note: I used * selector here, because in my example bill_item has the same columns as free_meals_bill. You have to adapt this to your real used case, of course, if it doesn't fit to something like this (depending on how bill_item actually looks like):

SELECT
    punch_date,
    employee_id,
    employee_name,
    product_name
...

Edit:

To avoid such duplication by table design you should think about adding a UNIQUE contraint to your table:

ALTER TABLE free_meals_bill ADD CONSTRAINT my_unique_constraint UNIQUE (employee_id);

This prevents INSERT statements from inserting duplicate records automatically, you don't need to do this with SELECT statements

Upvotes: 0

rdr20
rdr20

Reputation: 196

Your select statement has wrong column sequence, it should be the same sequence with your insert statement. date,id,name,product. And, should be the same number of columns too.

Upvotes: 0

Related Questions