Re: Need help with bash script and postgresql

First, you can specify a password with -P (I think --password works
also). psql --help for optins.

Usually the DB defaults to trusting everything local (if I'm not
mistaken -- someone please correct me if I'm over simplifying).

As far as getting your data into the database, I would look at \copy
in the psql command:

http://www.postgresql.org/docs/8.2/static/app-psql.html

Depending upon the format of the input file, it should be able to
just suck it in.

Doing it from a script seems error prone. If you separate the
columns with commas and do not have any white space you could do
something like:

cat myfile.txt | ( IFS=, while read a b; do psql -d mydatabase -c
"insert into mytable (aaa, bbb) values ( '$a' , '$b' )" ; done

The cat presents the file on stdin. We drop into a subshell so the
IFS will take effect (not sure this is required). We set IFS to
comma (or whatever you have separating your fields). We then loop
reading each line into variables a and b. Then we insert this into
the table. There are a lot of gotchas with this. For example, if a
column is an integer, you would not want to put the single quotes
around the expansion of the variable (e.g. ( $a, '$b' ) if aaa was an
integer and bbb was a string.

The \copy knows all this I bet. I've not used it but my guess is
that it is fairly robust.

In that case, you would simply do something like:

psql -d mydatabase -c "\copy mytable(aaa,bbb) from myfile.txt" <==
note the backslash before the copy so you use psql's copy and not the
db's copy.

You can also say "from stdin" if you need to pipe to it.

On Jul 23, 2007, at 5:09 AM, Pavel Stehule wrote:

> Hello
>
> I don't understand well, what you want to do. You can
>
> cat myfile.txt | psql database
>
> or like your sample
>
> for i in `cat myfile.txt` ; do psql mydatabase -c "insert into
> mytable (aaa,bbb) values ("xxx", "yyy");"
> ...
> regards
> Pavel Stehule
>
>
> 2007/7/23, Chuck Payne <cpayne(at)magigames(dot)net>:
>>
>> Hey,
>>
>> I have spend the last several days looking for a website or how to
>> that
>> would show me how to call postgresql in bash script. I know that
>> in mysql I
>> can do like this
>>
>> for i in `cat myfile.txt` ; do mysql -uxxx -pxxxx -Asse
>> mydatabase "insert
>> into mytable (aaa,bbb) values ("xxx", "yyy");"
>>
>> I have tried to do what with pgsql and it not working. I have
>> looked at my
>> two books I have and they are very limited.
>>
>> Can some what tell if postgre has flag like -Asse? Or show me a
>> simple
>> script they have do that is kinda like above.
>>
>> Thanks,
>>
>> Payne
>>
>> ----------------------------------------------------
>> www.britishscifiexchange.com
>> www.magigames.net
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>