Re: ON CONFLICT DO UPDATE

Okay, I think I manage to solve it by adding balance = excluded.balance
inside the parenthesis of ON CONFLICT clause.

On Thu, May 10, 2018 at 1:13 PM, tango ward <tangoward15(at)gmail(dot)com> wrote:

> Sorry Sir Adrian, updating the code.
>
> for row in cur_tdc:
> print row['studentnumber'], row['firstname'], row['lastname'],
> row['currentbalance']
> cur_phil.execute("""
> INSERT INTO recipients_studentbalance(
> created, modified, student_id,
> first_name, middle_name,
> last_name, campus_name,
> year_level, section,
> balance, balance_as_of,
> school_system_id
> )
> VALUES (current_timestamp,
> current_timestamp,
> %s, %s, %s, %s, %s,
> %s, %s, %s, current_date,
> (SELECT id
> FROM education_schoolsystem
> WHERE name='My Test School.')
> )
> ON CONFLICT (school_system_id,
> student_id,
> campus_name
> ) DO UPDATE
> SET school_system_id =
> excluded.school_system_id,
> student_id = excluded.student_id,
> campus_name = excluded.campus_name
> """, (row['studentnumber'], row['firstname'],
> row['middlename'], row['lastname'],
> 'My Test Schol.',
> row['yearlevel'], row['section'],
> row['currentbalance']))
>
> On Thu, May 10, 2018 at 12:07 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> > wrote:
>
>> On 05/09/2018 07:04 PM, tango ward wrote:
>>
>>>
>>> Hi,
>>>
>>> Sorry for asking question again.
>>>
>>> I would like to know if there's a workaround for this. I need to insert
>>> Student Balance data into a table. The source data have duplicate values
>>> for student_id, school_id and campus_name. My StudentBalance model in Djano
>>> have Class Meta of:
>>>
>>> class Meta:
>>> unique_together = (
>>> "school",
>>> "student_id",
>>> "campus_name"
>>> )
>>>
>>> Searched online and found this magical tool called ON CONFLICT DO
>>> UPDATE. I played around with it, made it work but there's a problem. The
>>> balance data is not being updated which is because it's not in class Meta
>>> with unique_together. I would like to know if there's a way to update the
>>> data in Student Balance column without adding it to the class Meta?
>>>
>>
>> I doubt that Meta has anything to do with the balance not being updated.
>> What the Meta does is set up a UNIQUE index over the school, student_id and
>> campus_name columns. I am going to say the issue is with ON CONFLICT DO
>> UPDATE clause you create on the table. To help solve this we need:
>>
>> 1) The schema of the table StudentBalance points to.
>>
>> 2) The ON CONFLICT DO UPDATE clause you created on the table.
>>
>>
>>>
>>> Thanks,
>>> J
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com
>>
>
>