Postgresql cancella record duplicati

Esempio di query SQL per cancellare records duplicati in postgresql

DELETE
 FROM downloads
 WHERE itemid NOT IN
 (SELECT MAX(dtupla.itemid)
 FROM downloads As dtupla
 GROUP BY dtupla.email,  dtupla. filename,  dtupla.date_part("hour", createddate)

Alternativa per la cancellazione di record duplicati limitando la ricerca ai records
con count (*)   > 1

DELETE
 FROM downloads
 WHERE itemid NOT IN
 (SELECT MIN(dtpl.itemid)
 FROM downloads As dtpl
 GROUP BY dtpl.email, dtpl.filename, date_trunc('minute',dtpl.createddate)
 HAVING count(*) >= 1);
 SELECT 1;

Conta record che hanno alcuni campi chiave duplicati e che pertatno potrebbero essere record duplicati

SELECT *
 FROM ( SELECT email, date_trunc('hour',createddate), filename, count (filename) as totuples
 FROM contactsdownloads
 GROUP BY email, filename , date_trunc('hour',createddate)
 ORDER BY totuples DESC, date_trunc('hour',createddate) ) AS foo
 WHERE totuples > 1

Risorse:

Postgresql 

• postgresql check if table exist ;
• How escape single quote ‘ in postgresql  ;
• SQL Concatenare due o più campi – String Concatenation ;
• Postgresql cancella record duplicati ;
• postgresql Elencare i campi di una tabella ;
• PostgreSQL CREATE TEMPORARY TABLE ;
• Postgresql show tables, views, index, sequences ;
• Postresql varie ;
• pg_class to check if table exists ;
• Postgresql Comandi utili ;
• Postgresql reset sequence ;

Plpgsql 

• plpgsql how to loop record
• PostgreSQL SQL use CASE when … then … else ..END in SELECT
• plpgsql functions nexted cycles FOR LOOP multiple IF THEN
• Postgresql plpgsql function example IF … THEN … ELSE … END IF
• plpgsql Postgres FOR LOOP END LOOP

SQL  – PostgreSQL

• SQL Joincast su ;
• SQL Cast ;
• SQL Union Query ;
• SQL Count ;
• SQL Delete ;
• Postgresql : SQL UPDATE query ;
• postgresql : SQL SELECT query ;
• postgresql : Eseguire una SQL UPDATE query di test ;
• PostgreSQL Total, Subtotal an alternative ;

• PostgreSQL | maurizio siagri ;
• MySQL | maurizio siagri  ;

• PostgreSQL – novice – Check if table exists | Threaded View
• PostgreSQL blog resourses

Creare eliminare database e tabelle

Annunci

• delete
• duplicati
• sql