Remove rows in table in Postgresql

Avatar
Permanently deleted user
Created

Sometimes I need to delete identical rows in any table in Postgresql database. When I try it using recordset editor I get error because of duplicates:

"Unexpected update count received (Actual: 2, Expected: 1). Changes will be rolled back. SQL: {SQL STATEMENT}"

I believe It'd better to use CTID identifier to remove exactly one record

 

Votes

0

Share

3 comments
Avatar
vasily chernov
Created

Hi,

Could you provide sample DDL of such kind of tables?

Thank you.

0
Avatar
Permanently deleted user
Created 
--create sample table
drop table if exists ctid_test;

create table ctid_test(
  i int,
  t text,
  v varchar(100)
);

--populate with dummy values including doubles
insert into ctid_test(i,t,v) values
  (1, 'text1', 'text1'),
  (1, 'text1', 'text1'),
  (2, 'text1', 'text1'),
  (2, 'text1', 'text2');


--this select shows uniqueness of ctid
select
 ctid,
  i,
  t,
  v
from ctid_test;

--this statement shows one record only because it's using ctid filter also
select * from ctid_test where i = 1 and t = 'text1' and v = 'text1' and ctid = '(0,1)';

 

0
Avatar
vasily chernov
Created

@Dkuznetsov Hi,

As a workaround, try to drop rows in table editor. If you need to narrow down results you can use filter criteria.

Thank you.

0

Please sign in to leave a comment.