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:
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> 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.
В списке pgsql-admin по дате отправления: