arrays - SQLAlchemy（Postgres）ARRAY 与 JSON 的整数列表

Question

我想使用 SQLAlchemy（使用 Postgres）将整数列表存储在 Column 中。

我最好使用 ARRAY 还是 JSON？我的猜测是 ARRAY 在这种情况下会有更好的性能，但我没有看到任何在线确认。

附加问题：如果我希望整数是唯一的怎么办？

标签： arraysjsonpostgresqlsqlalchemyflask-sqlalchemy

Answer 1

PostgreSQL collects statistics on array membership for PostgreSQL arrays, but not for JSON arrays. This often leads to much better performance. Plus PostgreSQL arrays are typed, which seems like a benefit for your case.

Additional question: What if I want the integers to be unique?

Can you expand on that? You want it to throw an error if you try to add a second copy of any one element? Silently ignore the addition? (Although neither case has in-place additions, it is really an assignment of the new array/JSONB which has happens to have one more element than the old one).