This has been tested on 14.5 and 13.7.
When an index is created on the root of a (declarative) partitioned table, that index is also created on the children,
unlessthere is an existing index on that child that matches the definition of the new index. It seems that using `=
TRUE`confuses it, compared to `IS TRUE`.
Test case:
BEGIN;
CREATE TABLE public.t (
id bigint NOT NULL,
t timestamp without time zone NOT NULL,
b boolean NOT NULL
) PARTITION BY RANGE (t);
CREATE TABLE public.t_older (
id bigint NOT NULL,
t timestamp without time zone NOT NULL,
b boolean NOT NULL
);
CREATE INDEX ON public.t_older USING btree (id) WHERE b IS TRUE;
CREATE INDEX ON public.t_older USING btree (id) WHERE b = TRUE;
ALTER TABLE t ATTACH PARTITION t_older
FOR VALUES FROM ('2010-01-01') TO ('2022-01-01');
CREATE INDEX ON public.t USING btree (id) WHERE b IS TRUE;
CREATE INDEX ON public.t USING btree (id) WHERE b = TRUE;
COMMIT;
The result is:
xof=# \d t
Partitioned table "public.t"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | bigint | | not null |
t | timestamp without time zone | | not null |
b | boolean | | not null |
Partition key: RANGE (t)
Indexes:
"t_id_idx" btree (id) WHERE b IS TRUE
"t_id_idx1" btree (id) WHERE b = true
Number of partitions: 1 (Use \d+ to list them.)
fin_test=# \d t_older
Table "public.t_older"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | bigint | | not null |
t | timestamp without time zone | | not null |
b | boolean | | not null |
Partition of: t FOR VALUES FROM ('2010-01-01 00:00:00') TO ('2022-01-01 00:00:00')
Indexes:
"t_older_id_idx" btree (id) WHERE b IS TRUE -- Correctly does not create a new index
"t_older_id_idx1" btree (id) WHERE b = true
"t_older_id_idx2" btree (id) WHERE b = true -- Unexpected duplicated index
