Обсуждение: psql copy from through bash
Can anyone see what I'm misisng? I am trying to run a psql "copy from" command through a bash script to load a buch of cdvfiles into the same table. I'm getting an error about the file "infile" not existing? #!/bin/sh for infile in /path_to_files/*.csv do cat infile | psql dbname -c "\copy table_name FROM stdin with delimiter as ',' NULL AS 'NA' CSV HEADER" done Thanks in advance
On 11 January 2013 19:13, Kirk Wythers <kwythers@umn.edu> wrote:
Can anyone see what I'm misisng? I am trying to run a psql "copy from" command through a bash script to load a buch of cdv files into the same table. I'm getting an error about the file "infile" not existing?
#!/bin/sh
for infile in /path_to_files/*.csv
do
cat infile | psql dbname -c "\copy table_name FROM stdin with delimiter as ',' NULL AS 'NA' CSV HEADER"
done
Thanks in advance
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi Kirk,
maybe try this:
cat $infile |
- Szymon
On 11 January 2013 19:32, Kirk Wythers <wythe001@umn.edu> wrote:
On Jan 11, 2013, at 12:18 PM, Szymon Guz <mabewlun@gmail.com> wrote:On 11 January 2013 19:13, Kirk Wythers <kwythers@umn.edu> wrote:Can anyone see what I'm misisng? I am trying to run a psql "copy from" command through a bash script to load a buch of cdv files into the same table. I'm getting an error about the file "infile" not existing?
#!/bin/sh
for infile in /path_to_files/*.csv
do
cat infile | psql dbname -c "\copy table_name FROM stdin with delimiter as ',' NULL AS 'NA' CSV HEADER"
done
Thanks in advance
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-generalHi Kirk,maybe try this:cat $infile |Oh my goodness! Thanks you.Once more quickie. It seems that I am going to be asked for my password every time psql loops through the copy statement.What is considered best practices to handle authentication? I am connecting locally, as myself as the user and I'm being asked for my user password. I added the -w (no-password) to the psql statement, but now assume I need to add a .pgpass file or something.Suggestions?
Add the password to ~/.pgpass http://www.postgresql.org/docs/9.1/static/libpq-pgpass.html
Szymon
Hello >> >> Once more quickie. It seems that I am going to be asked for my password >> every time psql loops through the copy statement. >> >> What is considered best practices to handle authentication? I am >> connecting locally, as myself as the user and I'm being asked for my user >> password. I added the -w (no-password) to the psql statement, but now assume >> I need to add a .pgpass file or something. >> >> Suggestions? >> > > Add the password to ~/.pgpass > http://www.postgresql.org/docs/9.1/static/libpq-pgpass.html or PGPASSWORD=mypassword psql database -c "copy ..." Regards Pavel > > Szymon
Kirk Wythers <kwythers@umn.edu> writes: > Can anyone see what I'm misisng? I am trying to run a psql "copy from" command through a bash script to load a buch ofcdv files into the same table. I'm getting an error about the file "infile" not existing? > > #!/bin/sh > > for infile in /path_to_files/*.csv > do > cat infile | psql dbname -c "\copy table_name FROM stdin with delimiter as ',' NULL AS 'NA' CSV HEADER" > done Well, I don't know what else could be wrong but suggest you get rid of the backslash as in \copy and just say COPY which is the SQL command. \copy is a psql macro and I'm not sure it's appropriate here. And you win the "useless use of cat award" here too. psql ... <infile Of course one good reason for preferring cat is that you'll never type > by mistake instead of < and clobber your data. Er, some shells have a no-clobber option though. HTH > Thanks in advance > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800
On Jan 11, 2013, at 12:18 PM, Szymon Guz <mabewlun@gmail.com> wrote:
On 11 January 2013 19:13, Kirk Wythers <kwythers@umn.edu> wrote:Can anyone see what I'm misisng? I am trying to run a psql "copy from" command through a bash script to load a buch of cdv files into the same table. I'm getting an error about the file "infile" not existing?
#!/bin/sh
for infile in /path_to_files/*.csv
do
cat infile | psql dbname -c "\copy table_name FROM stdin with delimiter as ',' NULL AS 'NA' CSV HEADER"
done
Thanks in advance
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-generalHi Kirk,maybe try this:cat $infile |
Oh my goodness! Thanks you.
Once more quickie. It seems that I am going to be asked for my password every time psql loops through the copy statement.
What is considered best practices to handle authentication? I am connecting locally, as myself as the user and I'm being asked for my user password. I added the -w (no-password) to the psql statement, but now assume I need to add a .pgpass file or something.
Suggestions?
On 01/11/2013 11:32 AM, Kirk Wythers wrote:
On Jan 11, 2013, at 12:18 PM, Szymon Guz <mabewlun@gmail.com> wrote:On 11 January 2013 19:13, Kirk Wythers <kwythers@umn.edu> wrote:Can anyone see what I'm misisng? I am trying to run a psql "copy from" command through a bash script to load a buch of cdv files into the same table. I'm getting an error about the file "infile" not existing?
#!/bin/sh
for infile in /path_to_files/*.csv
do
cat infile | psql dbname -c "\copy table_name FROM stdin with delimiter as ',' NULL AS 'NA' CSV HEADER"
done
Thanks in advance
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-generalHi Kirk,maybe try this:cat $infile |Oh my goodness! Thanks you.Once more quickie. It seems that I am going to be asked for my password every time psql loops through the copy statement.What is considered best practices to handle authentication? I am connecting locally, as myself as the user and I'm being asked for my user password. I added the -w (no-password) to the psql statement, but now assume I need to add a .pgpass file or something.Suggestions?
Yes a .pgpass file would work nicely