时间戳

首页 > 解决方案 > postgresql 插入更新

sql - postgresql 插入更新

问题描述

INSERT INTO analytic_filter_log_device(guest_device_id,query_filter_id,created_time) VALUES (UPDATE guest_device gd
    SET status = false
    FROM(
        SELECT
        gl.guest_device_id AS device_id
        FROM guest_login gl
        JOIN guest_device gd ON gd.id = gl.guest_device_id AND gd.status = TRUE
        JOIN guest_login_ap gla ON gla.guest_login_id = gl.id
        JOIN config_ap ca ON ca.id = gla.ap_id AND ca.company_id = 1
        WHERE ('2018-06-26 00:00:00' IS NULL OR '2018-07-26 23:59:59' IS NULL OR (gla.start_time, gla.end_time) OVERLAPS ('2018-06-26 00:00:00', '2018-07-26 23:59:59')) AND
        get_duration_in_interval(gla.start_time, gla.duration::bigint, '2018-06-26 00:00:00', '2018-07-26 23:59:59') >= 5
        GROUP BY gl.guest_device_id
        HAVING COUNT(DISTINCT gl.id) >= 5
    ) as t1
    WHERE gd.id = t1.device_id
    RETURNING 1,1,now());

标签： sqlpostgresqlsql-insert

解决方案

您可以使用这样的select内部values子句。您可以使用指定常量，insert into .. values (1,2,3)也可以直接使用选择查询作为插入源：insert into ... select ... from

您可以通过两种方式重写查询：

您需要使用数据修改 CTE

with updated (guest_device_id) as (
  UPDATE guest_device gd
    SET status = false
  FROM(
        SELECT gl.guest_device_id AS device_id
        FROM guest_login gl
          JOIN guest_device gd ON gd.id = gl.guest_device_id AND gd.status = TRUE
          JOIN guest_login_ap gla ON gla.guest_login_id = gl.id
          JOIN config_ap ca ON ca.id = gla.ap_id AND ca.company_id = 1
        WHERE (gla.start_time, gla.end_time) OVERLAPS ('2018-06-26 00:00:00', '2018-07-26 23:59:59')) 
           AND get_duration_in_interval(gla.start_time, gla.duration::bigint, '2018-06-26 00:00:00', '2018-07-26 23:59:59') >= 5
        GROUP BY gl.guest_device_id
        HAVING COUNT(DISTINCT gl.id) >= 5
  ) as t1
  WHERE gd.id = t1.device_id
  RETURNING gd.id
)
INSERT INTO analytic_filter_log_device(guest_device_id,query_filter_id,created_time) 
select guest_device_id, 1, now()
from updated;

或者update returning直接在select查询中使用：

INSERT INTO analytic_filter_log_device(guest_device_id,query_filter_id,created_time) 
select id, 1, now()
from (
  UPDATE guest_device gd
    SET status = false
  FROM(
        SELECT gl.guest_device_id AS device_id
        FROM guest_login gl
          JOIN guest_device gd ON gd.id = gl.guest_device_id AND gd.status = TRUE
          JOIN guest_login_ap gla ON gla.guest_login_id = gl.id
          JOIN config_ap ca ON ca.id = gla.ap_id AND ca.company_id = 1
        WHERE (gla.start_time, gla.end_time) OVERLAPS ('2018-06-26 00:00:00', '2018-07-26 23:59:59')) 
           AND get_duration_in_interval(gla.start_time, gla.duration::bigint, '2018-06-26 00:00:00', '2018-07-26 23:59:59') >= 5
        GROUP BY gl.guest_device_id
        HAVING COUNT(DISTINCT gl.id) >= 5
  ) as t1
  WHERE gd.id = t1.device_id
  RETURNING gd.id
) t;

请注意，我将硬编码替换为returning 1并returning gd.id删除了无用的'2018-06-26 00:00:00' IS NULL部分（因为这总是正确的）

推荐阅读