I have a large PostgreSQL table (NOT indexed) with duplicates so I ran the following query to eventually end up with a unique rows table:
INSERT INTO newTable(Field1, Field2, Field3) SELECT DISTINCT Field1, Field2, Field3 FROM oldTable ;
It ran until I ran out of disk space which is more than the size of the oldTable, and I'm positive that at least a 3rd is duplicate.
If anyone would know why it behaves like this. I couldn't run explain analyze because the table is over a Tb in size.
I can't remove duplicates using SELECT with functions min() or max() as I don't have an ID column.
I expected a smaller table with unique rows.
Edit : Does running a slightly different query change the the in-depth mechanism of the process ? I know that the following creates a new table unlike the first query that Inserts into an existing one:
SELECT DISTINCT Field1, Field2, Field3 INTO newTable FROM oldTable;