Postgres 10.5: how to link an array of integers to primary keys of separate table?

user_4723005 0
Viewed 300

I have two Postgres tables, TABLE_A and TABLE_B.

TABLE_A contains column table_b_fks, whose values are an array of integers (e.g. "{1,2,4}"), which are of the set of primary keys for TABLE_B.

TABLE_B has two columns, the primary keys and text.

My goal is to create a Materialized View which is identical to TABLE_A except that the table_b_fks array of integers is replaced with an array of text's from TABLE_B.

Is this possible? It seems that postgres does not support arrays of foreign keys. Are there alternatives?

materialized-views postgresql arrays
1 Answers

In fact, an array of integers cannot be formally concerned as foreign keys, though it can be easily used to join tables:

select table_b_fks, array_agg(name)
from table_a
join table_b on id = any(table_b_fks)
group by 1

Test it in db<>fiddle.

Note that the example is a bit simplified, you need a unique column(s) in table_a to identify its rows (that can be used in group by).

user_19957380
Related