时间戳

首页 > 解决方案 > 如何插入表格？

sql - 如何插入表格？

问题描述

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

在插入“free_meals_bill”表之前，我想检查每个employee_id 是否只允许打一拳。例如，如果 john（员工 id 123456）已经在 free_meals_bill 中，那么在同一日期，john 数据不应再次插入到“free_meals_bill”表中。

询问：

 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

标签： sqlpostgresqlsql-insertinsert-query

解决方案

您可以使用 NOT EXISTS 条件来检查要插入的值是否已经存在：

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)

但是如果你只允许每行(employee_id, punch_date)你应该创建一个唯一的约束或索引

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

然后你可以这样做：

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;

推荐阅读