0

I'm looking for help with this little code :

INSERT INTO "WU_MatchingUsers"("IDWU_User1", "IDWU_User2", "MatchingScore") VALUES(i, j, random);
                     ON CONFLICT ("IDWU_User1", "IDWU_User2")
                     DO
                        UPDATE SET "MatchingScore" = EXCLUDED."MatchingScore";

How can I make if there is already an existing row of the id 1 + id 2 to just only update the matching score ? This not seems to work since I have this error :

ERREUR:  erreur de syntaxe sur ou près de « ON »
LIGNE 16 :                      ON CONFLICT ("IDWU_User1", "IDWU_User2"...

Kind regards !

Mathieu Arthur
  • 31
  • 7
  • 2
    There should be no semicolon before the `ON`. It's still part of the `INSERT` statement. – Bergi Apr 16 '21 at 09:00
  • That's didn't solve the problem ... – Mathieu Arthur Apr 16 '21 at 09:11
  • 1
    [Sure does for me](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=635fec030dd91233cd03564af0211372) – Bergi Apr 16 '21 at 09:16
  • What do you get when you run `select version();` –  Apr 16 '21 at 09:17
  • I obtain this : ```PostgreSQL 11.10 (Debian 11.10-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit ``` – Mathieu Arthur Apr 16 '21 at 09:21
  • Then the stray `;` in the middle of the statement **is** the reason. –  Apr 16 '21 at 09:29
  • I have this error now : ```ERREUR: il n'existe aucune contrainte unique ou contrainte d'exclusion correspondant à la spécification ON CONFLICT CONTEXTE : instruction SQL « INSERT INTO "WU_MatchingUsers"("IDWU_User1", "IDWU_User2", "MatchingScore") VALUES(i, j, random) ON CONFLICT ("IDWU_User1", "IDWU_User2") DO UPDATE SET "MatchingScore" = EXCLUDED."MatchingScore" » fonction PL/pgsql inline_code_block, ligne 15 à instruction SQL``` – Mathieu Arthur Apr 16 '21 at 09:35
  • That's a completely different error message and means (if my French doesn't fail me) that you do not have a unique constraint on those two columns. –  Apr 16 '21 at 12:04

0 Answers0