Sample Header Ad - 728x90

PostgreSQL, INSERT INTO SELECT DISTINCT generates more rows?

0 votes
2 answers
1160 views
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;
Asked by Anvil (11 rep)
Mar 7, 2023, 07:01 PM
Last activity: Mar 8, 2023, 07:29 PM