3

I have a table stored all my project rss channel url, now I found some url end with '/' but some sub url are not. I my app I have to handle this situation in everywhere. Then I want to store all the sub url link without the last '/', if the url end with '/', I want to delete the end of '/'. I have write the update sql command like this:

UPDATE rss_sub_source 
SET sub_url = SUBSTRING(sub_url, 1, CHAR_LENGTH(sub_url) - 1) 
WHERE sub_url LIKE '%/';

when I execute the sql:

SQL Error [23505]: ERROR: duplicate key value violates unique constraint "unique_sub_url"
  Detail: Key (sub_url)=(https://physicsworld.com/feed) already exists.

the error shows that some url without '/' have already exists. when I update wht end with '/' url, it will conflict with the exists one because I add an uniq constraint. There table contains thousands of url, update one by one obviously impossible. So I want to ignore and jump to update the url if it did not obey the uniq constraint, only update the success record. Finnaly delete the end with '/' record.

Is it possible to ignore the update error events in PostgreSQL? if not what should I do to make all rss url did not end with '/'?

2
  • 1
    Are you trying to find the duplicates and only update the rest of records?
    – Biju jose
    Commented Jul 22, 2021 at 16:57
  • I have updated the record one by one.@Bijujose if I encount conflicts, I delete the record
    – Dolphin
    Commented Jul 22, 2021 at 17:00

2 Answers 2

3

You can check if such a sub_url already exists:

UPDATE rss_sub_source 
SET sub_url = SUBSTRING(sub_url, 1, CHAR_LENGTH(sub_url) - 1) 
WHERE sub_url LIKE '%/' 
AND NOT EXISTS 
(
  SELECT 1 FROM rss_sub_source 
  WHERE sub_url = SUBSTRING
                  (
                    sub_url, 1, CHAR_LENGTH(sub_url) - 1
                  ) 
)
2

Based on your comment, you have to find the duplicate then delete those records first and then do the update.

create table rss_sub_source (
  id int,
  sub_url varchar(100)
  );
  
  insert into rss_sub_source 
  select 1,'https://www.google.com/'
  union all
  select 2,'https://www.msn.com/'
  union all
  select 3,'https://www.google.com/'
  union all 
  select 4,'https://www.gmail.com'

--find dupicates and delete

delete  FROM rss_sub_source r
using (
select SUBSTRING(sub_url, 1, CHAR_LENGTH(sub_url) - 1),
       row_number() OVER(partition by SUBSTRING(sub_url, 1,CHAR_LENGTH(sub_url) - 1) 
       order by id asc) rnk,
       id
from rss_sub_source
  )  x where x.id=r.id
 and rnk>1;
 
 select * FROM rss_sub_source;
 
 --final update 
 UPDATE rss_sub_source 
SET sub_url = SUBSTRING(sub_url, 1, CHAR_LENGTH(sub_url) - 1) 
WHERE sub_url LIKE '%/';

select * FROM rss_sub_source;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.