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 my_table (
the_visitor_id varchar(5) NOT NULL,
the_visitor_visit timestamp NOT NULL,
the_visitor_returning text
);
INSERT INTO my_table
VALUES ('VIS01', '2019-05-02 09:00:00','YES' ),
('VIS01', '2019-05-04 12:00:00',NULL ),
('VIS01', '2019-05-05 18:00:00',NULL ),
('VIS02', '2019-05-06 18:30:00',NULL),
('VIS02', '2019-05-15 12:00:00',NULL),
('VIS03', '2019-06-30 18:00:00','YES'),
('VIS04', '2019-06-30 18:00:00','NULL');
7 rows affected
SELECT the_visitor_id, the_visitor_visit, the_visitor_returning
FROM my_table t1
WHERE EXISTS (
SELECT FROM my_table
WHERE the_visitor_id = t1.the_visitor_id
AND ctid <> t1.ctid
);
| the_visitor_id | the_visitor_visit | the_visitor_returning |
|---|---|---|
| VIS01 | 2019-05-02 09:00:00 | YES |
| VIS01 | 2019-05-04 12:00:00 | null |
| VIS01 | 2019-05-05 18:00:00 | null |
| VIS02 | 2019-05-06 18:30:00 | null |
| VIS02 | 2019-05-15 12:00:00 | null |
SELECT the_visitor_id, the_visitor_visit, the_visitor_returning
FROM (
SELECT *, count(*) OVER (PARTITION BY the_visitor_id) AS ct
FROM my_table
) sub
WHERE ct > 1;
| the_visitor_id | the_visitor_visit | the_visitor_returning |
|---|---|---|
| VIS01 | 2019-05-02 09:00:00 | YES |
| VIS01 | 2019-05-04 12:00:00 | null |
| VIS01 | 2019-05-05 18:00:00 | null |
| VIS02 | 2019-05-06 18:30:00 | null |
| VIS02 | 2019-05-15 12:00:00 | null |