How do I parse through an array of IDs to return all the corresponding records in PostgreSQL?-CodePudding

let me clarify:

I have a table:

CREATE TABLE public.assembly_part (
        assembly_id BIGSERIAL NOT NULL PRIMARY KEY,
        assembly_name VARCHAR(300),
        part_id_array INTEGER[]);

The idea here is that part_id_array will be records with different length arrays of integers,

eg.

{1,2,3}
{4,5}
{6,7,8,9}

And here I have the corresponding subassembly table:

CREATE TABLE public.subassembly_part (
        part_id BIGSERIAL NOT NULL PRIMARY KEY,
        part_name VARCHAR(300),
        part_rev VARCHAR(300),
        part_mat_req_id INTEGER);

So for array {1,2,3} I would want to return the rows from subassembly table where part_id is 1,2,3 respectively. How would the select statement look? Please let me know if I can be more clear, thank you!

EDIT:

Tried the following but didn't work out

SELECT *
FROM public.subassembly_part
WHERE public.subassembly_part.part_id = ANY (public.assembly_part.part_id_array);

Got this error:

ERROR:  missing FROM-clause entry for table "subassembly_part"

CodePudding user response：

If you want to just select parts from subassembly_part that match an array of part_id's then:

select * from  subassembly_part where subassembly_part.part_id= any ('{1, 2, 3}');

This can be simplified by not using an array and doing:

select * from  subassembly_part where subassembly_part.part_id in (1, 2, 3);