By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table source (
id int not null,
flag char(1),
Data varchar(30) default '151234567890',
amt varchar(14)
);
insert into source (id,amt) values
(1,100), (1,200), (2,100), (2,200), (2,300),
(3,100), (3,200), (4,100), (4,200), (4,300);
10 rows affected
select * from source;
| id | flag | data | amt |
|---|---|---|---|
| 1 | null | 151234567890 | 100 |
| 1 | null | 151234567890 | 200 |
| 2 | null | 151234567890 | 100 |
| 2 | null | 151234567890 | 200 |
| 2 | null | 151234567890 | 300 |
| 3 | null | 151234567890 | 100 |
| 3 | null | 151234567890 | 200 |
| 4 | null | 151234567890 | 100 |
| 4 | null | 151234567890 | 200 |
| 4 | null | 151234567890 | 300 |
UPDATE source tgt
SET flag = 'Z'
FROM source src
WHERE src.Data = '151234567890'
AND tgt.Data = src.Data
AND tgt.id = src.id
AND tgt.ctid > src.ctid
AND tgt.flag IS NULL;
6 rows affected
select * from source;
| id | flag | data | amt |
|---|---|---|---|
| 1 | null | 151234567890 | 100 |
| 2 | null | 151234567890 | 100 |
| 3 | null | 151234567890 | 100 |
| 4 | null | 151234567890 | 100 |
| 1 | Z | 151234567890 | 200 |
| 2 | Z | 151234567890 | 200 |
| 2 | Z | 151234567890 | 300 |
| 3 | Z | 151234567890 | 200 |
| 4 | Z | 151234567890 | 200 |
| 4 | Z | 151234567890 | 300 |
-- resetting flags
update source set flag = null;
10 rows affected
WITH CTE AS (
SELECT ctid, id, Data, Amt
, ROW_NUMBER() OVER (PARTITION BY Data, id ORDER BY Amt) AS rn
FROM source
WHERE Data= '151234567890'
AND flag IS NULL
)
UPDATE source t
SET flag = 'Z'
FROM CTE c
WHERE c.rn > 1
AND t.id = c.id
AND t.Data = c.Data
AND t.ctid = c.ctid;
6 rows affected
select * from source;
| id | flag | data | amt |
|---|---|---|---|
| 1 | null | 151234567890 | 100 |
| 2 | null | 151234567890 | 100 |
| 3 | null | 151234567890 | 100 |
| 4 | null | 151234567890 | 100 |
| 1 | Z | 151234567890 | 200 |
| 2 | Z | 151234567890 | 200 |
| 2 | Z | 151234567890 | 300 |
| 3 | Z | 151234567890 | 200 |
| 4 | Z | 151234567890 | 200 |
| 4 | Z | 151234567890 | 300 |
-- resetting flags
update source set flag = null;
10 rows affected
WITH TAB AS
(
SELECT id,flag
FROM source
where flag IS NULL
and Data= '151234567890'
ORDER BY 1
OFFSET (SELECT COUNT(*) FROM source where flag IS NULL and Data = '151234567890') - 6
FETCH FIRST 6 ROWS ONLY
)
UPDATE source
SET flag = 'Z'
FROM TAB
WHERE source.id = TAB.id;
8 rows affected
select * from source;
| id | flag | data | amt |
|---|---|---|---|
| 1 | null | 151234567890 | 100 |
| 1 | null | 151234567890 | 200 |
| 2 | Z | 151234567890 | 100 |
| 3 | Z | 151234567890 | 100 |
| 4 | Z | 151234567890 | 100 |
| 2 | Z | 151234567890 | 200 |
| 2 | Z | 151234567890 | 300 |
| 3 | Z | 151234567890 | 200 |
| 4 | Z | 151234567890 | 200 |
| 4 | Z | 151234567890 | 300 |
-- resetting flags
update source set flag = null;
10 rows affected
WITH CTE AS (
SELECT id
FROM source
WHERE Data= '151234567890'
AND flag IS NULL
ORDER BY id DESC
FETCH FIRST 6 ROWS ONLY
)
UPDATE source t
SET flag = 'Z'
FROM CTE c
WHERE t.id = c.id;
8 rows affected
select * from source;
| id | flag | data | amt |
|---|---|---|---|
| 1 | null | 151234567890 | 100 |
| 1 | null | 151234567890 | 200 |
| 2 | Z | 151234567890 | 100 |
| 3 | Z | 151234567890 | 100 |
| 4 | Z | 151234567890 | 100 |
| 2 | Z | 151234567890 | 200 |
| 2 | Z | 151234567890 | 300 |
| 3 | Z | 151234567890 | 200 |
| 4 | Z | 151234567890 | 200 |
| 4 | Z | 151234567890 | 300 |