Re: Conditional query with copy command

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(at)jakobs(dot)com
> <mailto:holger(at)jakobs(dot)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.