Can I have an example how such a cast is done for the string "third" in
this case?
tiistai 5. tammikuuta 2016 17.38.34 UTC+2 Jon Rosebaugh kirjoitti:
>
> Aha; the arrow in your message appeared to be pointing at the wrong
> thing (due to variable-width fonts). My apologies.
>
> Looks like you'll need to perform a cast to convert your string to a
> JSONB string.
>
> On Tue, Jan 5, 2016, at 06:53 AM, Sami Pietilä wrote:
> > Hi,
> >
> > I think the data column is jsonb type.
> >
> > wsysdb=> select * from jtable;
> > data
> > ---------------------
> > ["first", "second"]
> > ["third", "fourth"]
> > (2 rows)
> >
> > wsysdb=> \d+ jtable;
> > Table "public.jtable"
> > Column | Type | Modifiers | Storage | Stats target | Description
> > --------+-------+-----------+----------+--------------+-------------
> > data | jsonb | | extended | |
> >
> >
> >
> > maanantai 4. tammikuuta 2016 19.41.29 UTC+2 Jon Rosebaugh kirjoitti:
> > >
> > > The @> operator (contains) is defined only for JSONB; your original
> > > message said you were using JSONB. However, this error says your
> column
> > > is just JSON. These two types are not the same thing; you should use
> > > JSONB if you have a choice.
> > >
> > > On Thu, Dec 31, 2015, at 03:43 PM, Sami Pietilä wrote:
> > > > Perhaps there is something wrong with my select. I tried with
> following
> > > > select and got an error message below:
> > > >
> > >
> select([database.tables['jtable'].c['data']]).where(database.tables['jtable'].c['data'].contains('third'))
>
>
> > >
> > > >
> > > > --- Error Message ---
> > > > sqlalchemy.exc.DataError: (psycopg2.DataError) invalid input syntax
> for
> > > > type json
> > > > LINE 3: WHERE jtable.data @> 'third'
> > > > ^
> > > > DETAIL: Token "third" is invalid.
> > > > CONTEXT: JSON data, line 1: third
> > > > [SQL: 'SELECT jtable.data \nFROM jtable \nWHERE jtable.data @>
> > > > %(data_1)s'] [parameters: {'data_1': 'third'}]
> > > >
> > > >
> > > > torstai 31. joulukuuta 2015 16.11.47 UTC+2 Jon Rosebaugh kirjoitti:
> > > > >
> > > > > Your SQL itself isn't going to work; there's no 'item' column in
> your
> > > > > select statement. You should read
> > > > > http://www.postgresql.org/docs/9.4/interactive/functions-json.html
> > > to
> > > > > see what operators you have. (I think you want '@>', not LIKE.)
> > > > >
> > > > > In SQLAlchemy, this would be done as
> > > > > .where(mytable.c['data'].contains('third')).
> > > > >
> > > > >
> > > >
> > > > --
> > > > You received this message because you are subscribed to the Google
> > > Groups
> > > > "sqlalchemy" group.
> > > > To unsubscribe from this group and stop receiving emails from it,
> send
> > > an
> > > > email to [email protected] <javascript:>.
> > > > To post to this group, send email to [email protected]
> > > <javascript:>.
> > > > Visit this group at https://groups.google.com/group/sqlalchemy.
> > > > For more options, visit https://groups.google.com/d/optout.
> > >
> >
> > --
> > You received this message because you are subscribed to the Google
> Groups
> > "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> an
> > email to [email protected] <javascript:>.
> > To post to this group, send email to [email protected]
> <javascript:>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" 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/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
