时间戳

首页 > 解决方案 > 将具有重复值的数据插入 Postgresql

sql - 将具有重复值的数据插入 Postgresql

问题描述

我需要在 postgresql 中插入数据集。

INSERT INTO table_subject_topics_exams (name_of_subject, section, topic, subtopic)
VALUES ('Algebra', 'Mathematics', 'Progressions', 'Number Sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Arithmetic Progression'),
    ('Algebra', 'Mathematics', 'Progressions', 'Arithmetic Progression'),
    ('Algebra', 'Mathematics', 'Progressions', 'Arithmetic Progression'),
    ('Algebra', 'Mathematics', 'Progressions', 'Arithmetic Progression'),
    ('Algebra', 'Mathematics', 'Progressions', 'Geometric Progression'),
    ('Algebra', 'Mathematics', 'Progressions', 'Geometric Progression'),
    ('Algebra', 'Mathematics', 'Progressions', 'Geometric Progression'),
    ('Algebra', 'Mathematics', 'Progressions', 'Geometric Progression');

问题是请求中有很多重复的值。由于结果数据库应该涉及

    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Arithmetic Progression'), 
    ('Algebra', 'Mathematics', 'Progressions', 'Geometric Progression');

如果这个数据集在数据库中，或者什么都没有。我应该如何进行查询？

附言

表格是：

CREATE TABLE public.table_subject_topics_exams
(
    ids_of_subject_section integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    name_of_subject character varying(50) COLLATE pg_catalog."default" NOT NULL,
    section character varying(50) COLLATE pg_catalog."default",
    topic character varying(50) COLLATE pg_catalog."default" NOT NULL,
    subtopic character varying(50) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT table_subject_topics_exams_pkey PRIMARY KEY (ids_of_subject_section)
)

标签： sqlpostgresql

解决方案

如果您不想插入重复项，请添加唯一索引或约束：

CREATE UNIQUE INDEX unq_table_subject_topics_exams_3 ON table_subject_topics_exams(name_of_subject, section, topic, subtopic);

如果您希望您insert的非重复值成功，请添加：

ON CONFLICT DO NOTHING

作为INSERT.

这是一个 db<>fiddle。

推荐阅读