FAQ
Hello

Hoping for some help on this one....

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

Search Discussions

  • Andreas Kretschmer at Jul 10, 2010 at 6:42 am

    Scott Geller wrote:


    Hello

    Hoping for some help on this one....

    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.

    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?
    Sure, you can use psql for that, here a simple example:


    ,----[ my script ]
    kretschmer@tux:~$ cat test.sh
    #!/bin/bash

    d=`date`

    psql test -c "select 'today is ' || '$d' || ', and the database says ' || now()"
    `----

    ,----[ running the script ]
    kretschmer@tux:~$ ./test.sh
    ?column?
    ---------------------------------------------------------------------------------------------
    today is Sa 10. Jul 08:40:25 CEST 2010, and the database says 2010-07-10 08:40:25.248636+02
    (1 Zeile)

    kretschmer@tux:~$
    `----


    You got it?


    Andreas
    --
    Really, I'm not out to destroy Microsoft. That will just be a completely
    unintentional side effect. (Linus Torvalds)
    "If I was god, I would recompile penguin with --enable-fly." (unknown)
    Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
  • Alan Hodgson at Jul 12, 2010 at 3:58 pm

    Scott Geller wrote:
    Hello

    Hoping for some help on this one....

    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.

    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?
    Using Perl would be a lot easier.

    --
    "No animals were harmed in the recording of this episode. We tried but that
    damn monkey was just too fast."
  • Mladen Gogala at Jul 10, 2010 at 12:03 pm

    Scott Geller wrote:
    Hello

    Hoping for some help on this one....

    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
  • Scott Geller at Jul 10, 2010 at 8:02 pm
    Thanks Mladen

    I'm a novice - if psgl is not a scripting language, can you please give me
    an example of one? maybe perl?

    Thanks

    On Sat, Jul 10, 2010 at 7:03 AM, Mladen Gogala wrote:

    Scott Geller wrote:
    Hello

    Hoping for some help on this one....

    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
  • Mladen Gogala at Jul 10, 2010 at 10:22 pm

    Scott Geller wrote:
    Thanks Mladen

    I'm a novice - if psgl is not a scripting language, can you please
    give me an example of one? maybe perl?

    Thanks
    No, psql is not a scripting language, psql is an ad-hoc query utility.
    Scripting languages are numerous, among the most popular are Python, PHP
    and Perl, but there are others, like Ruby. I prefer Perl for the CLI
    but, on the other hand, there are many people that do not share my
    preferences. The only reason for my preference is the fact that I've
    been using Perl since 1994. All of the above scripting languages, with
    the possible exception of Ruby, support PostgreSQL. I know nothing of
    Ruby so I have no idea whether it supports PgSQL or not.
    Python is becoming increasingly popular these days, because of its full
    blown OO model, which Perl doesn't have. There is some mess with the
    versions, like version 3 being slow to gain acceptance, but that should
    get resolved soon.

    --
    Mladen Gogala
    Sr. Oracle DBA
    1500 Broadway
    New York, NY 10036
    (212) 329-5251
    www.vmsinfo.com
  • Frank Bax at Jul 10, 2010 at 12:56 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJul 10, '10 at 5:25a
activeJul 12, '10 at 3:58p
posts7
users5
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase