Re: create batch script to import into postgres tables
От | Adrian Klaver |
---|---|
Тема | Re: create batch script to import into postgres tables |
Дата | |
Msg-id | 4d5841c4-9a6c-0e20-2c05-c21d3d3942df@aklaver.com обсуждение исходный текст |
Ответ на | Re: create batch script to import into postgres tables (Pepe TD Vo <pepevo@yahoo.com>) |
Ответы |
Re: create batch script to import into postgres tables
Re: create batch script to import into postgres tables |
Список | 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
В списке pgsql-admin по дате отправления: