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?.
select version();
| version |
|---|
| PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit |
SELECT 1
create table products(
product_name text not null,
created_at timestamp with time zone not null,
modified_at timestamp with time zone not null,
amount numeric,
units text,
requires_cold_storage boolean,
promoted boolean
);
insert into products values
('Bottled Mineral Water 1,5l', '2025-01-02T06:53:11', '2025-01-02T06:53:11', '1.5', 'l', null, false),
('Frozen Vegetable Mix', '2025-01-02T06:55:40', '2025-01-02T06:55:40', '450', 'g', true, true),
('Toothbrush', '2025-01-02T07:12:55', '2025-01-02T07:12:55', '1', 'pcs', false, false),
('Bottled Mineral Water 1,5l', '2025-01-02T06:53:11', '2025-01-03T15:01:32', '1.5', 'l', false, true),
('Frozen Vegetable Mix', '2025-01-02T06:55:40','2025-01-04T00:00:00', '450', 'g', true, false)
;
CREATE TABLE
INSERT 0 5
select * from products;
| product_name | created_at | modified_at | amount | units | requires_cold_storage | promoted |
|---|---|---|---|---|---|---|
| Bottled Mineral Water 1,5l | 2025-01-02 06:53:11+00 | 2025-01-02 06:53:11+00 | 1.5 | l | null | f |
| Frozen Vegetable Mix | 2025-01-02 06:55:40+00 | 2025-01-02 06:55:40+00 | 450 | g | t | t |
| Toothbrush | 2025-01-02 07:12:55+00 | 2025-01-02 07:12:55+00 | 1 | pcs | f | f |
| Bottled Mineral Water 1,5l | 2025-01-02 06:53:11+00 | 2025-01-03 15:01:32+00 | 1.5 | l | f | t |
| Frozen Vegetable Mix | 2025-01-02 06:55:40+00 | 2025-01-04 00:00:00+00 | 450 | g | t | f |
SELECT 5
WITH
duplicates as (
SELECT
product_name,
created_at
FROM products
GROUP BY
product_name,
created_at
HAVING count(*) > 1
)
SELECT t.*
FROM products as t
JOIN duplicates USING(product_name, created_at)
| product_name | created_at | modified_at | amount | units | requires_cold_storage | promoted |
|---|---|---|---|---|---|---|
| Bottled Mineral Water 1,5l | 2025-01-02 06:53:11+00 | 2025-01-02 06:53:11+00 | 1.5 | l | null | f |
| Frozen Vegetable Mix | 2025-01-02 06:55:40+00 | 2025-01-02 06:55:40+00 | 450 | g | t | t |
| Bottled Mineral Water 1,5l | 2025-01-02 06:53:11+00 | 2025-01-03 15:01:32+00 | 1.5 | l | f | t |
| Frozen Vegetable Mix | 2025-01-02 06:55:40+00 | 2025-01-04 00:00:00+00 | 450 | g | t | f |
SELECT 4