The following bug has been logged online:
Bug reference: 6080
Logged by: Chris Bandy
Email address: bandy.chris@gmail.com
PostgreSQL version: 9.0.3
Operating system: Gentoo
Description: information_schema.columns.column_default contains NULL
inconsistently
Details:
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.
-- Chris
$ psql -P null='<null>' testing
psql (9.0.3)
Type "help" for help.
testing=> select version();
version
----------------------------------------------------------------------------
---------------------------------------------------
PostgreSQL 9.0.3 on x86_64-pc-linux-gnu, compiled by GCC
x86_64-pc-linux-gnu-gcc (Gentoo 4.4.5 p1.2, pie-0.4.5) 4.4.5, 64-bit
(1 row)
testing=> create table tt (a varchar, b varchar default null, c varchar
default null::varchar, d varchar(1) default null, e varchar(1) default
null::varchar);
CREATE TABLE
testing=> \d tt
Table "public.tt"
Column | Type | Modifiers
--------+----------------------+---------------------------------
a | character varying |
b | character varying |
c | character varying |
d | character varying(1) | default NULL::character varying
e | character varying(1) | default NULL::character varying
testing=> select column_name, data_type, column_default from
information_schema.columns where table_name = 'tt';
column_name | data_type | column_default
-------------+-------------------+-------------------------
a | character varying | <null>
b | character varying | <null>
c | character varying | <null>
d | character varying | NULL::character varying
e | character varying | NULL::character varying
(5 rows)