How to return a default value if no result

From: "Rob - TEAM Systems Ltd" <rob(at)teamsystems(dot)co(dot)uk>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: How to return a default value if no result
Date: 2014-08-22 11:19:14
Message-ID: 002101cfbdfa$e7d085b0$b7719110$@teamsystems.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I want my query to return at least one row with a default result even if
there are no matches in the database.

As a simple example, getting the valid VAT rate for a transaction date and
VAT code:

Select vat_rate from vatrates where vat_code='1' and vat_date<='2014-08-01'
order by vat_date desc limit 1

This is fine as long as there is a match. If there are no matches, I'd like
it to return a default (0) rather than no result.

I could make sure there is always a result by having a UNION with a
hard-coded record, but this doesn't let me order by a field not in the
result.

In this example I could just make sure there is a dummy row in the table
with a very old date and do something like this:

Select vat_rate from vatrates where (vat_code='1' or vat_code='DUMMY') and
vat_date<='2014-08-01' order by vat_date desc limit 1

But it seems like a bodge to me.

Is there a nice way of forcing a default result somehow?

Thanks,

Rob

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Rob - TEAM Systems Ltd 2014-08-22 11:29:27 Re: How to return a default value if no result
Previous Message Pedro Sousa 2014-08-20 13:53:41 change the recording database partition