Postgres 13

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 employee (
    id int,
    firstname text, 
    lastname text, 
    country text
);

INSERT INTO employee VALUES
(1, 'Raj','Gupta','India'),
(2, 'Raj','Gupta','India'),
(3, 'Mohan','Kumar','USA'),
(4, 'James','Barry','UK'),
(5, 'James','Barry','UK'),
(6, 'James','Barry','UK');

SELECT * FROM employee

CREATE TABLE



INSERT 0 6




id
firstname
lastname
country


1
Raj
Gupta
India

2
Raj
Gupta
India

3
Mohan
Kumar
USA

4
James
Barry
UK

5
James
Barry
UK

6
James
Barry
UK



SELECT 6


SELECT
    id
FROM (
    SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY firstname, lastname, country) row_num
    FROM 
        employee
) s
WHERE row_num > 1


id


5

6

2



SELECT 3


DELETE FROM employee
WHERE id IN (
    SELECT
        id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY firstname, lastname, country) row_num
        FROM 
            employee
    ) s
    WHERE row_num > 1
)

DELETE 3


SELECT * FROM employee


id
firstname
lastname
country


1
Raj
Gupta
India

3
Mohan
Kumar
USA

4
James
Barry
UK



SELECT 3

id	firstname	lastname	country
1	Raj	Gupta	India
2	Raj	Gupta	India
3	Mohan	Kumar	USA
4	James	Barry	UK
5	James	Barry	UK
6	James	Barry	UK