CODEWITHSUNDEEP
sqlite
Dharani.C
Dharani.C

Reputation: 33

In SQLite , How to SELECT a column only if it exists in the table

I am trying to write a query where the table will be generated dynamically for each job . And the columns will either exist or not based on input. In SQLite , i need to fetch the value of a column only if it exists otherwise null.

I tried with if & case statements using Pragma_table_info , but for negative scenario it is not working.

'''select case when (select name from pragma_table_info('table_name') where name = col_name )is null then error_message else col_name''' end from table_name

This query is running if the mentioned col_name exists . But if not exists then it is throwing syntax error in else part.

Only in select query it should be done

Upvotes: 0

Views: 2032

Answers (1)

forpas
forpas

Reputation: 164099

Your code should work if the table's name, the column's name and the error message are properly quoted:

SELECT CASE 
         WHEN (SELECT name FROM pragma_table_info('table_name') WHERE name = 'col_name') 
           IS NULL THEN 'error_message' 
         ELSE 'col_name'
       END

But you can do the same simpler with aggregation and COALESCE():

SELECT COALESCE(MAX(name), 'error_message') 
FROM pragma_table_info('table_name') 
WHERE name = 'col_name'

See the demo.

Upvotes: 2

Related Questions