Skip site navigation (1) Skip section navigation (2)

Re: default value returned from sql stmt

From: Alban Hertroys <haramrae(at)gmail(dot)com>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, David Salisbury <salisbury(at)globe(dot)gov>, pgsql-general(at)postgresql(dot)org
Subject: Re: default value returned from sql stmt
Date: 2012-03-30 08:45:40
Message-ID: 547602A3-FD9A-4138-8E28-31748EEEDA2F@gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
On 30 Mar 2012, at 10:22, Richard Huxton wrote:

> On 30/03/12 08:46, Pavel Stehule wrote:
>> 2012/3/30 Richard Huxton<dev(at)archonet(dot)com>:
>>> On 29/03/12 23:28, Pavel Stehule wrote:
>>>> 
>>>> select anum from t1 where anum = 4
>>>> union all select 100 limit 1;
>>> 
>>> 
>>> I'm not sure the ordering here is guaranteed by the standard though, is it?
>>> You could end up with the 4 being discarded.
>> 
>> A order is random for only "UNION", "UNION ALL" should to respect
>> order.  But I didn't check it in standard.
> 
> Let's put it this way - a quick bit of googling can't find anything that says the order *is* guaranteed, and (almost?) no other operations do so by default.


Obviously, UNION needs to sort the results to filter out any duplicate rows, so it would change the order of the results of above query and return the 100-valued row for anum values > 100.

UNION ALL will not do so by default, so it would probably behave as Pavel describes. Until you add an ORDER BY to your query.

A more robust implementation would be:

select anum, 0 from t1 where anum = 4
union all
select 100, 1 limit 1
order by 2;

If you don't want the extra column in your query results, you can wrap the query in another select.

Alban Hertroys

--
The scale of a problem often equals the size of an ego.



In response to

pgsql-general by date

Next:From: Alban HertroysDate: 2012-03-30 08:51:14
Subject: Re: user get notification when postgresql database updated
Previous:From: Pavel StehuleDate: 2012-03-30 08:34:21
Subject: Re: default value returned from sql stmt

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group