Content-Type: multipart/alternative;
 boundary="------------hCe4pIZN2KI7jILkmBJNs38b"
Message-ID: <8a13ca66-0a65-2a3c-f1e3-8736dd30dfe1@gmail.com>
Date: Mon, 15 May 2023 01:42:46 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.10.0
Subject: Re: how do I capture conflicting rows
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: 
 <CALXkTpx3iLuGRx1_4N6GJ85ptoW4n4XSdh3ChJLE-hrFGC982g@mail.gmail.com>
 <34308ba9-6033-6e57-f44e-500b2ca9cad5@gmail.com>
 <CALXkTpxRGtUjH+GP67W_+fXahaUUmpTPE+LiLugF--_862PD-Q@mail.gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
In-Reply-To: 
 <CALXkTpxRGtUjH+GP67W_+fXahaUUmpTPE+LiLugF--_862PD-Q@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/8a13ca66-0a65-2a3c-f1e3-8736dd30dfe1%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------hCe4pIZN2KI7jILkmBJNs38b
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Maybe you can get something for nothing, but I'm dubious; you're going to 
have to pay a price /somewhere/.

On 5/15/23 01:25, Nikhil Ingale wrote:
> Thing is there is a list of tables (350+ tables) on which I'm running the 
> insert query i.e., INSERT INTO table ON CONFLICT DO NOTHING to continue 
> inserting the records by ignoring the conflicting rows. But, at the same 
> time I would like to capture the conflicting rows or every single 
> conflicting column (not just the PK's) and their values for every single 
> table.
>
> INSERT INTO TABLE ON CONFLICT DO NOTHING don't even report us on the 
> conflicting rows. How do I identify what the conflicting rows are by 
> continuing the inserts to happen even if there are any conflicts. I mean 
> my insert command shouldn't fail on conflicts but at the same time 
> conflicts should be reported to the user.
>
> Regards,
> Nik
>
> On Mon, May 15, 2023 at 11:39 AM Ron <ronljohnsonjr@gmail.com> wrote:
>
>     On 5/15/23 00:32, Nikhil Ingale wrote:
>     > Hi All,
>     >
>     > The following query inserts the rows by ignoring the rows that has
>     conflicts.
>     >
>     > INSERT INTO test (id,name,age,branch) SELECT * FROM student ON
>     CONFLICT DO
>     > NOTHING;
>     >
>     > How do I capture the conflicting records to a file while
>     non conflicting
>     > records are inserted to the table?
>
>     On conflict insert the PK into a separate table, along with a timestamp
>     column populated by clock_timestamp().  (That way you can export and
>     delete
>     sets of records while it's being written to.
>
>     -- 
>     Born in Arizona, moved to Babylonia.
>
>

-- 
Born in Arizona, moved to Babylonia.
--------------hCe4pIZN2KI7jILkmBJNs38b
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    Maybe you can get something for nothing, but I'm dubious; you're
    going to have to pay a price <i>somewhere</i>.<br>
    <br>
    <div class="moz-cite-prefix">On 5/15/23 01:25, Nikhil Ingale wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CALXkTpxRGtUjH+GP67W_+fXahaUUmpTPE+LiLugF--_862PD-Q@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">Thing is there is a list of tables (350+ tables) on
        which I'm running the insert query i.e., INSERT INTO table ON
        CONFLICT DO NOTHING to continue inserting the records by
        ignoring the conflicting rows. But, at the same time I would
        like to capture the conflicting rows or every single conflicting
        column (not just the PK's) and their values for every single
        table.
        <div><br>
        </div>
        <div>INSERT INTO TABLE ON CONFLICT DO NOTHING don't even report
          us on the conflicting rows. How do I identify what the
          conflicting rows are by continuing the inserts to happen even
          if there are any conflicts. I mean my insert command shouldn't
          fail on conflicts but at the same time conflicts should be
          reported to the user.</div>
        <div><br>
        </div>
        <div>Regards,<br>
          Nik</div>
      </div>
      <br>
      <div class="gmail_quote">
        <div dir="ltr" class="gmail_attr">On Mon, May 15, 2023 at
          11:39 AM Ron &lt;<a href="mailto:ronljohnsonjr@gmail.com"
            moz-do-not-send="true" class="moz-txt-link-freetext">ronljohnsonjr@gmail.com</a>&gt;
          wrote:<br>
        </div>
        <blockquote class="gmail_quote" style="margin:0px 0px 0px
          0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On
          5/15/23 00:32, Nikhil Ingale wrote:<br>
          &gt; Hi All,<br>
          &gt;<br>
          &gt; The following query inserts the rows by ignoring the rows
          that has conflicts.<br>
          &gt;<br>
          &gt; INSERT INTO test (id,name,age,branch) SELECT * FROM
          student ON CONFLICT DO <br>
          &gt; NOTHING;<br>
          &gt;<br>
          &gt; How do I capture the conflicting records to a file while
          non conflicting <br>
          &gt; records are inserted to the table?<br>
          <br>
          On conflict insert the PK into a separate table, along with a
          timestamp <br>
          column populated by clock_timestamp().  (That way you can
          export and delete <br>
          sets of records while it's being written to.<br>
          <br>
          -- <br>
          Born in Arizona, moved to Babylonia.<br>
          <br>
          <br>
        </blockquote>
      </div>
    </blockquote>
    <br>
    <div class="moz-signature">-- <br>
      Born in Arizona, moved to Babylonia.</div>
  </body>
</html>

--------------hCe4pIZN2KI7jILkmBJNs38b--