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].
> 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.
--
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.
