Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jm0cf-0003tL-Ug for pgsql-admin@arkaria.postgresql.org; Thu, 18 Jun 2020 19:54:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jm0ce-0001PV-Rh for pgsql-admin@arkaria.postgresql.org; Thu, 18 Jun 2020 19:54:52 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jm0ce-0001PO-5M for pgsql-admin@lists.postgresql.org; Thu, 18 Jun 2020 19:54:52 +0000 Received: from sonic306-2.consmr.mail.bf2.yahoo.com ([74.6.132.41]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jm0ca-0002eU-LX for pgsql-admin@postgresql.org; Thu, 18 Jun 2020 19:54:50 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1592510087; bh=f3Xp5dhxM1IzgbvEpfaJIzfU2BrJApjEQOUESbNyFRE=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=radjby52vdmtCmPXCk2g7yPpob5p8ATb9UbDLtetvmBC3Sz4eBuLZAC17koHXybLtAX6RIdf/5ZuKOjkN/+tou/MuqSOw8wlvgbjd9ZxmWznDoKDBl+dcGR+3KJUPawj9jLuza1LlNzNG5RKaGq8p8Ct/14O/1ZsEww45LRpWqZODOMsoaCElUn0wicY+x3llMWzSu8I6Hm9AaCLaxgFhgSBoXJDmRQsy6Qv9J3BYEIJfxUl0BNVtke9kE8goqYaGSMwjfJFBhVhZtb9iR2dQzv9toC9oiM60970cXtDYpXDfFrseFxeVigiF1u3UWacC2t+nTb1gc7oVl0khBgfTQ== X-YMail-OSG: YgKp4ZcVM1lYf1PKeIjBsoX9cXM3fWYVfcQrKJhKHzSuYkSlOFRsfWO2EpsJwoE NV.FGIFvLY9hfbtsPjlzMp36QZ.vT97D.CqkE_nhg1sLiJDMGOwI87TUyzJKZdFjZHeif0oXb1xG .xfj5Dzzgziqp0yN12lEkTwfiOlFaeB8T8PtMmn1uRoJ4estPxNUkNEiavLBF3oZVQ5k1liDqos3 HNvUEtKvu2JOfsamkgfEKOx6WYBF6DxyvVJzHgL.Q2IyGoIeod9hbhPbzdzcPkbBBlKse3186FM5 nGEbjF7o35gFvS1Qmz0VMWTKq__6DR8YNs42pY1vOvJKlcOoVypq1Uv5Abvb3tjy3Z.rAt.lco3M 6Miwm3GX6PJcd66mZgiXXTzbSSogliz85xzCZEHJvar7jWALlS1DDjuui8LGoVKLRQ1h3AazyYpl dy3f2gsLINItdgI9RfrRMk6k6_PaXT8ZnjUh.R_W2cNJOkkzu4gQ0VyFwCjzvSQjCPV2DYZ3qNAK GngBlTYLwy71_yjONQRbsdfCw4t.iBA0mjp_Lr8IGPKFEpSfaOi6PmFGIWJCqoJybyE7y.veBMsF BsJ1P_lAjo0TW882l_ti2SnsIEef9uuKiP0I420g621CIctu_HqnAjyTZOwQPlO.QL6s9_Cw.xcW YydYekN0.BfMt5RSxqZ2YyNccr8jg4MCcb6.bCeWI73yqF0Nz9j1TON5p.zWEwcvm0vhf1XKv8LC uUbUvMd7IYYEFQ5mxF1VIOzo4dwuk4fUPgBwfraIcVPwYJbtv14pdoai.RsdDeaF5JH96zPmyTAW 9UyFISHzcfCCw7TAuOO6vyIN8fyVXLFPDTmJsEhJsyp2lt5aMyTf66qR_yllYwaI95DZJtQcnTDA lKfC8EAv3str1W9D1c1RUv6HrTgKZIISHZYNcjoY3D0oIBoTLh5aHqhZB2q7vGXk5XgQ31BYxG3N EwjZojJ2hxtjD1F7shNC5JmuoItjdE7_8xFut7ljM4vw0dq.btAz3nXEiOLVomi2XMxPhtfPWCON TPgur8BfESDGArD1LRzy0yXTNlJuWv63YT_C0hhVWs19mK2QUaCJJnkJpFQ9QxhsYkYytcmq3hxp GF1uVi7n7B_DHIW0H8maMYiknpY3i12YGYH_1aHBo58mVN9x.rVlVuC7A7ZP1FcOi7x4LsmgK79o QnXs_X0BIXB6liC1Wc4RbUBQvmOlxwknmhCt5dATXcFDCbnvh8p3DTWFehX7qEiSt7whXfAw.j7l HpnhUMvGo3g71IdkHYNjfsDQ5FYTRtTqb4EyxZ.GXlsoztxB6s2WraNZhKS.UwtrT54gMcacgn3. CRTShyHwzsIGpVGKWVvGahLiq_28GuzHVsajfLEW9B40ZWAadmdMDxnkidUNQ Received: from sonic.gate.mail.ne1.yahoo.com by sonic306.consmr.mail.bf2.yahoo.com with HTTP; Thu, 18 Jun 2020 19:54:47 +0000 Date: Thu, 18 Jun 2020 19:54:44 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Christopher Browne , Adrian Klaver Cc: Pgsql-admin , Pgsql-general Message-ID: <889466258.267016.1592510085000@mail.yahoo.com> In-Reply-To: <4d5841c4-9a6c-0e20-2c05-c21d3d3942df@aklaver.com> References: <866581879.1147164.1592317256828.ref@mail.yahoo.com> <866581879.1147164.1592317256828@mail.yahoo.com> <1f5f4fd6-ad22-4823-c922-17a021c2c0d7@aklaver.com> <74046808.1129536.1592317823808@mail.yahoo.com> <3625e291-8456-3831-6205-2d73232bcd1e@aklaver.com> <1718146430.1151531.1592319564343@mail.yahoo.com> <1761277119.1163490.1592322138731@mail.yahoo.com> <732982585.145357.1592493607620@mail.yahoo.com> <1814718409.184531.1592498400894@mail.yahoo.com> <4d5841c4-9a6c-0e20-2c05-c21d3d3942df@aklaver.com> Subject: Re: create batch script to import into postgres tables MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_267015_322467339.1592510084996" X-Mailer: WebService/1.1.16138 YMailNorrin Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.106 Safari/537.36 Content-Length: 24272 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_267015_322467339.1592510084996 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable >>psql -d production=C2=A0 -U postgres -c 'select count(*) from cell_per'; Null display is "NULL". =C2=A0 count ------- =C2=A0 =C2=A0 68 (1 row) you can do this once you are in psql. But if you are running from shell scr= ipt, it will be an error >>What error messages do you get? my shell script is:@echo off=C2=A0C:\Program Files\PostgreSQL\11\bin\psql -= U PSmasteruser=C2=A0 -d PSCIDR -h hostname.amazonaws.com -p 5432 -c "select= count(*) from tableA;" pause the error I have is 'C:\Program' is not recognized as an internal or extern= al command, operable program or batch file. I even surround the path in quotes because of space.@echo off=C2=A0cmd /c "= "C:\Program Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h host= name.amazonaws.com -p 5432 -c "select count(*) from tableA;" pause error is:=C2=A0'C:\Program Files\PostgreSQL\11\bin\psql: illegal option -- = u when I put -P Password123 (or fully qualified password=3DPassword123) it gives me another error "pset: unknow option: Password123psql: could not = set printing parameter "Password123" I can connect from psql shell fine when it prompt hostname, username (but c= onnect to postgres not to PSmasteruser, PSmasteruser username set up for aw= s maintenance postgres database, someone did, not me and the password is sa= me on both).=C2=A0 Once I'm in psql and I can change to PSCIDR instance fin= e and run select count(*) from tableA; Bach-Nga No one in this world is pure and perfect.=C2=A0 If you avoid people for the= ir mistakes you will be alone. So judge less, love, and forgive more.To cal= l him a dog hardly seems to do him justice though in as much as he had four= legs, a tail, and barked, I admit he was, to all outward appearances. But = to those who knew him well, he was a perfect gentleman (Hermione Gingold) **Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely = rich *** Faithful talent *** Sharing success =20 On Thursday, June 18, 2020, 03:06:39 PM EDT, Adrian Klaver wrote: =20 =20 On 6/18/20 9:40 AM, Pepe TD Vo wrote: > I get this part that separates SQL script for import each table, >=20 > (import.sql) > begin; > \copy table_1 (c1, c2, c3) from '/path/tabledata1.csv' csv header; > commit; >=20 > but when open the psql sql shell script it prompts line by line for=20 > localhost, port, db, user, and password.=C2=A0 If I set up a script and l= et=20 > it run it won't connect to the postgresql instance.=C2=A0 I want to know = how=20 > to execute a batch script connect to the database/instance. > In oracle I created a shell script with all oracle_sid, oracle_home, and= =20 > read the function/procedure... for psql, especially from window client,= =20 > I did put psql_home and connect to the instance, it failed >=20 > c:\Progra~1\PostgreSQL\11\bin\psql -d PSCIDR -h localhost -p 5432 -U=20 > postgres -i import.sql The above should be -f import.sql. AFAIK there is no -i for psql, so=20 that should be failing. >=20 > even I do a simple count >=20 > c:\Progra~1\PostgreSQL\11\bin\psql -d PSCIDR -h localhost -p 5432 -U=20 > postgres -c "select count(*) from tableA"; psql -d production=C2=A0 -U postgres -c 'select count(*) from cell_per'; Null display is "NULL". =C2=A0 count ------- =C2=A0 =C2=A0 68 (1 row) psql -d production=C2=A0 -U postgres -c 'select count(*) from cell_per' Null display is "NULL". =C2=A0 count ------- =C2=A0 =C2=A0 68 psql -d production=C2=A0 -U postgres -c 'select count(*) from cell_per;' Null display is "NULL". =C2=A0 count ------- =C2=A0 =C2=A0 68 >=20 > none of them is work. Try to learn how to execute its script. What error messages do you get? >=20 > ** > *Bach-Nga >=20 > *No one in this world is pure and perfect.=C2=A0 If you avoid people for= =20 > their mistakes you will be alone. So judge less, love, and forgive=20 > more.EmojiEmojiEmoji > To call him a dog hardly seems to do him justice though in as much as he= =20 > had four legs, a tail, and barked, I admit he was, to all outward=20 > appearances. But to those who knew him well, he was a perfect gentleman= =20 > (Hermione Gingold) >=20 > **Live simply **Love generously **Care deeply **Speak kindly. > *** Genuinely rich *** Faithful talent *** Sharing success >=20 >=20 >=20 >=20 > On Thursday, June 18, 2020, 12:08:44 PM EDT, Adrian Klaver=20 > wrote: >=20 >=20 > On 6/18/20 8:20 AM, Pepe TD Vo wrote: >=20 > Please don't top post. The preferred style on this list is inline or > bottom posting(https://en.wikipedia.org/wiki/Posting_style). >=20 >=C2=A0 > I have a Postgresql client installed and connected.=C2=A0 how can= i create a >=C2=A0 > batch script running from the client window? >=20 > Create a file with commands in it like the example from Christopher > Browne that was posted earlier: >=20 > "There is no single straightforward answer to that. >=20 >=20 > Supposing I want a batch to either all be processed, or to all not proces= s, > then I might write a sql file like: >=20 >=20 > begin; > \copy table_1 (c1, c2, c3) from '/path/tabledata1.csv' csv header; > \copy table_2 (c1, c2, c3) from '/path/tabledata2.csv' csv header; > \copy table_3 (c1, c2, c3) from '/path/tabledata3.csv' csv header; > commit; >=20 >=20 > But you may be fine with having a separate SQL script for each table. >=20 >=20 > There will be conditions where one or the other is more appropriate, and > that will be based on the requirements of the process." >=20 > Then point psql at it: >=20 > psql -d some_db -h some_host -U some_user -f the_file >=20 > Be aware that \copy is all or nothing. If there is a single failure in > the copying the whole copy will rollback. Given that the one file per > table might be preferable. >=20 >=20 >=C2=A0 > >=C2=A0 > ** >=C2=A0 > *Bach-Nga >=20 >=C2=A0 > >=20 >=20 >=20 > --=20 > Adrian Klaver > adrian.klaver@aklaver.com >=20 >=20 >=20 --=20 Adrian Klaver adrian.klaver@aklaver.com =20 ------=_Part_267015_322467339.1592510084996 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
>>psql -d production&n= bsp; -U postgres -c 'select count(*) from cell_per';
Nul= l display is "NULL".
  count
---= ----
    68
(1 row)<= br clear=3D"none" style=3D"font-family: Helvetica Neue, Helvetica, Arial, s= ans-serif;">
you can do this once you are in psql. But if you a= re running from shell script, it will be an error

>>What error messages do you get?

my sh= ell script is:
@echo off <= /div>
C:\Program F= iles\PostgreSQL\11\bin\psql -U PSmasteruser  -d PSCIDR = -h hostname.amazonaws.com -p 5432 -c "select count(*) from tableA;"
pause

=
the error I have is 'C:\Pro= gram' is not recognized as an internal or = external command, operable program or batch file.
<= span>
I even surround the path in quotes because of space.
@echo off 
cmd /c ""C:\Program Files\Post= greSQL\11\bin\psql" -U PSmasteruser = -d PSCIDR -h hostname.amazonaws.com -p 5432 -c "select count(*) from tableA= ;"
pause

error is: 'C:\Program Files\PostgreSQL\11\bin\psql: illegal option -- u

when I put -P Password123 (or ful= ly qualified password=3DPassword123)

it gives me another = error "pset: unknow option: Password123
psql: could not set printing= parameter "Password123"

I can connect from psql shell fine when it prompt hostname, usernam= e (but connect to postgres not to PSmasteruser, PSmasteruser username set u= p for aws maintenance postgres database, someone did, not me and the passwo= rd is same on both).  Once I'm in psql and I can change to PSCIDR inst= ance fine and run select count(*) from tableA;


=

Bach-N= ga

No one in this world is pure an= d perfect.  If you avoid people for their mistakes you will be alone. = So judge less, love, and forgive more.3D"Emoji"3D"Emoji"3D"Emoj=
To call him a dog hardly seem= s to do him justice though in as much as he had four legs, a tail, and bark= ed, I admit he was, to all outward appearances. But to those who knew him w= ell, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **= Care deeply **Speak kindly.
*** Genuinely r= ich *** Faithful talent *** Sharing success

<= div id=3D"ydpf8dd9bc5yui_3_13_0_rc_1_1_1381789421038_7866" class=3D"ydpf8dd= 9bc5yiv3811552299ms__id13894" dir=3D"ltr">


=20
=20
On Thursday, June 18, 2020, 03:06:39 PM EDT, Adrian Kla= ver <adrian.klaver@aklaver.com> wrote:


On 6/18/20 9:40 AM, Pepe TD Vo wrote:=
> I get this part that separates SQL script for impor= t each table,
>
> (import.sql)> begin;
> \copy table_1 (c1, c2, = c3) from '/path/tabledata1.csv' csv header;
> commit;<= br clear=3D"none">>
> but when open the psql sql s= hell script it prompts line by line for
> localhost, = port, db, user, and password.  If I set up a script and let
> it run it won't connect to the postgresql instance.  I = want to know how
> to execute a batch script connect = to the database/instance.
> In oracle I created a shel= l script with all oracle_sid, oracle_home, and
> read= the function/procedure... for psql, especially from window client,
> I did put psql_home and connect to the instance, it failed=
>
> c:\Progra~1\PostgreSQL\11\b= in\psql -d PSCIDR -h localhost -p 5432 -U
> postgres = -i import.sql

The above should be -f i= mport.sql. AFAIK there is no -i for psql, so
that should= be failing.

>
&= gt; even I do a simple count
>
>= c:\Progra~1\PostgreSQL\11\bin\psql -d PSCIDR -h localhost -p 5432 -U
> postgres -c "select count(*) from tableA";

psql -d production  -U postgres -c 'select cou= nt(*) from cell_per';
Null display is "NULL".
  count
-------
  &nbs= p; 68
(1 row)

psql = -d production  -U postgres -c 'select count(*) from cell_per'
Null display is "NULL".
  count
-------
    68
psql -d production  -U postgres -c 'select count(*) fr= om cell_per;'
Null display is "NULL".
&= nbsp; count
-------
    68

>
> none of them is work. Try to learn how to execute its script.

What error messages do you get?

>
> **
> *Bach-Nga
>
> *No one i= n this world is pure and perfect.  If you avoid people for
> their mistakes you will be alone. So judge less, love, and f= orgive
> more.EmojiEmojiEmoji
> = To call him a dog hardly seems to do him justice though in as much as he > had four legs, a tail, and barked, I admit he was, to= all outward
> appearances. But to those who knew him= well, he was a perfect gentleman
> (Hermione Gingold= )
>
> **Live simply **Love gener= ously **Care deeply **Speak kindly.
> *** Genuinely ri= ch *** Faithful talent *** Sharing success
>
>
>
>
> On Thursday, June 18, 2020, 12:08:44 PM EDT, Adrian Klaver
> <adrian.klaver@aklaver= .com> wrote:
>
>
> On 6/18/20 8:20 AM, Pepe TD Vo wrote:
>= ;
> Please don't top post. The preferred style on thi= s list is inline or
> bottom posting(htt= ps://en.wikipedia.org/wiki/Posting_style).
>
>  > I have a Postgresql client installed and conn= ected.  how can i create a
>  > batch scr= ipt running from the client window?
>
> Create a file with commands in it like the example from Christopher=
> Browne that was posted earlier:
&= gt;
> "There is no single straightforward answer to t= hat.
>
>
>= Supposing I want a batch to either all be processed, or to all not process= ,
> then I might write a sql file like:
>
>
> begin;
> \copy table_1 (c1, c2, c3) from '/path/tabledata1.csv' csv head= er;
> \copy table_2 (c1, c2, c3) from '/path/tabledata= 2.csv' csv header;
> \copy table_3 (c1, c2, c3) from '= /path/tabledata3.csv' csv header;
> commit;
>
>
> But you may = be fine with having a separate SQL script for each table.
>
>
> There will be conditi= ons where one or the other is more appropriate, and
> = that will be based on the requirements of the process."
&= gt;
> Then point psql at it:
> <= br clear=3D"none">> psql -d some_db -h some_host -U some_user -f the_fil= e
>
> Be aware that \copy is all= or nothing. If there is a single failure in
> the cop= ying the whole copy will rollback. Given that the one file per
> table might be preferable.
>
>
>  >
>  &= gt; **
>  > *Bach-Nga
> <= br clear=3D"none">>  >
>
>
>
> --
> Adrian Klaver
> ad= rian.klaver@aklaver.com <mailto:adrian= .klaver@aklaver.com>
<= br clear=3D"none">>
>
>


--
= Adrian Klaver
adrian.klaver= @aklaver.com


------=_Part_267015_322467339.1592510084996--