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

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(at)richrelevance(dot)com>:
>
> The following bug has been logged online:
>
> Bug reference: 4324
> Logged by: Jeff Galyan
> Email address: jeff(at)richrelevance(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>