Is there a way to create a unique constrain on a jsonb column in a table?
I have a table with a "label" column that is a jsonb column storing objects that look like this:
{ "en": "Girl", "ja": "女性" }. I would like to set up the unique constraint to ensure that each row is unique based on label->>en at least. Is this possible when defining the schema? Or do I need to restort to brute forcing it in the SQL?
I am using Drizzle ORM with node postrgesql.9 Replies
I think it is not possible to add
unique for a value of a jsonb. But, a unique on the jsonb would workRight, I wondered about that. Is it doing a deep comparison? In my use case it is pretty shallow, but I wonder what the limitation for this would be. 🤔
looks like it does. I have updated the example with an object and a property, still works
I guess it compares it at the binary level or something like that
@RickDom Do you know about generated columns? Maybe you could have a generated
label_en column and create the index on that one. I'm not sure if there are any restrictions around generated columns though that would prevent this.
https://www.postgresql.org/docs/current/ddl-generated-columns.htmlPostgreSQL Documentation
5.3. Generated Columns
5.3. Generated Columns # A generated column is a special column that is always computed from other columns. Thus, it is …
Yeah I was debating about doing this with a generated column. It might be the cleanest route depending on what it is like getting a generated column setup in the drizzle schema
Thanks, I will take a look at it!
It was my first try before sharing this playground with you. Tell me if you want I update it with a generated column 🫡
OK that is easier than I thought. I think in my initial use case, the
unique on the jsonb column should be good enough as those are pretty simple and since it is doing a deep comparison it should be ok. But I think I might have some other usecases with jsonb columns where I think going with the generated column will likely be the best route due to the complexity. Thankst @Raphaël M (@rphlmr) ⚡ and @Niklas !