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

On Fri, Jul 1, 2011 at 10:35 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Chris Bandy" <bandy(dot)chris(at)gmail(dot)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 value,
> > while a column with a DEFAULT definition would return the defined expression
> > 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.  The standard says that for a column
> 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.

>
>                        regards, tom lane

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

-- Chris

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

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

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