Thanks for the quick response Jeremy. Very helpful. Our use case is a generic attributes table with the following columns: type VARCHAR value JSONB
The name or type of the attribute is stored in the "type" column and the attribute value is stored in the "value" column. Each attribute value can either be an Int, Bool, or String. In order to capture the type of the attribute value while using a single column, we chose to use a JSONB column and store JSON primitives in that column. It is sort of a hack to capture both the value and type without having a separate column for each attribute. As an alternative, we could store all values as a "string" and do some sort of type inference when retrieving the data. If you have any other ideas on how to achieve the same effect, I'm all ears. Thanks! On Wednesday, April 24, 2019 at 4:05:34 PM UTC-4, [email protected] wrote: > > According to the PG docs, the following JSON primitive types can be stored > in a JSONB column: string, number, boolean, null. > https://www.postgresql.org/docs/9.4/datatype-json.html. > > However, when using ROM + Sequel as such: > > [8] pry(main)> ds.insert(title: "A test", meta: Sequel.pg_jsonb(1)) > > I am getting the following error: > > Sequel::DatabaseError: PG::DatatypeMismatch: ERROR: column "meta" is of type > jsonb but expression is of type integerLINE 1: ... INTO "books" ("title", > "meta") VALUES ('A test', 1) RETURNI... > ^HINT: You will > need to rewrite or cast the expression. > from > /Users/solnic/.gem/ruby/2.6.3/gems/sequel-5.19.0/lib/sequel/adapters/postgres.rb:152:in > `async_exec'Caused by PG::DatatypeMismatch: ERROR: column "meta" is of type > jsonb but expression is of type integerLINE 1: ... INTO "books" ("title", > "meta") VALUES ('A test', 1) RETURNI... > ^HINT: You will need to rewrite or cast the expression. > > > I am wondering if this is related to: > > "The pg_json extension adds support for Sequel > <https://sequel.jeremyevans.net/rdoc-plugins/classes/Sequel.html> to > handle PostgreSQL's json and jsonb types. It is slightly more strict than > the PostgreSQL json types in that the object returned should be an array or > object (PostgreSQL's json type considers plain numbers strings, true, > false, and null as valid). *Sequel* > <https://sequel.jeremyevans.net/rdoc-plugins/classes/Sequel.html>* will > work with PostgreSQL json values that are not arrays or objects, but > support is fairly limited and the values do not roundtrip.*" > > taken from the Sequel docs: > https://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/pg_json_rb.html > . > > If so, is there any other out-of-the-box Sequel support that I will be > foregoing by choosing to store JSON primitives in a JSONB column while > using Sequel and Postgres rather than a JSON array or JSON hash? > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
