时间戳

首页 > 解决方案 > 当 COALESCE() 应确保不为空时，为什么会违反此 NOT NULL 约束？

postgresql - 当 COALESCE() 应确保不为空时，为什么会违反此 NOT NULL 约束？

问题描述

我有一个大约 20 行的 PostgreSQL 表。我正在尝试从另一个模式中的类似表中将数据导入其中。源表具有空值字段。目标表不允许其中一些为空值。我正在使用 COALESCE 将任何空值替换为默认值。但是，当我尝试导入一行时，我得到一个 NOT NULL 约束违规。

如果我删除表中除主键和不可为空的列之外的所有列，则查询有效！

这是插入查询：

insert into alarm_history_minimum
(alarm_history_pkey, area)
select alarm_pkey,coalesce(area, 'All'::character varying)
from old_public.alarm_history
where alarm_pkey = 82234;

这是最小化的表：

CREATE TABLE public.alarm_history_minimum
(
    alarm_history_pkey bigint NOT NULL,
    area character varying(32) COLLATE pg_catalog."default" NOT NULL DEFAULT 'All'::character varying
)

这是完整的表格：

CREATE TABLE public.alarm_history
(
    alarm_history_pkey bigint NOT NULL,
    area character varying(32) COLLATE pg_catalog."default" NOT NULL DEFAULT 'All'::character varying,
    device character varying(32) COLLATE pg_catalog."default",
    alarm_number smallint NOT NULL DEFAULT 0,
    tag character varying(32) COLLATE pg_catalog."default",
    state smallint NOT NULL,
    message text COLLATE pg_catalog."default",
    alarm_date timestamp with time zone,
    charge integer,
    ack_date timestamp with time zone,
    ack_state integer NOT NULL DEFAULT 0,
    furnace character varying(32) COLLATE pg_catalog."default",
    cooling_cover character varying(32) COLLATE pg_catalog."default",
    heating integer,
    base character varying(32) COLLATE pg_catalog."default",
    CONSTRAINT alarm_history_pk PRIMARY KEY (alarm_history_pkey),
    CONSTRAINT alarm_history_base_fk FOREIGN KEY (area, base)
        REFERENCES public.bases (area, base) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT alarm_history_charge_fk FOREIGN KEY (charge)
        REFERENCES public.charges (charge) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT alarm_history_cooler_fk FOREIGN KEY (cooling_cover, area)
        REFERENCES public.cooling_covers (cover, area) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT alarm_history_device_fk FOREIGN KEY (device)
        REFERENCES public.bases (alarm_device) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT alarm_history_furnace_fk FOREIGN KEY (furnace, area)
        REFERENCES public.furnaces (furnace, area) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
)

标签： postgresql

解决方案

我忘记了我的 alarm_history 表有一个触发器。空值来自由该触发器更新的表。在这次转换工作中，那个触发器曾经咬过我，我忘记了。

感谢那些发布问题和评论的人，并为我自己的白痴弄乱了列表而道歉。再次。

推荐阅读