add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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?.
--https://stackoverflow.com/q/77244940/5298879
--OP's
create table test (
id int generated by default as identity primary key,
contract varchar,
amount0 int,
amount1 int,
price double precision );

INSERT INTO test (amount0, amount1, price)
SELECT
(SELECT val FROM unnest(ARRAY[50, 60, 80, 100, 200]) AS val ORDER BY random() LIMIT 1),
(SELECT val FROM unnest(ARRAY[50, 60, 80, 100, 200]) AS val ORDER BY random() LIMIT 1),
(SELECT val FROM unnest(ARRAY[1.5, 1.8, 2.1, 2.5]) AS val ORDER BY random() LIMIT 1)
FROM generate_series(1, 100);

SELECT id, amount0, amount1, price FROM test LIMIT 6;
CREATE TABLE

INSERT 0 100
id amount0 amount1 price
1 60 60 1.5
2 60 60 1.5
3 60 60 1.5
4 60 60 1.5
5 60 60 1.5
6 60 60 1.5 
SELECT 6
truncate test; alter table test alter column id restart;

INSERT INTO test (contract,amount0, amount1, price)
SELECT contract,amount0, amount1, price
FROM (values (50),(60),(80),(100),(200)) a(amount0),
(values (50),(60),(80),(100),(200)) b(amount1),
(values (1.5), (1.8), (2.1), (2.5)) c(price),
(values ('abc'),('klm'),('xyz') ) d(contract),
generate_series(1, 1e2, 1) duplicator(n)
order by n, random()
limit 100;

SELECT id, contract,amount0, amount1, price FROM test LIMIT 6;
TRUNCATE TABLE

ALTER TABLE

INSERT 0 100
id contract amount0 amount1 price
1 abc 100 100 1.5
2 abc 60 60 2.5
3 xyz 60 50 2.1
4 klm 100 60 2.1
5 klm 200 200 1.5
6 klm 80 80 1.8 
SELECT 6
--this shows how unique those combinations of options are
SELECT (contract,amount0, amount1, price) as combination, count(*)
FROM test
GROUP BY 1 ORDER BY 2 DESC, random()
LIMIT 6;
combination count
(abc,60,60,2.1) 1
(abc,60,100,1.8) 1
(klm,50,200,2.5) 1
(abc,80,80,2.5) 1
(klm,50,60,2.1) 1
(abc,50,100,1.5) 1 
SELECT 6
--You'll only see duplicates past 100 samples,
--because that's when unique combinations run out and need to be recycled
truncate test; alter table test alter column id restart;

INSERT INTO test (contract,amount0, amount1, price)
SELECT contract,amount0, amount1, price
FROM (values (50),(60),(80),(100),(200)) a(amount0),
(values (50),(60),(80),(100),(200)) b(amount1),
(values (1.5), (1.8), (2.1), (2.5)) c(price),
(values ('abc'),('klm'),('xyz') ) d(contract),
generate_series(1, 1e2, 1) duplicator(n)
order by n, random()
limit 602;

--this shows how unique those combinations of options are
SELECT (contract,amount0, amount1, price) as combination, count(*)
FROM test
GROUP BY 1 ORDER BY 2 DESC, random()
LIMIT 6;
TRUNCATE TABLE

ALTER TABLE

INSERT 0 602
combination count
(xyz,60,100,2.1) 3
(abc,80,200,2.5) 3
(abc,50,60,1.5) 2
(xyz,200,80,2.5) 2
(klm,200,80,1.5) 2
(klm,80,60,2.5) 2 
SELECT 6