postgresql , how can i add to my query a list of values to the where clause ?

Posted on 2009-05-01
Last Modified: 2013-12-17
hello experts,

i'm looking on how to add a list of values at the end of my query in order to
receive the answer only for a group of values.

i'm looking to add somegthing like the last line,
except that my values are not comming from a subquery.

  CD.rf_card_id = TB.rf_card_id
  CD.account_id = AC.account_id
  TB.container_id = TB.container_id
  AND account_id in (101, 102, 103, 104, 110)    <-

thank you in advance.
Question by:toshi_
    LVL 61

    Expert Comment

    Edit the statement
    If this is prepared statement - pass array of strings as parameter in place.

    Author Comment

    hello gheist,
    could you be more explicit please,

    i'm not an expert, neither with postgresql nor with english ;o)

    what i would need is to view how to write the list,
    i'll correcte the string generation after what is correct.

    thank you for further help.
    LVL 61

    Expert Comment

    It is not PostgreSQL problem.

    How do you pass statement to PostgreSQL?
    Where does it come from?

    Author Comment

    i pass my query from .NET c# to
    postgresql 8.4 through Npgsql,

         //sb.Append(" TB.weight*0.5 as GEWICHT, ") ;
                sb.Append(" CASE WHEN  ") ;
                    sb.Append(" TB.weight*0.5 >= 0.5 AND ") ;
                    sb.Append(" TB.weight*0.5 <= 5.0 ") ;
                        sb.Append(" THEN 5.0 ") ;
                    sb.Append(" ELSE TB.weight*0.5 ") ;
                sb.Append(" END AS GEWICHT, ") ;

                sb.Append(" 0 as FNFLAG,");
                sb.Append(" CD.rf_card_id  as RFCID, ");    
                sb.Append(" AC.account_id as ACCID ");
                sb.Append(" FROM ");
                sb.Append(" trash_bag as TB, rf_card as CD, account as AC ");
                sb.Append(" WHERE ");
                sb.Append(" CD.rf_card_id = TB.rf_card_id ");
                sb.Append(" AND ");
                sb.Append(" CD.account_id = AC.account_id ");
                sb.Append(" AND ");
                sb.Append(" TB.container_id = TB.container_id ");

    // i want to reduce the query by passing a list of containers.

                // TIME
                // sb.Append(" AND > '2008-01-01' ");
                //sb.Append(" AND > '" + aBegin.ToString(PgsSymbolicConstant.DATE_FORMAT_DB)+  "'");
                sb.Append(" AND > '" + aBegin.ToString(PgsSymbolicConstant.DATETIME_FORMAT_DB)+  "'");
                // sb.Append(" AND < '2008-01-31' ");
                //sb.Append(" AND <= '" + _theEnd.ToString(PgsSymbolicConstant.DATE_FORMAT_DB)+  "'");
                sb.Append(" AND <= '" + _theEnd.ToString(PgsSymbolicConstant.DATETIME_FORMAT_DB)+  "'");
                sb.Append(" ORDER BY date ASC; ");

                DataTable _dtForExport = dba_.ExecuteQuery(sb.ToString());

                //DataTable _dtFiltered= FilterZerosAndMinimal(_dtForExport);

                return _dtForExport;
    LVL 61

    Expert Comment

    I have asked moderators to add .net area(s), there is nothing specific to PostgreSQL in your .NET program

    Author Comment

    i'd like to know how to set a list of values to restrict my query,
    is a question of query,
    it doesnt nothing have to do with .NET
    i think i 'm not accurate enough with english language

    but there's someting I'M SURE "this is a postresql question"

    LVL 22

    Accepted Solution

    somewhere  you need to build the account_id string as a comma seperated list then append it to the string buffer for your query.

    sinply ...
      String acids = "101, 102, 103, 104, 110";

     sb.Append( " AND AC.account_id in ("  );
     sb.Append( acids );
      sb.Append( " )" );

      sb.Append(" ORDER BY ASC; ");

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
    Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now