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