psql copy from through bash

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 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-general

Hi 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 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-general

Hi 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 01/11/2013 11:32 AM, Kirk Wythers wrote:

On Jan 11, 2013, at 12:18 PM, Szymon Guz <mabewlun@gmail.com
<mailto:mabewlun@gmail.com>> wrote:

On 11 January 2013 19:13, Kirk Wythers <kwythers@umn.edu
<mailto: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
<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi 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