Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2012

    Unanswered: Using a boolean array to select rows from table

    My problem here is twofold. First off, I have a Java application that needs to do a SELECT on a table, using a boolean array to decide which rows to select. The array can be true/false, it can be an int array instead with 1/0, or it can be a decimal number which in binary has bits set corresponding to the rows I want to select. The choice of which to use depends on what PostgreSQL supports and on which design I choose.

    In my table I have a column called Index, which goes from 0 to the number of rows minus 1. Thus I can sort by that and then apply my array. But I don't know if it's possible to do something like this in a SELECT statement, using either an array or a decimal number.

    Once I have selected the proper rows, I run a function on each row. This function returns true/false or 1/0, again depending on PostgreSQL's support. So I select the result of that function for each row I've selected previously. Thus I end up with one column full of true/false. I then need to use that column in conjunction with the array from before, such that I can do AND, OR etc on it. I'm using a matrix in Java for that, so that will take care of doing that. But I need to convert the column into a single line, instead of a bunch of rows. But is that possible in PostgreSQL somehow, using the SELECT?

  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    This seem awfully ... convoluted. I assume that the first T/F array value would be matched against the first record, the second T/F array value against the second record, and so on. However, there is absolutely no guarantee that a select statement you run today, and the same select statement run next week will return data records ordered in the same way, unless you supply the appropriate 'order by' clause.

    In SQL, which is set oriented, a standard approach is to create a table with the same PK fields as the target table, and optionally one or more flag fields. It is easy to construct a sql statement to return all records from table 'A' which have the same PK as records in table 'B' where the flagfield is true. In this way you let SQL handle the matching.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts