Stephan Szabo <sszabo@xxxxxxxxxxxxxxxxxxxxx> writes: > On Fri, 11 Feb 2005, Guy Rouillier wrote: >> Now I create a view like this: >> create or replace view v1 as select * from t1; >> >> Next, I attempt to update this view like this: >> create or replace view v1 as select * from t1 union select * from t2; >> >> I receive: ERROR: cannot change data type of view column "serv_id" > I'm pretty sure PostgreSQL treats the type of serv_id in the new view as > varchar with no limit rather than varchar(50). This is the same issue reported here: http://archives.postgresql.org/pgsql-general/2004-12/msg00508.php http://archives.postgresql.org/pgsql-hackers/2004-12/msg00408.php and is pretty closely related to this: http://archives.postgresql.org/pgsql-bugs/2005-02/msg00008.php I was looking at fixing it yesterday. The obvious path to a fix is to do select_common_type across all the members of a set-operation tree at once, rather than pairwise as we do it now. That bothers me a bit because it could change the semantics. Consider for example SELECT float8col UNION ALL (SELECT numericcol UNION SELECT numericcol) The final result will be float8 in any case, but I think that a strict reading of the spec requires the righthand UNION to be done in numeric type, before we promote to float8. If we promote to float8 and then union, roundoff could make formerly distinct values not distinct, resulting in a different number of output rows. The above is a pretty contrived case though --- in particular, I had to write UNION ALL for the upper level, else it would do a duplicate-elimination pass that would mask the issue anyway. In practice I'm not sure anyone would ever notice the difference if we took the simple path of selecting one type across the whole tree. Maybe it would work to resolve types only across nested nodes of exactly the same set operator. I haven't worked through the details of this however. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
