Insert into table from select distinct query in postgresql

Question

I have a table with 33 columns that has several duplicates so i am trying to remove all the duplicates this way because this select distinct query has the correct number of data.

    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)

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

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

Please edit your question and add the create table statements for the tables in question. Formatted text please, no screen shots — user330315
– user330315, Commented Jan 5, 2017 at 13:55
If i use this i will only get the 8 values and i want all 33 values — KwstasMost
– KwstasMost, Commented Jan 5, 2017 at 13:55
It is not clear what you are trying to achieve. Are you trying to identify all records where a combination of 8 fields are unique? — Dave Poole
– Dave Poole, Commented Jan 5, 2017 at 13:57
Do not put parentheses around the column names in the select list. That creates a single column with an anonymous record structure. — user330315
– user330315, Commented Jan 5, 2017 at 15:35

user330315 · Accepted Answer · 2017-01-05 15:44:03Z

6

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

Use create table .. as select ... if you want to create the table

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

Other wise just use an insert based on a select:

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;

Very important: do not put parentheses around the columns in the select list - that creates a single column with an anonymous record type.

answered Jan 5, 2017 at 15:44

user330315

Sign up to request clarification or add additional context in comments.

Comments

Lajos Arpad · Accepted Answer · 2017-01-05 14:00:09Z

1

insert into destinationTable(dC1, dC2, dC3, dC4, dC5, dC6, dC7, dC8)
select sC1, sC2, sC3, sC4, sC5, sC6, sC7, sC8
from sourceTable

You can join the tables to get the 33 columns.

answered Jan 5, 2017 at 14:00

Lajos Arpad

80.4k42 gold badges122 silver badges234 bronze badges

Collectives™ on Stack Overflow

Insert into table from select distinct query in postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related