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