CODEWITHSUNDEEP
sqlpostgresql
Tahnoon Pasha
Tahnoon Pasha

Reputation: 6018

Postgres selecting only columns that meet a condition

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

Upvotes: 1

Views: 4394

Answers (3)

Hart CO
Hart CO

Reputation: 34784

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

Upvotes: 1

Ray Toal
Ray Toal

Reputation: 88478

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.

Upvotes: 2

Jan Dörrenhaus
Jan Dörrenhaus

Reputation: 6717

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.

Upvotes: 4

Related Questions