Re: ON CONFLICT DO UPDATE - Mailing list pgsql-general
| From | tango ward |
|---|---|
| Subject | Re: ON CONFLICT DO UPDATE |
| Date | |
| Msg-id | CAA6wQL+cLH=6+5DRXh31ch8C9vL48cOuvCQsPUuS5_euBaou5Q@mail.gmail.com Whole thread Raw |
| In response to | Re: ON CONFLICT DO UPDATE (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: ON CONFLICT DO UPDATE
(tango ward <tangoward15@gmail.com>)
Re: ON CONFLICT DO UPDATE (Alban Hertroys <haramrae@gmail.com>) |
| List | pgsql-general |
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']))
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.klaver@aklaver.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.klaver@aklaver.com
pgsql-general by date: