Postgres Remove records by duplicate control_id

Asked Dec 14 '15 at 19:07

Active Dec 14 '15 at 19:09

Viewed 29 times

I'll preface with the join's and examples that were similar found, I could not get to work and i appreciate any help. I have postgres 9.X table with a few columns: datetime|control_id|content|

How do I remove additional records that have the same control_id? I do not care about which record's datetime and content is kept as the content will be duplicate, but the datetime is unique (it does not matter which datetime is kept). eg

Input may look like:

01012015|333|blahblah
01042014|333|blahblah
02032015|333|blahblah
10102014|388|bleehbleeh
11112014|388|bleehbleeh

Output I want:

01012015|333|blahblah
10102014|388|bleehbleeh

Any date can be in the output as long as the control_id column only exists once. A select distinct didn't work for me as the datetime column is unique.

edited Dec 14 '15 at 19:09

asked Dec 14 '15 at 19:07

bbrown

Do you want to delete those rows _permanently_ or just filter them out in a SELECT query? – Dec 14 '15 at 19:10
http://stackoverflow.com/questions/6583916/delete-completely-duplicate-rows-in-postgresql-and-keep-only-1 – Dec 14 '15 at 19:13
They can be deleted. – bbrown Dec 14 '15 at 19:14
Great, thanks! I did not realize ctid was for that or that it existed. Worked like a charm. – bbrown Dec 14 '15 at 19:26

Postgres Remove records by duplicate control_id

0 Answers0