0

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.

Improve this question

1 Answer 1

Reset to default
0

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.

Improve this answer
2
  • 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?
    – tdammon
    Oct 6, 2020 at 17:10
  • 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.
    – Laurenz Albe
    Oct 7, 2020 at 5:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.