CODEWITHSUNDEEP
sqlpostgresql
goku
goku

Reputation: 213

adjusting name in postgres when repeated

Hello I need some help in adjusting filenames in a table when they are repeated, the names were part of the url, I want to add a couple of characters in order to have 0 repeated names. I do not want to use a function like UUID to generate new names.

select count(distinct filename) from images;
6730725
select count(distinct url) from images;
10991695
select count(*) from images;
10991695


id,url,filename
1,some_url, test1
2,some_url2, test2
3,some_url3, test1
...

The ideal output would be to add a few random characters where the names are repeated

id,url,filename
1,some_url, test1
2,some_url2, test2
3,some_url3, test1ab

the filenames don't end with an extension (jpg,png,...) so Free to manipulate them.

Upvotes: 0

Views: 56

Answers (2)

Nick
Nick

Reputation: 147216

You can update your table, adding a string to the end of duplicated filenames which represents which duplicate they are. This method ensures you don't accidentally add a duplicate random string to the end of a filename. For example:

WITH cte AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY filename ORDER BY id) AS rn
  FROM images
)
UPDATE images i
SET filename = CONCAT(i.filename, '_', to_char(cte.rn, 'FM00000000'))
FROM cte
WHERE i.id = cte.id AND cte.rn > 1
;
SELECT * FROM images ORDER BY id

Sample output:

id  url         filename
1   some_url    test1
2   some_url2   test2
3   some_url3   test1_00000002
4   some_url4   test3
5   some_url5   test1_00000003

Demo on db-fiddle

If you don't want to update the table, you can implement the same logic in a SELECT:

WITH cte AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY filename ORDER BY id) AS rn
  FROM images
)
SELECT id, url,
       CASE WHEN rn > 1 THEN CONCAT(filename, '_', to_char(rn, 'FM00000000'))
            ELSE filename
       END AS filename
FROM cte
ORDER BY id

The output is the same. Demo on db-fiddle

Upvotes: 1

Popeye
Popeye

Reputation: 35920

You can use the analytical function as follows:

Select id, url, 
       Case when Row_number() over (partition by url order by id) > 1 
            Then concat(filename,'random_srring',Row_number() over (partition by url order by id) )
            Else filename
       End as filename
  From images:

Upvotes: 0

Related Questions