On Wed, Jul 20, 2016 at 06:03:08PM -0400, Tom Lane wrote:
> Greg Stark writes:
> > But I think I agree that it's surprising that the collate clause isn't
> > working in the ORDER BY on a column produced by a UNION. Certainly
> > that's where people usually want to put it.
>
> See this ancient com
On Wed, Jul 20, 2016 at 10:55:38PM +0100, Greg Stark wrote:
> On Wed, Jul 20, 2016 at 10:38 PM, Bruce Momjian wrote:
> > SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY x COLLATE "C";
>
>
> ::***> select 'a-c' COLLATE "C" AS x UNION ALL SELECT 'ab' AS x ORDER BY x ;
Oh, collate on the str
Greg Stark writes:
> But I think I agree that it's surprising that the collate clause isn't
> working in the ORDER BY on a column produced by a UNION. Certainly
> that's where people usually want to put it.
See this ancient comment in transformSetOperationStmt:
* For now, we don't support r
Actually there's nothing about UNION here. It's true for any column alias:
::***> select 'a-c' AS x ORDER BY x COLLATE "C" ;
ERROR: 42703: column "x" does not exist
LINE 2: select 'a-c' AS x ORDER BY x COLLATE "C" ;
^
LOCATION: errorMissingColumn, parse_relatio
On Wed, Jul 20, 2016 at 5:38 PM, Bruce Momjian wrote:
> I think the 'ORDER BY x COLLATE "C"' is being parsed as an a_expr, and
> we don't allow a_expr in a UNION. Perhaps we are too strict here, but I
> can't tell.
>
ORDER BY 1 COLLATE "C" is indeed an expression - the number no longer
refers
On Wed, Jul 20, 2016 at 10:38 PM, Bruce Momjian wrote:
> SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY x COLLATE "C";
::***> select 'a-c' COLLATE "C" AS x UNION ALL SELECT 'ab' AS x ORDER BY x ;
┌─┐
│ x │
├─┤
│ a-c │
│ ab │
└─┘
(2 rows)
But I think I agree that it's surp
Seems you can't use UNION and COLLATE in the same SELECT statement; you
have to put the UNION inside of WITH and then do the COLLATE outside:
test=> SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY 1 COLLATE "C";
ERROR: collations are not supported by type integer
LINE 1: ... 'a-c' AS x
