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

Re: Wish: remove ancient constructs from Postgres

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: nikolay(at)samokhvalov(dot)com
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Wish: remove ancient constructs from Postgres
Date: 2006-02-27 17:19:53
Message-ID: 20060227091612.E39968@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-general
On Mon, 27 Feb 2006, Nikolay Samokhvalov wrote:

>it's completely different thing. look at the spec and you'll
>understand the difference. in two words, with 'DISTINCT ON' we lose
>some values (from some columns), when UNION not (it just removes
>duplicates, comparing _entire_ rows).

No it's not, really.  Read the spec.

The output of a union on a text field is non-deterministic (due to some
collation choices).  This means that the output of the query may be
determined by an effectively random choice of which value to use.

Basically AFAICT something like (modulo simple errors):

select foo from (
 select foo from tablea union select foo from tableb
)
where foo = 'A' collate case_sensitive

can give different results in the case of tablea having 'A' and tableb
having 'a' if the union is using a case insensitive comparison.


In response to

pgsql-general by date

Next:From: Bernhard WeisshuhnDate: 2006-02-27 17:24:40
Subject: Re: ltree + gist index performance degrades significantly over a night
Previous:From: CGDate: 2006-02-27 17:14:40
Subject: Re: ltree + gist index performance degrades significantly over a night

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