Posts

Showing posts from October, 2018

Find duplicates in Postgres table and remove them

 To find all duplicates in table: SELECT name, COUNT( name ) FROM client GROUP BY name HAVING COUNT( name )> 1 ORDER BY name; To remove one: DELETE FROM client a USING client b WHERE a.id < b.id AND a.name = b.name;

Notepad++ Tips

Sometimes you need to remove ids from sql dump. For example: INSERT INTO public.dcicv VALUES (4, 'Benzilpenicilina Benzatínica', 'Antibacterianos', 'Penicilinas'); INSERT INTO public.dcicv VALUES (5, 'Procaína', 'Antibacterianos', 'Penicilinas'); INSERT INTO public.dcicv VALUES (6, 'Benzilpenicilina procaínica', 'Anti-infeciosos',  'Penicilinas'); convert to : INSERT INTO public.dcicv VALUES ( 'Benzilpenicilina Benzatínica', 'Antibacterianos', 'Penicilinas'); INSERT INTO public.dcicv VALUES ( 'Procaína', 'Antibacterianos', 'Penicilinas'); INSERT INTO public.dcicv VALUES ( 'Benzilpenicilina procaínica', 'Antibacterianos', 'Penicilinas'); just do replace regular expression: [0-9]+(\,)