Use ENUM in array of objects

Question

Is it possible to setup an ARRAY column with objects/JSON on a TABLE, forcing a parameter of the objects in the ARRAY to adhere to an ENUM, while keeping that object parameter unique?

Data examples:

ENUM val1, val2, val3

[{p1: val1, p2: 'something'}, {p1: val2, p2: 'something'}] <-- valid
[{p1: val1, p2: 'something'}, {p1: val4, p2: 'something'}] <-- not valid, val4 not an ENUM value
[{p1: val1, p2: 'something'}, {p1: val1, p2: 'something else'}] <-- not valid, p1 not unique

If it is possible, using PostgreSQL and sequelize, how would I go about it, setting up the column?

• Vérace: Use a table, enforce the constraints there and convert the table contents to json?

Answer 1

You have a few options,

1. You can just use CHECK conditions if it's on one table's jsonb column
2. CREATE TABLE with the possible values and set it as an fkey.
3. Reduce that jsonb to something more logical in the app, or in the db. Create a function such that f(jsonb)=>k and store k on the table, or possibly as an ENUM