Re: BUG #6080: information_schema.columns.column_default contains NULL inconsistently

On Fri, Jul 1, 2011 at 10:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Chris Bandy" <bandy.chris@gmail.com> writes:
> > While using the information_schema to examine my tables, I found that
> > "columns"."column_default" does not consistently represent the DEFAULT
> > constraint/definition of a column.
>
> > I would expect a column without a DEFAULT definition to return a null v=
alue,
> > while a column with a DEFAULT definition would return the defined expre=
ssion
> > as a character value.
>
> > In the following log, columns "a", "b" and "c" appear identical though =
their
> > definitions differ.
>
> I don't see anything to fix here. =C2=A0The standard says that for a colu=
mn
> without any explicit default value, COLUMN_DEFAULT should be null.

That makes sense.

>
> But AFAICS there is room for implementation dependency in other cases.
> In the particular cases you show here, PG recognizes some of them as
> being equivalent to not having a default value, so for efficiency's sake
> it converts them to that form.

That makes sense, too. Perhaps I am naive, but a null is a null,
right? Is the different presentation of defaults for "d" and "e"
indicative of an *in*efficiency in PG?

>
> I don't think we're bound to make every
> such case work like that, though.

As it stands now, it is impossible to state a succinct/clear
definition of the contents of "column_default" in PG: It contains a
null value for columns with a default of null or contains a character
expression of the default value with a type cast that, possibly, does
not match the column type.

>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0regards, tom lane

One other case that I failed to include originally is below. The
reported default includes the type length.

-- Chris


$ psql -P null=3D'<null>' testing
psql (9.0.3)
Type "help" for help.

testing=3D> create table tt (f varchar(1) default null::varchar(1));
CREATE TABLE
testing=3D> \d tt
                         Table "public.tt"
 Column |         Type         |             Modifiers
--------+----------------------+------------------------------------
 f      | character varying(1) | default NULL::character varying(1)

testing=3D> select column_name, data_type, column_default from
information_schema.columns where table_name =3D 'tt';
 column_name |     data_type     |       column_default
-------------+-------------------+----------------------------
 f           | character varying | NULL::character varying(1)
(1 row)