sql delete - How to remove duplicate rows in CockroachDB -


i have table in cockroachdb, have populated data table before applying constraints set primary key, , because of insert statement failed through data-loading phase, of rows loaded table more 1 time mistake.

the constraint want apply is:

create unique index on "mydb"."mytable" ("row_id"); 

but duplicate data loaded table, following error:

pq: multiple primary keys table "mytable" not allowed 

i have check see if there duplicated rows following query:

select       row_id,     count(row_id) id         mytable group      row_id having       count(row_id) > 1; 

and query showed there duplicate rows.

what best way remove duplicate rows in cockroachdb?

if don't care which duplicated row keep, run:

delete mytable rowid in (     select min(rowid) mytable group row_id having count(*) > 1 ); 

for duplicates, query delete row created first.†

note rowid not same row_id column. rowid internal cockroachdb column magically created tables no primary key, , guaranteed unique each row in table.

rowid stores <timestamp><node-id>, sorts according insertion time, rows inserted near-simultaneously nodes skewed clocks may not sort in insertion order.


Comments

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

python Tkinter Capturing keyboard events save as one single string -

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -