3

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?

Kwhitejr
  • 2,206
  • 5
  • 29
  • 49

1 Answers1

2

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

klin
  • 112,967
  • 15
  • 204
  • 232
  • For future readers, my actual use case an `id` column on each table, so the only adjustment required was line 3 `join table_b on table_b.id` ... and line 4 `group by table_a.id`. Thanks! – Kwhitejr Dec 31 '18 at 19:41