Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fB7Faom84139 for ; Fri, 7 Dec 2001 10:36:50 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id fB7FaDi29708; Fri, 7 Dec 2001 10:36:13 -0500 (EST) To: Stephan Szabo cc: Christopher Kings-Lynne , Hackers Subject: Re: [SQL] how to change the type In-reply-to: <20011206183802.E30420-100000@megazone23.bigpanda.com> References: <20011206183802.E30420-100000@megazone23.bigpanda.com> Comments: In-reply-to Stephan Szabo message dated "Thu, 06 Dec 2001 18:41:09 -0800" Date: Fri, 07 Dec 2001 10:36:13 -0500 Message-ID: <29705.1007739373@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200112/212 X-Sequence-Number: 16359 Stephan Szabo writes: > IIRC, the values are stored as length + data. I think char() might > do wierd things (I don't know if the trailing spaces are stored), but > varchar() and text should be expandable because anything that could have > fit before should still fit and look the same. Yup, exactly. > Going down is > problematic, because if you have a varchar(5) field where one value is say > 'abcd' and you make it varchar(3) what happens? What would actually happen right now is nothing: the value would still be 'abcd' and would still read out that way. The 3-char limit would only get enforced during inserts and updates of the column. char(N) does store the trailing spaces, so altering N would give unwanted results: again, existing values would read out with the old width until updated. You could fix this by issuing UPDATE tab SET col = col after tweaking the pg_attribute.atttypmod value. (AFAICS, any "clean" implementation would have to do just that internally, with the same unpleasant space and speed implications as we've discussed for DROP COLUMN.) regards, tom lane