Ok. Thank you!
torstai 21. tammikuuta 2016 16.42.30 UTC+2 Michael Bayer kirjoitti:
>
> that style of PG function use isn't directly supported yet and there is
> a recipe to achieve this at
>
>
Sounds good. I was able to write following sql sentense, but I could not
figure out how to refer to 'value' (which is created by the
jsonb_array_elements_text) named column in where().
sql_sentense = select([database.tables['jtable'],
I have been using this kind of sql to search case insensitive strings from
jsonb lists in postgresql. jsonb_array_elements_text unpacks the list
items as normal table rows from which text can be matched with ilike:
select * from jtable, jsonb_array_elements_text(jtable.data) where value
ILIKE
The generated SQL does not seem to be able to return any items. The
generated SQL looks like this.
Section: baserecords, Column name: first_names
SELECT baserecords._id
FROM baserecords
WHERE (lower(CAST(baserecords.last_names AS CHAR)) LIKE '%%' || :lower_1 ||
'%%')
I think casting JSONB
I am not exactly sure where to add sql.func.lower(). Following command gave
an error that lower() for jsonb does not exist.
sql_command =
select([self.tables[subsection].c[id_label]]).where(sql.func.lower(self.tables[subsection].c[column_name]).contains(cast(word.lower(),
JSONB)))
I think I got "contains" and "cast" working for case sensitive exact string
[.contains(cast(word, JSONB)))]. However I would like to match for case
insensitive substrings like using [.ilike('%'+word+'%'))]. How can I
specify to "contains" to match case insensitive? I also tried to use
es.
>
> 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;
ins) 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ä wro
Hi,
I am looking for an example how to use sqlalchemy core to search an item
from a column having a jsonb list.
I have PosgreSQL database having following example table from which I am
searching an item:
create table jtable (data jsonb);
insert into jtable (data) values ('["first","second"]');
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
Thank you! This kind of approach seems to work well:
ins = records.insert().returning(records.c.id)
result = connection.execute(ins)
row = result.fetchone()
sunnuntai 29. marraskuuta 2015 18.53.48 UTC+2 Michael Bayer kirjoitti:
>
>
>
> On 11/29/2015 10:50 AM, Sami Pietilä wr
Hi,
I have postgresql database with "records" table. There is "id" (bigserial
unique primary key) column. I need to insert a row in such a way that I get
id from newly inserted row.
ins = records.insert()
results = conn.execute(ins)
I was unable to find a code example of how to add
12 matches
Mail list logo
