From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Vladimir Ryabtsev <greatvovan(at)gmail(dot)com> |
Cc: | psycopg(at)lists(dot)postgresql(dot)org |
Subject: | Re: Array of tuples as a parameter, with type casts |
Date: | 2019-12-04 18:47:18 |
Message-ID: | 55fa8796-a91b-6560-604c-83dd77b17980@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | psycopg |
On 12/4/19 10:44 AM, Adrian Klaver wrote:
> On 12/3/19 4:00 PM, Vladimir Ryabtsev wrote:
>> It does not change much, the error message just says it now expects
>> 'character varying'.
>> Anyway bigint problem is the next in the line.
>
> Yeah it is related to this:
>
> https://www.postgresql.org/docs/11/functions-array.html
> unnest(anyarray) setof anyelement
>
> Dealing with anyelement can be tricky.
>
> I tried out an alternate solution:
>
> json_query = "select * from json_to_recordset(%s) as t(a timestamp, b
> varchar, c bigint)"
>
> cur.execute(json_query,[Json([{'a': datetime.now().isoformat(), 'b':
> 'abc', 'c': 100},{'a': datetime.now().isoformat(), 'b': 'xyz', 'c':
> 200}])])
Should have added that Json comes from:
from psycopg2.extras import Json
http://initd.org/psycopg/docs/extras.html?highlight=json#json-adaptation
>
> Which results in:
>
> test_(postgres)# select * from json_to_recordset(E'[{"a":
> "2019-12-04T10:40:22.823910", "b": "abc", "c": 100}, {"a":
> "2019-12-04T10:40:22.823918", "b": "xyz", "c": 200}]') as t(a timestamp,
> b varchar, c bigint)
> ;
> a | b | c
> ----------------------------+-----+-----
> 2019-12-04 10:40:22.82391 | abc | 100
> 2019-12-04 10:40:22.823918 | xyz | 200
> (2 rows)
>
>>
>> --
>> Regards,
>> Vladimir
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Vladimir Ryabtsev | 2019-12-04 21:32:44 | Re: Array of tuples as a parameter, with type casts |
Previous Message | Adrian Klaver | 2019-12-04 18:44:47 | Re: Array of tuples as a parameter, with type casts |