Thread: BUG #4324: Default value for a column is not returned in select when column has not been explicitly set

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.

Hello,

can you send any samples? This works for me:

postgres=# select version();
                                              version
----------------------------------------------------------------------------------------------------
 PostgreSQL 8.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20070502 (Red Hat 4.1.2-12)
(1 row)
postgres=# create table g1(a bool default true null, c varchar);
CREATE TABLE
postgres=# insert into g1(c) VALUES ('ahoj');
INSERT 0 1
postgres=# select * from g1;
 a |  c
---+------
 t | ahoj
(1 row)

postgres=# select version();
                                                version
--------------------------------------------------------------------------------
 PostgreSQL 8.4devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070
(1 row)
postgres=# create table g1(a bool default true null, c varchar);
CREATE TABLE
postgres=# insert into g1(c) VALUES ('ahoj');
INSERT 0 1
postgres=# select * from g1;
 a |  c
---+------
 t | ahoj
(1 row)

regards
Pavel Stehule
2008/7/26 Jeff Galyan <jeff@richrelevance.com>:
>
> 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.
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

"Jeff Galyan" <jeff@richrelevance.com> writes:
> 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).

Works for me:

regression=# create table foo (f1 int);
CREATE TABLE
regression=# insert into foo values(1),(2);
INSERT 0 2
regression=# alter table foo add column b bool default true;
ALTER TABLE
regression=# select * from foo;
 f1 | b
----+---
  1 | t
  2 | t
(2 rows)

Please provide a concrete test case to demonstrate what you are seeing.

            regards, tom lane