Search Postgresql Archives

Re: case statement within insert

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On Fri, May 25, 2018 at 10:19 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
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@xxxxxxxxxxx

 
Using %s works. I also learned that if I just use '%PE%' or '%NSTP%', the LIKE _expression_ will treat them as placeholder. Solution is to double the percent signs.


Thanks a lot guys! I love you all.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux