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/75547215/5298879
create table licence (
licence_id smallserial,
user_id int,
property text,
validity_dates daterange,
competition_ids text[]);

insert into licence values
(1,20,'JOHN','[2022-01-01,2025-01-02)',null),
(2,21,'JOHN','[2022-01-01,2025-01-02)','{abcd-efg, asda-12df}'),
(3,22,'JOHN','[2022-01-01,2025-01-02)','{}');

table licence;
CREATE TABLE

INSERT 0 3
licence_id user_id property validity_dates competition_ids
1 20 JOHN [2022-01-01,2025-01-02) null
2 21 JOHN [2022-01-01,2025-01-02) {abcd-efg,asda-12df}
3 22 JOHN [2022-01-01,2025-01-02) {} 
SELECT 3
--see which records the original query was targetting
SELECT * FROM licence
WHERE NOT competition_ids @> ARRAY['hijk-23lm']
AND validity_dates = DATERANGE('2022-01-01', '2025-01-02', '[)')
AND property = 'JOHN';
--it did not consider the first record with `null` in competition_ids
--because of null comparison in @>
licence_id user_id property validity_dates competition_ids
2 21 JOHN [2022-01-01,2025-01-02) {abcd-efg,asda-12df}
3 22 JOHN [2022-01-01,2025-01-02) {} 
SELECT 2
UPDATE licence
SET competition_ids
= ARRAY(SELECT DISTINCT UNNEST(array_cat(competition_ids, ARRAY['hijk-23lm'] )))
WHERE NOT coalesce(competition_ids,'{}'::text[]) @> ARRAY['hijk-23lm']
AND validity_dates = DATERANGE('2022-01-01', '2025-01-02', '[)')
AND property = 'JOHN';
UPDATE 3
table licence;
licence_id user_id property validity_dates competition_ids
1 20 JOHN [2022-01-01,2025-01-02) {hijk-23lm}
2 21 JOHN [2022-01-01,2025-01-02) {abcd-efg,asda-12df,hijk-23lm}
3 22 JOHN [2022-01-01,2025-01-02) {hijk-23lm} 
SELECT 3