I have a property values table as below and I know that only one of the *_value fields will contain a value, the others will be NULL.
What is the most efficient way to select the value of the column only that is not null?
I have tried
SELECT property_id, COALESCE(int_value, str_value, etc)
but this doesn't work because they are different column types, similarly with NULLIF. I can cast all the values but I don't want to lose the value types.
property_values
-----------------------------------------------
property_id | integer
int_value | integer
str_value | character varying
time_value | timestamp without time zone
txt_value | text
dec_value | numeric
bool_value | boolean
json_value | jsonb
This is too long for a comment.
You cannot do what you want, at least "reasonably". As you note, coalesce() doesn't work because of the type conflict. That gets close to the root of the problem: a column in a result set has to have a specific type.
So, what type do you want the result to be? You could decide on a "universal" type, such as a string. I'm not sure that is such a good idea for your use case.