在Postgresql中插入具有重复值的数据
在Postgresql中插入具有重复值的数据
提问于 2021-01-09 20:03:59
我需要在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)
)回答 1
Stack Overflow用户
回答已采纳
发布于 2021-01-09 21:10:11
如果不想插入重复项，则添加唯一索引或约束：
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的最后一行。
Here是一个db<>fiddle。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/65642402
复制相关文章
相似问题
腾讯云开发者
