PostgreSQL数仓插入OLAP事实表报重复键违反唯一约束错误

环境与操作说明 #

搭建数据仓库过程中，public 业务库下存在customer、product、addressee、orders 4张业务表，在olap分析库下按星型模型创建维度表与事实表，建表语句如下：

CREATE TABLE olap.time 
(
        idtime SERIAL NOT NULL PRIMARY KEY,
        year integer,
        month integer,
        week integer,
        day integer
);

CREATE TABLE olap.addressees 
(
        idaddressee integer PRIMARY KEY NOT NULL,
        name varchar(40) NOT NULL,
        zip char(6) NOT NULL,
        address varchar(60) NOT NULL
);

CREATE TABLE olap.customers 
(
        idcustomer  varchar(10) PRIMARY KEY ,
        name varchar(40) NOT NULL,
        city varchar(40) NOT NULL,
        zip char(6) NOT NULL,
        address varchar(40) NOT NULL,
        email varchar(40),
        phone varchar(16) NOT NULL,
        regon char(9)
);

CREATE TABLE olap.fact
(
        idtime integer NOT NULL,
        idaddressee integer NOT NULL,
        idcustomer varchar(10) NOT NULL,
        idfact integer NOT NULL,
        price numeric(7,2),
        PRIMARY KEY (idtime, idaddressee, idcustomer),
        FOREIGN KEY (idaddressee) REFERENCES olap.addressees(idaddressee),
        FOREIGN KEY (idcustomer) REFERENCES olap.customers(idcustomer),
        FOREIGN KEY (idtime) REFERENCES olap.time(idtime)
);

建表完成后，执行以下SQL完成三个维度表的数据导入：

INSERT INTO olap.time (year, month, week, day)
    SELECT date_part('year', date), date_part('month', date), date_part('week', date), date_part('day', date) 
    FROM public.orders 
    GROUP BY public.orders.date
    ORDER BY public.orders.date;

INSERT INTO olap.addressees(idaddressee, name, zip, address)
    SELECT idaddressee, name, zip, address 
    FROM public.addressee;

INSERT INTO olap.customers (idcustomer, name, city, zip, address, email, phone, regon)
    SELECT idcustomer, name, city, zip, address, email, phone, regon 
    FROM public.customer;

维度数据导入完成后，执行以下SQL向事实表插入聚合后的业务数据：

INSERT INTO olap.fact (idtime, idaddressee, idcustomer, idfact, price)
    SELECT olap.time.idtime, olap.addressees.idaddressee, olap.customers.idcustomer, COUNT(*), public.orders.price
    FROM (((public.orders
    INNER JOIN olap.time ON (date_part('year', public.orders.date) = olap.time.year AND date_part('month', public.orders.date) = olap.time.month AND date_part('week', public.orders.date) = olap.time.week) AND date_part('day', public.orders.date) = olap.time.day)
    INNER JOIN olap.addressees ON public.orders.idaddressee = olap.addressees.idaddressee)
    INNER JOIN olap.customers ON public.orders.idcustomer = olap.customers.idcustomer)
    GROUP BY olap.time.idtime, olap.addressees.idaddressee, olap.customers.idcustomer, public.orders.price;

执行上述插入语句时，数据库返回报错：

ERROR: duplicate key value violates unique constraint

（注：粘贴的报错信息存在串行，标注的"syntax error"不属于该错误的实际类型，该错误为约束冲突类错误）

问题成因 #

• 事实表olap.fact定义的联合主键为(idtime, idaddressee, idcustomer)，要求同一时间、同一收件人、同一客户的维度组合在表中唯一存在。
• 插入语句的GROUP BY逻辑错误：分组字段额外加入了public.orders.price，如果同一个维度组合下存在多笔不同价格的订单，分组后会生成多条主键完全相同的记录，插入时触发主键唯一约束，抛出重复键错误。
• 指标字段逻辑不符合数仓设计要求：语句中直接取原始表的public.orders.price作为事实表的价格指标，没有做聚合计算，和维度聚合的逻辑矛盾；idfact字段用COUNT(*)赋值也没有实际业务意义，容易引发数值冲突。

解决方法 #

• 调整事实表字段设计：如果idfact需要作为事实表的独立单主键，直接将其修改为SERIAL自增类型，插入数据时不需要手动赋值；如果保留现有三字段联合主键，可以直接删除idfact冗余字段。
• 修正聚合分组逻辑：GROUP BY仅保留三个维度关联字段，所有指标字段必须使用聚合函数计算（比如用SUM(price)统计维度组合下的订单总金额，可根据业务需求替换为AVG、COUNT等聚合函数），不能直接取原始订单字段。
• 修正后的参考SQL如下：

-- 第一步：修改idfact为自增字段（如果不需要该字段可跳过此步直接删除字段）
CREATE SEQUENCE IF NOT EXISTS olap.fact_idfact_seq OWNED BY olap.fact.idfact;
ALTER TABLE olap.fact ALTER COLUMN idfact SET DEFAULT nextval('olap.fact_idfact_seq'::regclass);

-- 第二步：插入聚合后的事实数据，不需要手动插入idfact
INSERT INTO olap.fact (idtime, idaddressee, idcustomer, price)
SELECT 
    olap.time.idtime, 
    olap.addressees.idaddressee, 
    olap.customers.idcustomer, 
    SUM(public.orders.price) AS total_price
FROM public.orders
INNER JOIN olap.time 
    ON date_part('year', public.orders.date) = olap.time.year 
    AND date_part('month', public.orders.date) = olap.time.month 
    AND date_part('week', public.orders.date) = olap.time.week 
    AND date_part('day', public.orders.date) = olap.time.day
INNER JOIN olap.addressees 
    ON public.orders.idaddressee = olap.addressees.idaddressee
INNER JOIN olap.customers 
    ON public.orders.idcustomer = olap.customers.idcustomer
GROUP BY olap.time.idtime, olap.addressees.idaddressee, olap.customers.idcustomer;

内容的提问来源于stack exchange，提问作者berkay aksoy