Find records which have multiple occurrences in another table array (postgres)

Question

I have a table which has records in array. Also there is another table which have single string records. I want to get records which have multiple occurrences in another table. Following are tables;

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"}                        
    6  | {"viper","ferrariff","bmwi8","viper"}   
    7  | {"ferrariff","viper","viper","volt"}

vehicle_names

    id |  vehicle_name
  -----+-----------------------
    1  |  byd_tang
    2  |  volt
    3  | viper
    4  | laferrari
    5  | sonata
    6  |  jaguarxf
    7  |  swift
    8  |  teslax
    9  | mirai
    10 | ferrariff
    11 | bmwi8

I have a query which can give output what I expect but its not optimal and may be its expensive query.

This is the query:

select veh_name 
from vehicle_names dsb
where (select count(*) from vehicle dsd
       where dsb.veh_name = ANY (dsd.veh_types)) > 1

The output should be:

 byd_tang
 volt
 viper

Tim Biegeleisen · Accepted Answer · 2019-09-10 05:45:24Z

2

One option would be an aggregation query:

SELECT
   vn.id,
   vn.veh_name
FROM vehicle_names vn
INNER JOIN vehicle v
   ON vn. veh_name = ANY (v.veh_types)
GROUP BY
  vn.id,
  vn.veh_name
HAVING
  COUNT(*) > 1;

This only counts a vehicle name which appears in two or more records in the other table. It would not pick up, for example, a single vehicle record with the same name appearing two or more times.

edited Sep 10, 2019 at 5:45

answered Sep 10, 2019 at 5:36

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Find records which have multiple occurrences in another table array (postgres)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related