Re: Command Triggers

From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: Andres Freund <andres(at)anarazel(dot)de>
Cc: pgsql-hackers(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>
Subject: Re: Command Triggers
Date: 2011-12-11 19:09:36
Message-ID: 1323630576.24785.2.camel@vanquo.pezone.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2011-12-11 19:21:39 Re: patch for type privileges
Previous Message Dimitri Fontaine 2011-12-11 18:55:39 Re: Command Triggers