Re: Bug #513: union all changes char(3) column definition

Rae Stiening (stiening(at)cannon(dot)astro(dot)umass(dot)edu) writes:
> This script demonstrates the incorrect restoration of a
> table created by a union under postgresql version 7.1.3.

What's really going on here is that

1. The CREATE TABLE AS command creates a column with type bpchar and
typmod -1 (ie, no specific length enforced).

2. pg_dump dumps this column with the type identified as "character".

3. On reload, "character" is interpreted as "character(1)".

While each of these behaviors is justifiable to some degree when
considered by itself, their interaction is not good. It is actually
not possible for pg_dump to dump this table correctly, because there
is no CREATE TABLE command it can give to reproduce the type/typmod
combination.

I thought a little bit about trying to disallow the creation of such
tables, but I don't believe that can work in the general case.
CREATE TABLE AS cannot be expected to be able to extract a suitable
typmod from complex expressions. We could think about replacing
"bpchar/-1" with "text", but that only fixes the problem for bpchar;
we have the exact same issue with numeric, and there is no comparable
workaround for numeric.

So I think what we need to do is rejigger the type display and entry
rules so that there is a recognized representation for "bpchar with
no typmod", "numeric with no typmod", etc, and the parser will not
bogusly insert default length limits when it sees this representation.

For char I propose that this representation be
bpchar
ie the underlying type name. This is a bit ugly, but since the notion
of char(n) with no particular limit is definitely non-SQL92 anyway,
using a non-SQL name seems appropriate.

For varchar, it already works to write any of
varchar
char varying
character varying
This does not conflict with SQL92 since the standard doesn't allow the
length spec to be omitted in these types, and so there's not an expected
default of 1 as there is for char.

For numeric, we could say that the representation for typmod -1 is
"numeric"
(double quotes required) ... but I really wonder why we have the
convention that numeric defaults to numeric(30,6) in the first place.
Why shouldn't the default behavior be to use typmod -1 (no limit)?
The (30,6) convention cannot be justified on the basis of the SQL spec;
it says the default precision is implementation-defined and the default
scale is zero. "No limit" looks like a good enough
implementation-defined precision value to me, and as for the scale,
defaulting to no scale adjustment is less likely to make anyone unhappy
than defaulting to scale zero. So I propose that we remove all notion
of a default precision and scale for numeric, and say that numeric
written without a precision/scale spec means numeric with typmod -1.

For bit, the SQL spec requires us to interpret unadorned bit as meaning
bit(1), so there seems little choice but to use
"bit"
(quotes required) for the typmod -1 case.

For varbit, "bit varying" already works and need not be messed with;
same rationale as for varchar.

As far as implementation goes, on the output side all that's needed is
some simple changes in format_type to emit the desired representation.
In the parser, we need to remove transformColumnType's diddling of
typmod and instead insert the correct default typmod in gram.y. We
can't do it later than gram.y since the distinction between "bit" and
bit, etc, is not visible later.

Comments?

regards, tom lane