Postgres 12

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?.

CREATE TABLE vehicle (veh_id int, vehicle_types text[]);
INSERT INTO VEHICLE values
  (1, '{byd_tang,volt,viper,laferrari}')
, (2, '{volt,viper}')
, (3, '{byd_tang,sonata,jaguarxf}')
, (4, '{swift,teslax,mirai}')     
, (5, '{volt,viper}')             
, (6, '{viper,ferrariff,bmwi8,viper}')
, (7, '{ferrariff,viper,viper,volt}')
, (8, '{}')                            -- additional test rows
, (9, null);

9 rows affected

SELECT *, ARRAY(SELECT DISTINCT unnest (vehicle_types)) AS vehicle_types_uni
FROM   vehicle;



veh_id
vehicle_types
vehicle_types_uni


1
{byd_tang,volt,viper,laferrari}
{volt,laferrari,viper,byd_tang}

2
{volt,viper}
{volt,viper}

3
{byd_tang,sonata,jaguarxf}
{sonata,jaguarxf,byd_tang}

4
{swift,teslax,mirai}
{teslax,swift,mirai}

5
{volt,viper}
{volt,viper}

6
{viper,ferrariff,bmwi8,viper}
{bmwi8,ferrariff,viper}

7
{ferrariff,viper,viper,volt}
{ferrariff,volt,viper}

8
{}
{}

9
null
{}


-- preserve original order
SELECT *, ARRAY (SELECT v
                 FROM   unnest(vehicle_types) WITH ORDINALITY t(v,ord)
                 GROUP  BY 1
                 ORDER  BY min(ord)
                ) AS vehicle_types_uni
FROM   vehicle;
   


veh_id
vehicle_types
vehicle_types_uni


1
{byd_tang,volt,viper,laferrari}
{byd_tang,volt,viper,laferrari}

2
{volt,viper}
{volt,viper}

3
{byd_tang,sonata,jaguarxf}
{byd_tang,sonata,jaguarxf}

4
{swift,teslax,mirai}
{swift,teslax,mirai}

5
{volt,viper}
{volt,viper}

6
{viper,ferrariff,bmwi8,viper}
{viper,ferrariff,bmwi8}

7
{ferrariff,viper,viper,volt}
{ferrariff,viper,volt}

8
{}
{}

9
null
{}


-- according UPDATE
UPDATE vehicle
SET    vehicle_types = ARRAY (
                 SELECT v
                 FROM   unnest(vehicle_types) WITH ORDINALITY t(v,ord)
                 GROUP  BY 1
                 ORDER  BY min(ord)
                )
WHERE  cardinality(vehicle_types) > 1
AND    vehicle_types <> ARRAY (
                 SELECT v
                 FROM   unnest(vehicle_types) WITH ORDINALITY t(v,ord)
                 GROUP  BY 1
                 ORDER  BY min(ord)
                ) -- suppress empty updates (optional)
RETURNING *;


veh_id
vehicle_types


6
{viper,ferrariff,bmwi8}

7
{ferrariff,viper,volt}


TABLE vehicle;


veh_id
vehicle_types


1
{byd_tang,volt,viper,laferrari}

2
{volt,viper}

3
{byd_tang,sonata,jaguarxf}

4
{swift,teslax,mirai}

5
{volt,viper}

8
{}

9
null

6
{viper,ferrariff,bmwi8}

7
{ferrariff,viper,volt}

veh_id	vehicle_types	vehicle_types_uni
1	{byd_tang,volt,viper,laferrari}	{volt,laferrari,viper,byd_tang}
2	{volt,viper}	{volt,viper}
3	{byd_tang,sonata,jaguarxf}	{sonata,jaguarxf,byd_tang}
4	{swift,teslax,mirai}	{teslax,swift,mirai}
5	{volt,viper}	{volt,viper}
6	{viper,ferrariff,bmwi8,viper}	{bmwi8,ferrariff,viper}
7	{ferrariff,viper,viper,volt}	{ferrariff,volt,viper}
8	{}	{}
9	null	{}