Come posso eliminare un numero fisso di righe con l’ordinamento in PostgreSQL?

Sto cercando di trasferire alcune vecchie query MySQL su PostgreSQL, ma ho problemi con questo:

DELETE FROM logtable ORDER BY timestamp LIMIT 10; 

PostgreSQL non consente l’ordinamento o i limiti nella sua syntax di eliminazione e la tabella non ha una chiave primaria, quindi non posso usare una sottoquery. Inoltre, voglio mantenere il comportamento in cui la query elimina esattamente il numero o i record specificati, ad esempio, se la tabella contiene 30 righe ma hanno tutti lo stesso timestamp, voglio comunque eliminare 10, sebbene non sia importante quale 10.

Così; come faccio a cancellare un numero fisso di righe con l’ordinamento in PostgreSQL?

Modifica: nessuna chiave primaria significa che non esiste log_id colonna log_id o simile. Ah, le gioie dei sistemi legacy!

Potresti provare a usare il ctid :

 DELETE FROM logtable WHERE ctid IN ( SELECT ctid FROM logtable ORDER BY timestamp LIMIT 10 ) 

Il ctid è:

La posizione fisica della versione di riga all’interno della sua tabella. Si noti che sebbene il ctid possa essere utilizzato per localizzare la versione della riga molto rapidamente, il ctid una riga cambierà se viene aggiornato o spostato da VACUUM FULL . Quindi ctid è inutile come identificatore di riga a lungo termine.

C’è anche oid ma questo esiste solo se lo chiedi espressamente quando crei il tavolo.

I documenti Postgres consigliano di utilizzare array anziché IN e sottoquery. Questo dovrebbe funzionare molto più velocemente

 DELETE FROM logtable WHERE id = any (array(SELECT id FROM logtable ORDER BY timestamp LIMIT 10)); 

Questo e altri trucchi possono essere trovati qui

 delete from logtable where log_id in ( select log_id from logtable order by timestamp limit 10); 

Supponendo che tu voglia cancellare TUTTI i 10 record (senza l’ordine) potresti fare questo:

 DELETE FROM logtable as t1 WHERE t1.ctid < (select t2.ctid from logtable as t2 where (Select count(*) from logtable t3 where t3.ctid < t2.ctid ) = 10 LIMIT 1); 

Per il mio caso d'uso, eliminando i record 10M, questo si è rivelato più veloce.

È ansible scrivere una procedura che esegue il loop sull’eliminazione per le singole righe, la procedura potrebbe richiedere un parametro per specificare il numero di elementi che si desidera eliminare. Ma questo è un po ‘eccessivo rispetto a MySQL.

Se non si dispone di una chiave primaria, è ansible utilizzare l’array Dove syntax IN con una chiave composita.

 delete from table1 where (schema,id,lac,cid) in (select schema,id,lac,cid from table1 where lac = 0 limit 1000); 

Questo ha funzionato per me.