PostgreSQL JSONB data type in DB resource
Moderators: Jon, Steve, Ian, Dave
-
- Ebase User
- Posts: 82
- Joined: Fri Jan 15, 2016 2:34 pm
PostgreSQL JSONB data type in DB resource
Does Ebase support PostgreSQL's jsonb data type in DB resources? I've tried different combinations of data types for the resource field as well as the mapped form field, but haven't found any that worked without requiring a manual typecast in the insert/update query (which means assisted SQL can't be used, unless there's a way to typecast in assisted SQL?).
0 x
-
- Moderator
- Posts: 1342
- Joined: Wed Sep 12, 2007 12:49 pm
Re: PostgreSQL JSONB data type in DB resource
It should work if you set the form field type to Object.
0 x
-
- Ebase User
- Posts: 82
- Joined: Fri Jan 15, 2016 2:34 pm
Re: PostgreSQL JSONB data type in DB resource
Hi Jon,
I do indeed have the form field type as Object. I can't find any resource field type that doesn't lead to an SQL error though.
Any kind of string type (varchar, clob, longvarchar) gives me this error:
The workaround I've found for now is to create a new resource just to update this column, which uses native SQL so I can do a "::JSON" cast in the update/insert statements. (I got this from this blog post, under the "JSON Data Insertion" section). I wanted to keep the original resource using Assisted SQL, because it has quite a few fields and is used in many forms. Changing it to Native SQL would require every field to be mapped in every form, which we don't need and would be a big bother to change. I'm still using the original resource to load the JSON field, but I've set it to Read Only so the resource doesn't try to update it. This way I avoid needing an extra call to the DB when loading my form. For this solution, I'm using the CLOB type in both resources for the JSON column.
I do indeed have the form field type as Object. I can't find any resource field type that doesn't lead to an SQL error though.
Any kind of string type (varchar, clob, longvarchar) gives me this error:
Meanwhile, any binary type (blob, longvarbinary) lead to this error:ERROR: column "other_providers" is of type jsonb but expression is of type character varying Hint: You will need to rewrite or cast the expression.
(parameter 48 in this case corresponds to my JSONB column)No value specified for parameter 48.
The workaround I've found for now is to create a new resource just to update this column, which uses native SQL so I can do a "::JSON" cast in the update/insert statements. (I got this from this blog post, under the "JSON Data Insertion" section). I wanted to keep the original resource using Assisted SQL, because it has quite a few fields and is used in many forms. Changing it to Native SQL would require every field to be mapped in every form, which we don't need and would be a big bother to change. I'm still using the original resource to load the JSON field, but I've set it to Read Only so the resource doesn't try to update it. This way I avoid needing an extra call to the DB when loading my form. For this solution, I'm using the CLOB type in both resources for the JSON column.
0 x
-
- Moderator
- Posts: 1342
- Joined: Wed Sep 12, 2007 12:49 pm
Re: PostgreSQL JSONB data type in DB resource
Ah, I tested this in the latest version - V5.8 and it worked fine with a resource field type of either VARCHAR or LONGVARBINARY. But I think we may have changed something in this release which has effectively fixed this problem.
I'm not exactly what syntax you're using, but it should in general be possible to put your cast into the column list (in the Select columns box) with Assisted SQL - this is certainly possibly with functions. But you may need to be careful to use the AS keyword to rename the column in the result set e.g. something like this:
The resulting column names should match the resource field names.
I'm not exactly what syntax you're using, but it should in general be possible to put your cast into the column list (in the Select columns box) with Assisted SQL - this is certainly possibly with functions. But you may need to be careful to use the AS keyword to rename the column in the result set e.g. something like this:
Code: Select all
select col1, todate(col2) as col2, touppercase(col3) as col3
0 x
-
- Ebase User
- Posts: 82
- Joined: Fri Jan 15, 2016 2:34 pm
Re: PostgreSQL JSONB data type in DB resource
Ah, that may explain why it didn't work for me; this application is still running on 5.5.0.
I tried adding the cast to the Assisted SQL column list, but it looks like it might not be carried over to the update/insert logic, since I'm still getting the same error. I'll continue using my workaround for now, but good to know that in 5.8+ it shouldn't be needed anymore.
Thanks!
I tried adding the cast to the Assisted SQL column list, but it looks like it might not be carried over to the update/insert logic, since I'm still getting the same error. I'll continue using my workaround for now, but good to know that in 5.8+ it shouldn't be needed anymore.
Thanks!
0 x
Who is online
Users browsing this forum: No registered users and 7 guests