How to insert into a table?

Question

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

Wouldn't this be better solved by adding a unique constraint on the date (without time) and employee_id? — Mark Rotteveel, Feb 22 '21 at 08:07
not just solved, IMO if you want something unique, you should always use unique constraint, it block every potential miss fire and any bug (which will always happen no matter how careful both user and engineer are) that might lead to unwanted duplicate. — T. Peter, Feb 22 '21 at 08:12
I don't understand why your `select` retrieves rows from `bill_item` if you want to check if the data to be inserted already exists in `free_meals_bill`? — , Feb 22 '21 at 09:08

score 1 · Answer 1 · 2021-02-22T09:13:08.563

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;

score 0 · Answer 2 · answered Feb 22 '21 at 07:56

0

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.

answered Feb 22 '21 at 07:56

rdr20

186
7

S-Man · Answer 3 · 2021-02-22T08:58:13.067

0

demo:db<>fiddle

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

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

edited Feb 22 '21 at 08:58

answered Feb 22 '21 at 08:00

S-Man

22,521
7
40
63

i dont know how to insert could you please guide me – pythoncoder Feb 22 '21 at 08:43
What exact is your problem? Did you see the fiddle link? There you can see the demo... – S-Man Feb 22 '21 at 08:50
I want to check before insert, if exists means dont want to insert else insert – pythoncoder Feb 22 '21 at 08:56
Once more: Did you look at the fiddle: I shows: First run inserted the records, because nothing was in there. When inserting the second time, this results in nothing: No second entry was added. So: EXISTS checks if there is already a fitting record in the table, if not the record is inserted. – S-Man Feb 22 '21 at 09:00
I added another way, which should be taken to increase the consistency of your database: Using a UNIQUE constraint on the table instead using a SELECT statement... – S-Man Feb 22 '21 at 09:01
@pythoncoder Did this help? I'd appreciate if you could upvote the answer since it pointed out two possible solutions. Please don't forget to UPVOTE every answer which was helpful in any way (pointing out a good idea, shows a way to a possible solution, ...). This honors the time and effort the repliers invested into your problem and motivates them to do so. If one answer completely solves your problem, please ACCEPT it additionally to close the question :) – S-Man Feb 23 '21 at 07:36

How to insert into a table?

3 Answers3