0

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?

1
  • 1
    Use a table, enforce the constraints there and convert the table contents to json?
    – Vérace
    Commented Sep 12, 2017 at 14:46

1 Answer 1

0

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

Your Answer

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

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