From: DiasCosta <diascosta@diascosta.org>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Conditional query with copy command
Date: Thu, 26 Nov 2020 18:46:42 +0000
Message-ID: <5155df1c-df47-e11c-4aee-a461171ead65@diascosta.org> (raw)
In-Reply-To: <CALg-PKAM7rexK7fbCc9tBA4g=twPNxouRk6=OjJy0sgkNemkbw@mail.gmail.com>
References: <CALg-PKDeVkeia_6Dv-_Tc0wm4qQVYPioe9i+D-FhERx84aKEzw@mail.gmail.com>
<f05f4e12-8eea-62fe-4a7b-3e5a0298aaf0@jakobs.com>
<CALg-PKAM7rexK7fbCc9tBA4g=twPNxouRk6=OjJy0sgkNemkbw@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: diascosta@diascosta.org, pgsql-admin@lists.postgresql.org
Subject: Re: Conditional query with copy command
In-Reply-To: <5155df1c-df47-e11c-4aee-a461171ead65@diascosta.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox