Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

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