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]+(\,)