Postgres selecting only columns that meet a condition

Question

If I have a database table t1 that has the following data:

How can I select just those columns that contain the term "false"

 a   |   b   |  c   |  d   
------+-------+------+------
 1    | 2     | 3    | 4
 a    | b     | c    | d
 5    | 4     | 3    | 2
 true | false | true | true
 1    | 2     | 3    | 4
 a    | b     | c    | d
 5    | 4     | 3    | 2
 true | false | true | true
 1    | 2     | 3    | 4
 a    | b     | c    | d
 5    | 4     | 3    | 2
 true | false | true | true

Thanks

Select _the columns_? You mean the result should be `b`? How did you manage to store both booleans and numbers in the same column to begin with? — Ray Toal, Jun 13 '13 at 02:55
@RayToal thanks, the inputs are arbitrary. (In this case they are all varchar strings). I just wanted to understand how I might produce this. — Tahnoon Pasha, Jun 13 '13 at 03:00

score 4 · Answer 1 · answered Jun 13 '13 at 03:00

Assuming that you want to select everything from any column that contains "false", which means you want to select everything from column b in this case:

Even if that were possible somehow - although I cannot think of a solution off the top of my head - it would be a serious violation of the relational concept. When you do a select, you are always supposed to know beforehand - which means at design time, not at run time - which, and especially how many columns you are supposed to get. Data specific column counts are not the relational database way.

thanks @JanDoerrenhaus. I'm very new to this, (only using it tactically) so I may be asking the wrong sort of question. In practice the data will include columns that are populated over time, and I wanted to select those that are e.g. empty or full or have changed — Tahnoon Pasha, Jun 13 '13 at 03:03

score 2 · Accepted Answer · answered Jun 13 '13 at 03:07

I'm afraid the best way to do this is to write some procedural code to select all rows from the table, and at each row, examine the data to see which values match your condition. Remember the column containing the matching value by adding its name to a set.

When you are done with the full, painful table scan, your set will contain the column names that you need.

PL/pgSQL docs

I would be surprised if there were a way to select columns via some kind of meta-like SQL query, but one never knows with PostgresQL. Sometimes you have to do things procedurally, and this sounds like one of those cases.

thanks @RayToal this is helpful. I'll hold out for a bit just in case someone comes up with an alternative answer but from the comments so far I suspect this is as good as I'll get and I'll mark it up later if nothing else comes up. Appreciate your help. — Tahnoon Pasha, Jun 13 '13 at 03:13

score 1 · Answer 3 · edited May 23 '17 at 12:28

I'm a SQL Server guy, so not sure exactly how you can do this. I would use a cursor to iterate through each field in the table, and insert the column name into a results table if the value is found.

You can get column names from your table with:

SELECT column_name 
FROM information_schema.columns 
WHERE table_name='t1'

I think this should be the fastest way to determine whether or not an individual field contains 'False':

SELECT exists (SELECT 1 FROM t1 WHERE a = 'False' LIMIT 1);

So you should be able to set up a script to automate the process using those two bits.

I also found this answer which might be helpful: How to search a specific value in all tables

Postgres selecting only columns that meet a condition

3 Answers3