Re: create batch script to import into postgres tables - Mailing list pgsql-admin

On 6/18/20 9:40 AM, Pepe TD Vo wrote:
> I get this part that separates SQL script for import each table,
> 
> (import.sql)
> begin;
> \copy table_1 (c1, c2, c3) from '/path/tabledata1.csv' csv header;
> commit;
> 
> but when open the psql sql shell 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 shell 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\bin\psql -d PSCIDR -h localhost -p 5432 -U 
> postgres -i import.sql

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

> 
> 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 count(*) from cell_per';
Null display is "NULL".
  count
-------
     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(*) from cell_per;'
Null display is "NULL".
  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 in this world is pure and perfect.  If you avoid people for 
> their mistakes you will be alone. So judge less, love, and forgive 
> 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 generously **Care deeply **Speak kindly.
> *** Genuinely rich *** 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 this list is inline or
> bottom posting(https://en.wikipedia.org/wiki/Posting_style).
> 
>  > I have a Postgresql client installed and connected.  how can i create a
>  > batch script running from the client window?
> 
> Create a file with commands in it like the example from Christopher
> Browne that was posted earlier:
> 
> "There is no single straightforward answer to that.
> 
> 
> 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 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;
> 
> 
> But you may be fine with having a separate SQL script for each table.
> 
> 
> There will be conditions where one or the other is more appropriate, and
> that will be based on the requirements of the process."
> 
> Then point psql at it:
> 
> psql -d some_db -h some_host -U some_user -f the_file
> 
> 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.
> 
> 
>  >
>  > **
>  > *Bach-Nga
> 
>  >
> 
> 
> 
> -- 
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com