pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedConditional query with copy command
4+ messages / 3 participants
[nested] [flat]
* Conditional query with copy command
@ 2020-11-26 08:27 Sachin Kumar <sachinkumaras@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Sachin Kumar @ 2020-11-26 08:27 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Conditional query with copy command
@ 2020-11-26 09:42 Holger Jakobs <holger@jakobs.com>
parent: Sachin Kumar <sachinkumaras@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Holger Jakobs @ 2020-11-26 09:42 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Conditional query with copy command
@ 2020-11-26 10:14 Sachin Kumar <sachinkumaras@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 4+ messages in thread
From: Sachin Kumar @ 2020-11-26 10:14 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
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> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Conditional query with copy command
@ 2020-11-26 18:46 DiasCosta <diascosta@diascosta.org>
parent: Sachin Kumar <sachinkumaras@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: DiasCosta @ 2020-11-26 18:46 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-11-26 18:46 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-26 08:27 Conditional query with copy command Sachin Kumar <sachinkumaras@gmail.com>
2020-11-26 09:42 ` Holger Jakobs <holger@jakobs.com>
2020-11-26 10:14 ` Sachin Kumar <sachinkumaras@gmail.com>
2020-11-26 18:46 ` DiasCosta <diascosta@diascosta.org>
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