How to delete duplicate rows using the Length function in Postgres SQL 9.6

Is there a unique identifier for each row in the table? e.g. "ID"

If not, the problem you will face is that doc2 might be equal in size in some instances and when this happens it will be impossible to match to a single row unless there is an ID field (or equivalent). The following will work on your sample data

CREATE TABLE mytable(
   step_id  INTEGER  NOT NULL
  ,doc2     VARCHAR(11) NOT NULL
  ,doc2size INTEGER  NOT NULL
);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (1,'same size',24);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (1,'same size',24);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (10580774,'318',318);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (10580774,'369',369);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (10580775,'318',318);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (10580775,'379',379);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (10580776,'318',318);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (10580776,'373',373);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (10580777,'318',318);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (10580777,'370',370);

with CTE as (
select
*
, row_number() over(partition by step_id order by doc2size DESC) as rn
from mytable
)
delete
from mytable t
where exists (
     select null
     from CTE 
     where t.step_id = cte.step_id
     and t.doc2 = cte.doc2
     and CTE.rn > 1)

Select allOpen in new window

BUT it will delete BOTH highlighted rows that I added
The result is:

+----------+------+----------+
| step_id  | doc2 | doc2size |
+----------+------+----------+
| 10580774 |  369 |      369 |
| 10580775 |  379 |      379 |
| 10580776 |  373 |      373 |
| 10580777 |  370 |      370 |
+----------+------+----------+

Select allOpen in new window

ALSO you have the added problem that the doc2size isn't part of the persistent data, and I have assumed that I can usean equality predicate on the binary data which may be untrue.

The problem would be much easier, and more accurate, if every row had a unique identifier.

see: https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=aed0be67b99da1645ae8420bb11016a4
 

Unfortunately there is no unique identifier right now. That field was supposed to be step_id BUT due to some unknown database error duplicate entries were created for step_id although it was marked as a unique field. All together there are 1539 rows that are duplicates and need to go so I can reset the step_id field to be unique.

Also unfortunately there are duplicate Step_ID & Doc2 duplicate rows fields with equal value length values so I need to retain one of the two.
 
Is there anyway to create a new, temporary column and use something like ROW_NUMBER() to create a unique ID in that new filed, use that ID to clean up the data then drop that column after the cleanup?

The overall project is that I need to restore this (very large) Postgres data onto a new server (using backup and restore) and so I must do a process to temporarily remove the unique constraint on this table so I can do the restore without errors, then clean these duplicate records through one or more queries and then restore the constraint and put the database into production to complete the migration.

You can add the needed column by either of these

ALTER TABLE mytable ADD COLUMN id SERIAL PRIMARY KEY

Select allOpen in new window

or

ALTER TABLE mytable ADD COLUMN id SERIAL

Select allOpen in new window

see it running here
https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=30cf83e419d928dd616f34c8a99e3adb

Once you have cleaned-up the data then you can remove that column if needed.

A huge thanks for the link to dbfiddle! I was looking for a sandbox to test queries on and couldn't find one before :)

Let me play with the query you built for a bit and see if I can swap out the doc2size as a field with length(doc2) instead

You can include the length(doc2)  into the calculation of row numbers, like this:

with CTE as (
select
*
, row_number() over(partition by step_id order by length(doc2) DESC) as rn
from mytable
)
delete
from mytable t
where exists (select NULL
              from CTE
              where t.id = CTE.id and CTE.rn > 1

Select allOpen in new window

Just to end the documentation chain, here is the full steps combined:
https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=afdff883129cde72f77eb16eb8218f88

CREATE TABLE mytable(
   step_id  INTEGER  NOT NULL, doc2     VARCHAR(11) NOT NULL
);
INSERT INTO mytable(step_id,doc2) VALUES (1,'same size');
INSERT INTO mytable(step_id,doc2) VALUES (1,'same size');
INSERT INTO mytable(step_id,doc2) VALUES (10580774,'XXXX');
INSERT INTO mytable(step_id,doc2) VALUES (10580774,'XXXXXX');
INSERT INTO mytable(step_id,doc2) VALUES (10580775,'XX');
INSERT INTO mytable(step_id,doc2) VALUES (10580775,'XXXX');
INSERT INTO mytable(step_id,doc2) VALUES (10580776,'XXXXX');
INSERT INTO mytable(step_id,doc2) VALUES (10580776,'XX');
INSERT INTO mytable(step_id,doc2) VALUES (10580777,'X');
INSERT INTO mytable(step_id,doc2) VALUES (10580777,'XXXX');

Select allOpen in new window

ALTER TABLE mytable ADD COLUMN tempid SERIAL

Select allOpen in new window

/* ACTUALLY DELETE THE ROWS */
with CTE as (
select
*
, row_number() over(partition by step_id order by length(doc2) DESC) as rn
from mytable
)
delete
from mytable t
where exists (select NULL
              from CTE
              where t.tempid = CTE.tempid and CTE.rn > 1)

Select allOpen in new window

ALTER TABLE mytable DROP COLUMN tempid

Select allOpen in new window

select *, length(doc2)from mytable            

Select allOpen in new window

FYSA:
You can do this w/o the tempid.

/* ACTUALLY DELETE THE ROWS */
with CTE as (
select
ctid
, row_number() over(partition by step_id order by length(doc2) DESC) as rn
from mytable
)
delete
from mytable t
where ctid in (select ctid
              from CTE
              where rn > 1)

Select allOpen in new window