The following bug has been logged online:
Bug reference: 4324
Logged by: Jeff Galyan
Email address: jeff@richrelevance.com
PostgreSQL version: 8.3.3
Operating system: Linux
Description: Default value for a column is not returned in select
when column has not been explicitly set
Details:
When a column does not have a value explicitly set, v8.3.3 is not returning
the default value for the column, as 8.2.3 used to (per section 11.5 of the
SQL specification). The purpose of setting a default value for a column is
so a value will be returned if the column has not been explicitly set. If a
nullable column has no value but does have a default, the specification
requires that the default value be returned. If the column's value has been
explicitly set, then the value in the column must be returned. Further,
when a default is specified in the column descriptor, INSERTs which omit
setting a value for the column should automatically insert the default value
into the column. Again, the behavior in 8.2 conformed with the SQL
specification, section 11.5. 8.3 is not behaving per the spec.
Example:
Take an existing table with some data in it and add a nullable column of
type boolean with default value true. In 8.2, 'select bool_column from
my_table' would have returned 'true' for all rows where the column had not
been explicitly set (which should be all of them at this point). Subsequent
inserts would have the value automatically set to 'true' if no value was
specified, or whatever value is explicitly specified. In 8.3, this case
will return NULL for all rows where the value has not been explicitly
specified. Per sec. 11.5 of the SQL spec, the behavior of v8.2 is correct.
