Remove duplicate rows from postgress using ctid

Today, I was in a situation to delete the duplicate row entries in a table which has all the column values similar including the unique constraints too.

I'm not sure how the table accepted the unique constraints of same value, it's been an large dataset and I guess some could've gone wrong on migration or some thing else.

While trying to reindex the table it failed stating

>>

reindexdb: reindexing of database "testdb" failed: ERROR: could not create unique index "folder_pk"

DETAIL: Table contains duplicated values.

<<


After trying to remove the duplicate values, I found all the columns values are similar including the primary keys,

So finally got an idea from one forum, which suggests to use the "ctid" of the rows to delete that.

It looks, for the table rows, internally there is ctid and you can view it using

select ctid, id from table;

Then, remove the specific rows which you need using the ctid.

Shell script to connect postgress in a loop

i=211
while [ $i -le 250 ]
do
folder="psr-pub-$i"
echo "Starting processing $folder."
./svnmapping.sh $folder
echo "Done with $folder"
i=`expr $i + 1`
done


projectName=$1
userId="user1308"

DBConnect="psql -d psrdb -U psruser --tuples-only --single-line"

date=`date +"%F %T"`
projId=`echo "select id from project where path='projects.$projectName'" | $DBConnect`
projId=`echo $projId`

parent_folder_id=`echo "select id as parent_folderid from folder where path = 'scm' and project_id='$projId'" | $DBConnect`
parent_folder_id=`echo $parent_folder_id`

nextscm_id_query="select max(substring(id from (char_length('reps')+1))) from scm_repository"
primaryId_query="select id from objecttype where application='Scm' and name='Repository'"
secondaryId_query="select id from objecttype where application='Scm' and name='Commit'"

nextscm_id=`echo $nextscm_id_query | $DBConnect`
nextscmId=`echo $nextscm_id`
nextscmId=reps`expr $nextscmId + 1`
primaryId=`echo $primaryId_query | $DBConnect`
primaryId=`echo $primaryId`
secondaryId=`echo $secondaryId_query | $DBConnect`
secondaryId=`echo $secondaryId`

insertscm="insert into scm_repository values('$nextscmId', 'exsy1002', '/svnroot/$projectName', 'f')"
insertfolder="insert into folder values ('$nextscmId', 'scm.$projectName', '$projectName', '$projectName', '100', '$date+05:30', '$date+05:30','f', '$primaryId', '$secondaryId', '$userId', '$userId', '$parent_folder_id', '$projId', '0')"

scm=`echo $insertscm | $DBConnect`
fol=`echo $insertfolder | $DBConnect`

echo $scm" "$fol