Thread: ON CONFLICT DO UPDATE

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

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

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.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

> On 10 May 2018, at 7:13, tango ward <tangoward15@gmail.com> wrote:

>                          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

I'm pretty sure this ought to read:
        ON CONFLICT (school_system_id, student_id, campus_name)
        DO
           UPDATE SET modified = EXCLUDED.modified,
                balance = EXCLUDED.balance,
                balance_as_of = EXCLUDED.balance_as_of

Instead, you were re-assigning the keys (school_system_id, student_id, campus_name) to the same values again.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.