Re: create batch script to import into postgres tables

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com