Re: ON CONFLICT DO UPDATE

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
>