SQL Select between two fields depending on the value of one field

Question

I am using a PostgreSQL database, and in a table representing some measurements I've two columns: measurement, and interpolated. In the first I've the observation (measurement), and in the second the interpolated value depending on nearby values. Every record with an original value has also an interpolated value. However, there are a lot of records without "original" observations (NULL), hence the values are interpolated and stored in the second column. So basically there are just two cases in the database:

Value  Value  
NULL   Value

Of course, it is preferable to use the value from the first column if available, hence I need to build a query to select the data from the first column, and if not available (NULL), then the database returns the value from the second column for the record in question. I have no idea how to build the SQL query.

Please help. Thanks.

Answer 1

You can use Coalesce:

The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null.

Select Coalesce( first_value, second_value )
From your_table

This would return first_value if it is not NULL, second_value otherwise.

Answer 2

Peter nailed it. But for the sake of completeness (the title of your question is more general than your particular issue), here goes the docs for the several conditional expressions available in Postgresql (and in several other databases) : CASE, COALESCE, NULLIF, GREATEST, LEAST. The first one is the most general.