postgresql - 从 postgresql 中的选择不同查询插入到表中

Question

我有一个包含 33 列的表，其中有多个重复项，所以我试图通过这种方式删除所有重复项，因为这个选择不同的查询具有正确数量的数据。

    CREATE TABLE students (
school char(2),sex char(1),age int,address char(1),famsize char(3),
Pstatus char(1),Medu int,Fedu int,Mjob varchar,Fjob varchar,reason varchar,
guardian varchar,traveltime int,studytime int,failures char(1),
schoolsup varchar,famsup varchar,paid varchar,activities varchar,
nursery varchar,higher varchar,internet varchar,romantic varchar,
famrel int,freetime int,goout int,Dalc int,Walc int,
health int,absences int,id serial primary key)

我想插入来自这个选择不同查询的所有值将 8 列放入不同的空表中。

SELECT DISTINCT ("school","sex","age","address","famsize","Pstatus","Medu","Fedu","Mjob","Fjob","reason","nursery","internet")
FROM students;

Answer 1

I want to insert all values from this select distinct query with 8 columns into a different empty table.

如果要创建表，请使用create table .. as select ...

create table new_table
as
SELECT DISTINCT school, sex, age, address, famsize, "Pstatus", "Medu", "Fedu", "Mjob", "Fjob", reason, nursery, internet
FROM students;

否则只需使用基于选择的插入:

insert into empty_table (school, sex, age, address, famsize, "Pstatus", "Medu", "Fedu", "Mjob", "Fjob", reason, nursery, internet)
SELECT DISTINCT school, sex, age, address, famsize, "Pstatus", "Medu", "Fedu", "Mjob", "Fjob", reason, nursery, internet
FROM students;

非常重要:不要在选择列表中的列周围放置括号 - 这会创建一个具有匿名记录类型的单个列。