Re: How to use long list of columns with COPY command

Hi,

I would suggest, that it's easier to create an empty tmp table, whose structure
matches your csv data file. Then copy (sql, server) or \copy (psql, client)
your data into the tmp table.

Then INSERT, what you need, from there:

insert into originaltable select a,b,c,.... from tmptable where ... ;        -- multi line statement allowed

or, in your case of 200/209 columns:

after COPYing your data, alter the tmp table, in that you drop the 9 extra columns.
It might be less work to write down 9 column names, than 200.

If , what is left, matches the structure of your original table, then the loading of
the original table reduces to:

insert into originaltable select * from tmptable where ... ;

cheers

/np

On Sun, Jun 11, 2023 at 11:04:30PM +0000, M Sarwar wrote:
> Hi Holger,
> I appreciate your response on the Sunday.
> I tried to follow PSQL link but I could not find what I want. I am finding new discoveries but the solution for my
issue.
> 
> This is my command now.
> 
> 
> \COPY bronx.TEST_GLOBAL_MCM_RAW (single_date_a, single_start_time_b, dual_part_m, dual_data_n ) FROM
C:\Mohammed\Work\NGC\Apps\Bronx\Tables\Test_TestData_MCM\Test_TestData_MCM-LoadData\N541652_IN3799A0_CLD_ABMN.csv
DELIMITER','  CSV Header ;
 
> 
> Entire above command is run from a single line.
> I need to accommodate all the below columns and few more  without losing  the legibility.
> 
> 
>    single_stop_time_c ,     single_test_action_d ,     single_part_type_e ,     single_system_id_f ,
single_fixture_id_g,     single_operator_id_h ,     single_run_id_i ,
 
> 
>     single_aux_id1_j ,     single_aux_id2_k ,      single_sn_l ,     dual_data_o ,     dual_data_p ,     dual_data_q
,    dual_data_r ,     dual_data_s ,     dual_data_t ,     dual_data_u ,
 
> 
>     dual_data_v ,     dual_data_w ,     dual_data_x ,     dual_data_y ,     dual_data_z ,     dual_data_aa ,
dual_data_ab,     dual_data_ac ,
 
> 
>     dual_data_ad ,       dual_data_ae ,     dual_data_af ,     dual_data_ag ,     dual_data_ah ,  dual_data_ai ,
dual_data_aj,  dual_data_ak ,  dual_data_al ,    dual_data_am ,   dual_data_an ,     dual_data_ao ,
 
> 
>     dual_data_ap ,     dual_data_aq ,     dual_data_ar ,    dual_data_as ,    dual_data_at ,    dual_data_au ,
dual_data_av,    dual_data_aw ,    dual_data_ax ,    dual_data_ay ,
 
> 
>     dual_data_az ,     dual_data_ba ,    dual_data_bb ,    dual_data_bc ,     dual_data_bd ,    dual_data_be ,
dual_data_bf,    dual_data_bg ,    dual_data_bh ,    dual_data_bi ,
 
> 
>     dual_data_bj ,    dual_data_bk ,    dual_data_bl ,    dual_data_bm ,    dual_data_bn ,    dual_data_bo ,
dual_data_bp,    dual_data_bq ,    dual_data_br ,    dual_data_bs ,
 
> 
>     dual_data_bt ,    dual_data_bu ,    dual_data_bv ,    dual_data_bw ,    dual_data_bx ,
> 
>     dual_data_by ,
> 
>     dual_data_bz ,
> 
> 
> 
> I have tried with \n option and that is not working.
> Thanks,
> Sarwar
> 
> 
> ________________________________
> From: Holger Jakobs
> Sent: Sunday, June 11, 2023 5:54 PM
> To: pgsql-admin@lists.postgresql.org
> Subject: Re: How to use long list of columns with COPY command
> 
> 
> 
> Am 11.06.23 um 23:44 schrieb M Sarwar:
> Hello,
> I have some problem while loading the data using COPY command.
> 
> My environment:
> Amazon RDS
> Postgres - Database
> PgAdmin --  with PSQL tool.
> Command -- COPY
> 
> I am trying to use COPY command to load the data. Table TEST_GLOBAL_MCM_RAW has 209 columns. In COPY command, I am
tryingto use around 200 columns to load the data.
 
> When I try to span COPY command into multiple lines, COPY command is not working. I am forced to use all the command
ina single line and which is not legible.
 
> Do I have any options to make the COPY command legible while using all the columns of any table.
> I have tried \n and this is not working.
> 
> It is possible that someone  has faced this situation in the past.
> 
> Thank you,
> Sarwar
> 
> 
> Hi Sarwar,
> 
> 
> Page https://www.postgresql.org/docs/15/app-psql.html says:
> 
> Another way to obtain the same result as \copy ... to is to use the SQL COPY ... TO STDOUT command and terminate it
with\g filename or \g |program. Unlike \copy, this method allows the command to span multiple lines; also, variable
interpolationand backquote expansion can be used.
 
> 
> 
> So, when loading FROM  a file using \COPY (you were writing about COPY, which allows line breaks like all SQL
commands),line breaks aren't allowed. It's the same for all backslash meta commands of psql.
 
> 
> 
> It seems that you'll have to write the complete \COPY command in one line. It's usually quite short, unless you have
toname a hell of a lot of column names.
 
> 
> 
> Regards,
> 
> Holger
> 
> 
> 
> 
> --
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
> 
>