Re: case statement within insert - Mailing list 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.klaver@aklaver.com