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 Comandi utili ;
- Postgresql reset sequence ;
Plpgsql
SQL – PostgreSQL
Creare eliminare database e tabelle
Annunci