Copying data from one table into another postgresql

Question

So I have one table with duplicates of data and I only want to copy the distinct rows into the new table and also give the new table primary key id. This is what I have so far but it doesn't seem to do what I want it to.

INSERT INTO m_new
SELECT * FROM m
WHERE EXISTS (
    SELECT DISTINCT address, city, zip FROM m
)

Sample data and desired results would help. Your query is confusing. What is m? What is mm? — Gordon Linoff
– Gordon Linoff, Commented Oct 18, 2018 at 15:24

Gordon Linoff · Accepted Answer · 2018-10-18 15:23:54Z

2

I'm pretty sure you want distinct on:

INSERT INTO m_new
    SELECT DISTINCT ON (address, city, zip) m.*
    FROM m
    ORDER BY address, city, zip;

DISTINCT ON returns one row for each group of keys.

answered Oct 18, 2018 at 15:23

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Copying data from one table into another postgresql

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related