Skip site navigation (1) Skip section navigation (2)

Re: SQL from Linux command line

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: PostgreSQL List - Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: SQL from Linux command line
Date: 2010-07-10 12:56:00
Message-ID: BLU0-SMTP316DDD02E04ED1E685ABE2ACB60@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-novice
Scott Geller wrote:
> I'm good with the SQL part - for the SQL, I would write:
> 
>     create table2 as select *, case when _cnt_ < 50 then 'low' when
>     _cnt_ >= 50 'high' end as cnt from (select id, count(*) as _cnt_
>     from table1 where id = 1234 group by id) a;
> 
> My questions are:
> 
>    1. Would I use psql for this?
>    2. If so, how do I structure the script to run psql from Linux?
>    3. how do I pass the value for the where clause from the command line
>       into the code?


This might do what you want...

$ cat ./myscript
psql -c "create table2 as select *, \
case when _cnt_ < 50 then 'low' when _cnt_ >= 50 'high' end as cnt \
from (select id, count(*) as _cnt_ from table1 \
where id = $1 group by id) a;"

$ sh ./myscript 1234


In response to

pgsql-novice by date

Next:From: Frank BaxDate: 2010-07-10 13:01:11
Subject: Re: Bypassing authentication
Previous:From: Andre MajorelDate: 2010-07-10 12:51:26
Subject: Re: Bypassing authentication

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group