From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | tango ward <tangoward15(at)gmail(dot)com>, "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: case statement within insert |
Date: | 2018-05-25 14:19:55 |
Message-ID: | 9730aa5a-a2ac-4f1a-618c-50f377d2ab1a@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 05/25/2018 07:05 AM, Adrian Klaver wrote:
> On 05/25/2018 06:52 AM, Adrian Klaver wrote:
>> On 05/25/2018 02:04 AM, tango ward wrote:
>>>
>>>
>>> I want to insert data from mysql into a table in postgresql. I want
>>> to check when the subjectcode contains PE or NSTP so I can assign
>>> True or False to another column in destination DB.
>>>
>>>
>>> # Source data:
>>>
>>> # Source data: MySQL
>>> curr_msql.execute(''' SELECT code, subjectname
>>> FROM test_subj ''')
>>>
>>> # Destination
>>> for row in curr_msql:
>>> curr_psql.execute(''' INSERT INTO subs (
>>> created, modified,
>>> subjcode, subjname,
>>> is_pe_or_nstp)
>>>
>>> VALUES (current_timestamp,
>>> current_timestamp,
>>> %s, %s,
>>> CASE
>>> WHEN code like '%%PE%%' or code like '%%NSTP%%'
>>
>> Shouldn't the above be?:
>>
>> subjcode like '%%PE%%' or subjcode like '%%NSTP%%'
>
> Turns out that won't work as you cannot refer to a column in the CASE:
>
> cur.execute("insert into cell_per(category, cell_per, season,
> plant_type, short_category) values('herb test', 1, 'annual', CASE WHEN
> category like '%%herb%%' THEN 'herb' ELSE 'flower' END, 'ht' )")
>
> ProgrammingError: column "category" does not exist
> LINE 1: ...gory) values('herb test', 1, 'annual', CASE WHEN category l...
> ^
> HINT: There is a column named "category" in table "cell_per", but it
> cannot be referenced from this part of the query.
>
>
> This works:
>
> cur.execute("insert into cell_per(category, cell_per, season,
> plant_type, short_category) values(%s, 1, 'annual', CASE WHEN %s like
> '%%herb%%' THEN 'herb' ELSE 'flower' END, 'ht' )", ('herb test', 'herb
> test'))
>
>
> So change code to row['code']?
Insufficient caffeine.
...
WHEN %s like '%%PE%%' or %s like '%%NSTP%%'
...
, (row['code'], row['subjectname'], row['code'], row['code'])
FYI this is why I like the named parameters then the above could be
shortened to:
{'code': row['code'], 'subjectname': row['subjectname']}
and you get clearer query code:
VALUES (current_timestamp, current_timestamp,
%(code)s, %(subjectname)s,
CASE
WHEN %(code)s like '%%PE%%' or %(code)s like '%%NSTP%%'
THEN True
ELSE False
END)
>
>
>
>>
>>> THEN True
>>> ELSE False
>>> END) ''', (row['code'], row['subjectname']))
>>>
>>> I am getting TypeError: not all arguments converted during string
>>> formatting.
>>>
>>> Any advice pls?
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2018-05-25 15:41:50 | Re: case statement within insert |
Previous Message | Raymond O'Donnell | 2018-05-25 14:15:13 | Re: Insert data if it is not existing |