Subject: Re: Conditional query with copy command
To: pgsql-admin@lists.postgresql.org
References: 
 <CALg-PKDeVkeia_6Dv-_Tc0wm4qQVYPioe9i+D-FhERx84aKEzw@mail.gmail.com>
 <f05f4e12-8eea-62fe-4a7b-3e5a0298aaf0@jakobs.com>
 <CALg-PKAM7rexK7fbCc9tBA4g=twPNxouRk6=OjJy0sgkNemkbw@mail.gmail.com>
From: DiasCosta <diascosta@diascosta.org>
Message-ID: <5155df1c-df47-e11c-4aee-a461171ead65@diascosta.org>
Date: Thu, 26 Nov 2020 18:46:42 +0000
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
 Thunderbird/78.5.0
MIME-Version: 1.0
In-Reply-To: 
 <CALg-PKAM7rexK7fbCc9tBA4g=twPNxouRk6=OjJy0sgkNemkbw@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------C02779E2804B8158B0C56243"
Content-Language: en-GB
Precedence: bulk

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

Hi Sachin Kumar,

To resolve a situation similar to yours but with more than 70000000 
records in a CSV file for each month
in the year (with validation records in public transport), I used the 
following method:
1 - I created the file_fdw extension and a foreign data wraper
2 - I created a foreign table (FT) with the appropriate structure to 
support all fields in the CSV file (they can all
be of the varchar type). I defined a name for that FT that could be used 
for all months of the year without
needing to change the table definition
3 - I created the import destination table for each month's records with 
the appropriate column structure.
4 - I created the insertion instruction in the destination table from 
the selection of the FT, using,  eventually,
some restriction clause. I filled in some columns in the target table at 
the expense of expressions in the select
clause of the statement.
5 - Assuming that the structure of the CSV file is the same in each of 
the following months, I only need to
name the source file with the name used in the options of the definition 
of the destination FT.

Dias Costa

------------------------------------------------------------------------------------


On 26-11-2020 10:14, Sachin Kumar wrote:
> Thanks Mr. Holger,
>
> For the Quick reply. We are using Ver 12 and 13 so this query will 
> work for us.
>
> One more if you can guide on Archiving old records from PostgreSQL 
> Table and Retrieving it whenever required.
> Any Documentation or example on archiving records.
>
> On Thu, Nov 26, 2020 at 3:13 PM Holger Jakobs <holger@jakobs.com 
> <mailto:holger@jakobs.com>> wrote:
>
>     Hi,
>
>     Use the new syntax as of PostgreSQL 9.0:
>
>     copy hk_card_master_test from
>     'C:/inetpub/wwwroot/cards/media/static/gc_card_master2L.csv'
>     (format csv, header, encoding 'ISO_8859_5') where card_status = 'A'
>
>     This allows a simple WHERE condition as shown.
>
>
>     Am 26.11.20 um 09:27 schrieb Sachin Kumar:
>>     Hi Experts,
>>
>>     I am uploading 10 million data using CSV, but I want to put a
>>     condition on a column (Card_Status) inside the CSV that only
>>     where Card_Status = A data should be uploaded rest data are not
>>     uploaded.
>>     Please guide me in modifying the query I am using to upload the data
>>     Query:-
>>     copy hk_card_master_test from
>>     'C:/inetpub/wwwroot/cards/media/static/gc_card_master2L.csv' with
>>     delimiter ',' csv header encoding 'ISO_8859_5'
>>
>>     -- 
>>
>>     Best Regards,
>>     Sachin Kumar
>
>     -- 
>     Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>
>
>
> -- 
>
> Best Regards,
> Sachin Kumar


-- 
J. M. Dias Costa
Telef. 214026948

Se divulgar esta mensagem por terceiros, por favor:
1. Apague o meu endereço de correio electrónico e o meu nome.
2. Apague também os endereços dos seus amigos antes de distribuir.
3. Enderece como cópia oculta (Cc ou Bcc) para os SEUS destinatários.
Agindo deste modo, dificultará a disseminação de "vírus", "spams" e
"banners" e contribuirá para manter a privacidade de todos e cada um.
Obrigado.

Nota: Não se deverá ao acaso a ocorrência de palavras na minha escrita que não respeitem o
malfadado acordo ortográfico.


--------------C02779E2804B8158B0C56243
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 text="#0000ff" bgcolor="#ffffff">
    <div class="moz-cite-prefix">Hi Sachin Kumar,<br>
      <br>
      To resolve a situation similar to yours but with more than
      70000000 records in a CSV file for each month <br>
      in the year (with validation records in public transport), I used
      the following method:<br>
      1 - I created the file_fdw extension and a foreign data wraper<br>
      2 - I created a foreign table (FT) with the appropriate structure
      to support all fields in the CSV file (they can all <br>
      be of the varchar type). I defined a name for that FT that could
      be used for all months of the year without <br>
      needing to change the table definition<br>
      3 - I created the import destination table for each month's
      records with the appropriate column structure.<br>
      4 - I created the insertion instruction in the destination table
      from the selection of the FT, using,  eventually, <br>
      some restriction clause. I filled in some columns in the target
      table at the expense of expressions in the select <br>
      clause of the statement.<br>
      5 - Assuming that the structure of the CSV file is the same in
      each of the following months, I only need to <br>
      name the source file with the name used in the options of the
      definition of the destination FT.<br>
      <br>
      Dias Costa<br>
      <br>
------------------------------------------------------------------------------------<br>
      <br>
      <br>
      <br>
      On 26-11-2020 10:14, Sachin Kumar wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CALg-PKAM7rexK7fbCc9tBA4g=twPNxouRk6=OjJy0sgkNemkbw@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">Thanks Mr. Holger,
        <div><br>
        </div>
        <div>For the Quick reply. We are using Ver 12 and 13 so this
          query will work for us.</div>
        <div><br>
        </div>
        <div>One more if you can guide on Archiving old records from
          PostgreSQL Table and Retrieving it whenever required.</div>
        <div>Any Documentation or example on archiving records.</div>
        <img src="http://s.bl-1.com/LKcWKF3.gif" moz-do-not-send="true"
          width="1" height="1"></div>
      <br>
      <div class="gmail_quote">
        <div dir="ltr" class="gmail_attr">On Thu, Nov 26, 2020 at 3:13
          PM Holger Jakobs &lt;<a href="mailto:holger@jakobs.com"
            moz-do-not-send="true">holger@jakobs.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">
          <div>
            <p>Hi,</p>
            <p>Use the new syntax as of PostgreSQL 9.0:</p>
            <p>copy hk_card_master_test from
              'C:/inetpub/wwwroot/cards/media/static/gc_card_master2L.csv'
              (format csv, header, encoding 'ISO_8859_5') where
              card_status = 'A'</p>
            <p>This allows a simple WHERE condition as shown.</p>
            <p><br>
            </p>
            <div>Am 26.11.20 um 09:27 schrieb Sachin Kumar:<br>
            </div>
            <blockquote type="cite">
              <div dir="ltr">Hi Experts,
                <div><br>
                </div>
                <div>I am uploading 10 million data using CSV, but I
                  want to put a condition on a column (Card_Status)
                  inside the CSV that only where Card_Status = A data
                  should be uploaded rest data are not uploaded.<br>
                  Please guide me in modifying the query I am using to
                  upload the data<br>
                  Query:- </div>
                <div>copy hk_card_master_test from
                  'C:/inetpub/wwwroot/cards/media/static/gc_card_master2L.csv'
                  with delimiter ',' csv header encoding 'ISO_8859_5'<br
                    clear="all">
                  <div><br>
                  </div>
                  -- <br>
                  <div dir="ltr"><br>
                    Best Regards, <br>
                    Sachin Kumar<br>
                  </div>
                </div>
                <img src="http://s.bl-1.com/LKbJdfd.gif"
                  moz-do-not-send="true" width="1" height="1"></div>
            </blockquote>
            <pre cols="72">-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012</pre>
          </div>
        </blockquote>
      </div>
      <br clear="all">
      <div><br>
      </div>
      -- <br>
      <div dir="ltr" class="gmail_signature"><br>
        Best Regards, <br>
        Sachin Kumar<br>
      </div>
    </blockquote>
    <br>
    <br>
    <pre class="moz-signature" cols="72">-- 
J. M. Dias Costa
Telef. 214026948

Se divulgar esta mensagem por terceiros, por favor:
1. Apague o meu endereço de correio electrónico e o meu nome.
2. Apague também os endereços dos seus amigos antes de distribuir.
3. Enderece como cópia oculta (Cc ou Bcc) para os SEUS destinatários.
Agindo deste modo, dificultará a disseminação de "vírus", "spams" e
"banners" e contribuirá para manter a privacidade de todos e cada um.
Obrigado.

Nota: Não se deverá ao acaso a ocorrência de palavras na minha escrita que não respeitem o 
malfadado acordo ortográfico.</pre>
  </body>
</html>

--------------C02779E2804B8158B0C56243--