Using PostgreSQL to hold an array of data

Question

I am trying to use a postgreSQL data base column to store values from an array (or point to an enum table which does this). I will get an array of data like ['one','two','five']. The array can contain any amount of items from 0 to 10.

I have an enum table with these values:

id value
1 one
2 two
3 three
4 four
5 five

I want my database cell to point to all of the values that are contained in the array. Is this even possible or is there a common way to structure database tables to fix this issue.

So my desired solution when passed the array ['one','two','five'] would be to have a single cell in my database hold the enum pointers 1, 2, 5.

Answer 1

Create a view on the table that joins with the enum table and shows an array of text.

Then create an INSTEAD OF INSERT OR UPDATE trigger on the view that translates the strings to numbers and stores the number array in the underlying table.

• tdammon: Thank you for the guidance. After reading some docs on creating views I think I understand the general idea. I am still a bit confused about how I create the INSTEAD OF INSERT trigger. Is part of the query that will be run by my backend each time there is an update to my array?
• Laurenz Albe: Yes, you will also need an INSTEAD OF UPDATE trigger that does the same thing whenever there is sn UPDATE; they could use the same trigger function. Whenever even a single array element is modified, a new version of the whole row has to be written, that's how PostgreSQL works. To avoid that, you should normalize the design.