Thread: Need help with bash script and postgresql

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

Hi Chuck,

I am no expert but this should definitely be possible with postgres.

The posgres command in the loop should look like so (not sure about the password):

psql -U <user> -d mydatabase -c ""insert into mytable  (aaa,bbb) values ('xxx', 'yyy');"

Cheers,

Andy

On Mon, 2007-07-23 at 05:34 -0400, Chuck Payne wrote:
>
> 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.

I can't really offer any help yet.. (still playing with it)
but.. can't you do use copy instead of inserts?

eg: psql -U xxx -W DBname -c "\copy from '/tmp/myfile.txt' with csv
header"

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@magigames.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

On 23/07/2007 11:04, Andy Dale wrote:

> The posgres command in the loop should look like so (not sure about the
> password):

As I understand it, you supply the password via a pgpass file - you
can't include it on the command line.

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

Hi,

Le lundi 23 juillet 2007, Chuck Payne a écrit :
> for i in `cat
> myfile.txt` ; do mysql -uxxx -pxxxx -Asse mydatabase  "insert
> into mytable  (aaa,bbb) values ("xxx",
> "yyy");"

It seems a part of your problem is not about scripting psql but loading data
into PostgreSQL, so let me present you with pgloader:
  http://pgfoundry.org/projects/pgloader

It uses COPY to insert quickly some CSV (or CSV-like) formated data into the
database. You have to configure it (a section per input file, one input file
per table, see man page), then it will import the data. Then main difference
with plain COPY is that pgloader will be able to insert data in the presence
of bad input rows, simply discarding them, when COPY will cancel the load
entirely.

Hope this helps, regards,
--
dim

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@magigames.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
>

On 23/07/2007 14:22, Perry Smith wrote:

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

-P doesn't specify the password - see below:

C:\Documents and Settings\rod>psql --help
This is psql 8.2.4, the PostgreSQL interactive terminal.

Usage:
   psql [OPTIONS]... [DBNAME [USERNAME]]

<snip>

Output format options:

<snip>

   -P VAR[=ARG]    set printing option VAR to ARG (see \pset command)

<etc...>


Ray.


---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

On Jul 23, 2007, at 8:32 AM, Raymond O'Donnell wrote:

> On 23/07/2007 14:22, Perry Smith wrote:
>
>> First, you can specify a password with -P (I think --password
>> works also).  psql --help for optins.
>
> -P doesn't specify the password - see below:
>
> C:\Documents and Settings\rod>psql --help
> This is psql 8.2.4, the PostgreSQL interactive terminal.
>
> Usage:
>   psql [OPTIONS]... [DBNAME [USERNAME]]
>
> <snip>
>
> Output format options:
>
> <snip>
>
>   -P VAR[=ARG]    set printing option VAR to ARG (see \pset command)
>
> <etc...>

Ah... I knew that.  I always assume it does, then it doesn't work,
then I look at the man page.  Sorry.

(note that --password does not either.  It just forces psql to prompt
for one).

Perry Smith ( pedz@easesoftware.com )
Ease Software, Inc. ( http://www.easesoftware.com )

Low cost SATA Disk Systems for IBMs p5, pSeries, and RS/6000 AIX systems

On 23/07/2007 14:51, Perry Smith wrote:

> Ah... I knew that.  I always assume it does, then it doesn't work, then
> I look at the man page.  Sorry.

<grin> Been there, still wearing the t-shirt! :-)

Ray.


---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

On 7/23/07, Chuck Payne <cpayne@magigames.net> wrote:
>
> 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.

Sometimes it's handy to process multiple lines at a time and process
each piece of data.  Here's a bit of a script, simplified, that we use
to monitor our application where I work.

echo $newquery | psql -h pg -U report -Atp5432 -F" " productiondb >
/tmp/$$stat.tmp;
t=/tmp/$$stat.tmp;
# If there's no response, exit
if [[ -z $t ]]; then
        rm /tmp/$$stat.tmp
        exit;
fi;
while read line
 do
        arr=($line)
                tc=${arr[0]}
                frate=${arr[1]}
                fails=${arr[2]}
                if [[ frate -gt 25 && tc -gt 5 ]]; then
                        do something here
               fi;
                if [[ fails -gt 10 && tc -gt 5 ]]; then
                        do something here
               fi;
        fi;
done < /tmp/$$stat.tmp

This script reads one line at a time from the $$stat.tmp file and
explodes each space separated element and assigns them to variables
you can perform tests on.

On Jul 23, 6:11 am, r...@iol.ie ("Raymond O'Donnell") wrote:
> On 23/07/2007 11:04, Andy Dale wrote:
>
> > The posgres command in the loop should look like so (not sure about the
> > password):
>
> As I understand it, you supply the password via a pgpass file - you
> can't include it on the command line.
>
> Ray.
>
> ---------------------------------------------------------------
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> r...@iol.ie
> ---------------------------------------------------------------

FWIW you can supply the password via a PGPASSWORD environment
variable, e.g.:

prompt$ PGPASSWORD=secret psql -h pghost -U pguser -d thedb -c "SELECT
'x'"