I need to query postgres, run a count of _specific _records, and write to a different table based on the results all from the linux command line.
This feels like it should be simple, but I'm new to postgres and could use some help. 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?
Thanks!
Scott
My advice would be simple: use your favorite scripting language for that. The ad hoc query tools like psql are not meant for reporting and batch jobs. I've seen such tools abused so many times that I lost count. What people end up with is a lousy report with an unintelligible "script" that produces it. The script is hard to maintain, depends on the version and is usually longer than a comparable scripting language script.
-- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com