Remove rows in table in Postgresql

Permanently deleted user

创建于 2017年05月03日 14:14

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

3 条评论

vasily chernov

创建于 2017年05月20日 19:38

Hi,

Could you provide sample DDL of such kind of tables?

Thank you.

Permanently deleted user

创建于 2017年05月29日 13:27

--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)';

vasily chernov

创建于 2017年05月29日 15:47

@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.

请先登录再写评论。