On sön, 2011-12-11 at 04:26 +0100, Andres Freund wrote:
> > Building a CreateStmt seems to work well enough so far.
> > The only problem with that approach so far that I found is that:
>
> > CREATE TABLE collate_test2
> > (
> > a int,
> > b text COLLATE "POSIX"
> > );
> >
> > CREATE TABLE collate_test1
> > (
> > a int,
> > b text COLLATE "C" NOT NULL
> > );
> >
> > CREATE TABLE test_u AS SELECT a, b FROM collate_test1 UNION ALL SELECT a,
> > b FROM collate_test2; -- fail
> >
> > failed with:
> > ERROR: no collation was derived for column "b" with collatable type text
> > HINT: Use the COLLATE clause to set the collation explicitly.
> > "works" now.
> Could you explain why the above should fail? After all the UNION is valid
> outside the CREATE TABLE and you can even sort on b.
That would be strange, because earlier in the test file there is also
SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test2 ORDER BY 2; -- fail
The union itself is valid, but because it combines two different
collations, the collation derivation for the column is "unknown", and so
it cannot be ordered. And we made the implementation decision to not
allow creating columns with unknown collation.