PostgreSQL: PSQL Select Distinct Output * To Generate New Table

Earlier this evening I ran into a unique issue where a PostgreSQL table had been created with no primary key this allowing duplicate rows to exist in the database table. You are unable to delete such rows in a SQL table because there is no primary key. After not being able to delete the rows via the PSQL CLI I opened pgAdmin III to see if I could delete the rows using the GUI interface provided by pgAdmin III however the delete was grey in the menu as shown in the below image.

pgAdmin III Delete Grey When No Primary Key Exists For Table:

So after a bit of investigation I was able to figure out that you can actually select all unique rows in the table by issuing the below command via the PSQL command line.

Select All Unique Rows In A PostgreSQL Table With No Primary Key:

dev=# SELECT DISTINCT * FROM devices_users;
device_id | user_id
-----------+---------
242 | 54
250 | 56
158 | 40
64 | 42
166 | 87
12 | 8
(6 rows)
dev=#

As you can see above there are 6 rows displayed which is different than the 16 rows that exist as shown below using the count syntax.

Display Number Of Rows That Exist In A Postgres Table:

dev=# SELECT COUNT(*) FROM devices_users;
count
-------
16
(1 row)
dev=#

Now that we know there are so many duplicate rows we should create a new table with the distinct rows, rename the original table, and then rename the new table to the old tables name. First use the below syntax to generate a new Postgres table using the distinct rows from the original PSQL table.

Create A New Table With Distinct Rows Of The Original Table:

dev=# CREATE TABLE devices_users_new AS SELECT DISTINCT * FROM devices_users;
SELECT
dev=#

After generating the new table you should rename the original table which in this case is devices_users to something like devices_users_old. After moving the old table out of the way rename the new table or devices_users_new to devices_users as shown in the SQL syntax below.

Rename Old PSQL Table:

dev=# ALTER TABLE devices_users RENAME TO devices_users_old;
ALTER TABLE
dev=#

Rename New PSQL Table:

dev=# ALTER TABLE devices_users_new RENAME TO devices_users;
ALTER TABLE
dev=#

Using the above SQL syntax you have now effectively removed all of the duplicates from a PostgreSQL table that does not have a primary key. Please be sure to backup all data before attempting the steps mentioned above.