How to define a column as an Array of any type in POSTGRESQL FlaskSQLAlchemy?-CodePudding

I am using flask_sqlalchemy and I want to define column prompts to be an array which accepts values of any data type, particularly string or json.

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy(app)

class PreannotationPrompts(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique = True)
    type = db.Column(db.String(100))
    '''
    Example of values prompts should hold
    [
         {"key": "value", "key2": []},
         'Hello',
         {"key": "value", "key2": []},
         'Hi'
    ]
    '''
    prompts = db.Column(db.String(1000))  # How to define ?

How can I define prompts column appropriately in this case?

CodePudding user response：

SQL is a typed language, you you cannot have a column that accepts "any type". You should rethink your data model. Avoid arrays as column data types.

CodePudding user response：

If you anticipate fluid model within a column you probably should use JSONB type. You can store a dict or an array without a priori declared element's type.

from sqlalchemy.dialects.postgresql import JSONB

prompts = db.Column(JSONB, default=list)

However, using a schemaless column (like JSONB) can indicate that you have a problem with properly designed data model.