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

From Pavel Stehule
Subject Re: BUG #4324: Default value for a column is not returned in select when column has not been explicitly set
Date
Msg-id 162867790807260409of9cd6e1l6c1cdaa0ee5ad38c@mail.gmail.com
Whole thread Raw
In response to BUG #4324: Default value for a column is not returned in select when column has not been explicitly set  ("Jeff Galyan" <jeff@richrelevance.com>)
List pgsql-bugs
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
>

pgsql-bugs by date:

Previous
From: "Jeff Galyan"
Date:
Subject: BUG #4324: Default value for a column is not returned in select when column has not been explicitly set
Next
From: Craig Ringer
Date:
Subject: Re: Error while loading shared libraries