Default column value

On 12/30/2016 06:38 AM, Rich Shepard wrote:

Reading the 9.6 docs suggests an answer to my question, but does not
explicitly answer it, so I ask here.

If a column has a default value specified does this mean the column
cannot
contain a NULL value? In other words, is DEFAULT <some_value> NOT NULL
redundant?

No:

test=> create table default_test(id int, fld_1 varchar DEFAULT NULL);
CREATE TABLE

test=> \d default_test
Table "public.default_test"
Column | Type | Modifiers
--------+-------------------+-----------
id | integer |
fld_1 | character varying |

TIA,

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Rich Shepard <rshepard@appl-ecosys.com> writes:

If a column has a default value specified does this mean the column cannot
contain a NULL value? In other words, is DEFAULT <some_value> NOT NULL
redundant?

No, because you can explicitly insert a null. DEFAULT only controls
what happens when you omit the column in an INSERT command.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/30/2016 06:38 AM, Rich Shepard wrote:

Reading the 9.6 docs suggests an answer to my question, but does not
explicitly answer it, so I ask here.

If a column has a default value specified does this mean the column
cannot
contain a NULL value? In other words, is DEFAULT <some_value> NOT NULL
redundant?

Another way of looking at it:

test=> create table default_test_2(id int, fld_1 varchar DEFAULT 'test');
CREATE TABLE

test=> insert into default_test_2 values (1);
INSERT 0 1

test=> insert into default_test_2 values (2, NULL);
INSERT 0 1

test=> \pset
null 'NULL'

test=> select * from default_test_2 ;
id | fld_1
----+-------
1 | test
2 | NULL
(2 rows)

DEFAULT is what is the column is set to if the user does not specify a value.
As shown above a user can supply a NULL value. To guard against that the NOT NULL
constraint is required.

TIA,

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, 30 Dec 2016, Tom Lane wrote:

No, because you can explicitly insert a null. DEFAULT only controls what
happens when you omit the column in an INSERT command.

tom,

Thanks for clarifying. I did not pick this up from reading the manual and
knew that NULL could be an explicitly-defined default value.

Much appreciated,

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, 30 Dec 2016, Adrian Klaver wrote:

DEFAULT is what is the column is set to if the user does not specify a
value. As shown above a user can supply a NULL value. To guard against
that the NOT NULL constraint is required.

Thanks, Adrian. This was not clear to me when I read the manual.

Happy New Year,

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, 30 Dec 2016, Adrian Klaver wrote:

DEFAULT is what is the column is set to if the user does not specify a
value. As shown above a user can supply a NULL value. To guard against
that the NOT NULL constraint is required.

One more case I'd appreciate being clarified: when the column's value has
a check constraint with acceptable values in a list. For example,

param_units VARCHAR(8) DEFAULT 'mg/L'
CONSTRAINT param_units
CHECK (param_units IN ('ppm', 'mg/L', 'ug/L', 'umho/cm', 'percent', 'cfm', 'gpm')),

Seems to me that if values are constrained by a list a NULL cannot be
entered by the user. Is this correct?

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Dec 30, 2016 at 9:19 AM, Rich Shepard <rshepard@appl-ecosys.com>
wrote:

On Fri, 30 Dec 2016, Adrian Klaver wrote:

DEFAULT is what is the column is set to if the user does not specify a

value. As shown above a user can supply a NULL value. To guard against
that the NOT NULL constraint is required.

One more case I'd appreciate being clarified: when the column's value has
a check constraint with acceptable values in a list. For example,

param_units VARCHAR(8) DEFAULT 'mg/L'
CONSTRAINT param_units
CHECK (param_units IN ('ppm', 'mg/L', 'ug/L', 'umho/cm', 'percent',
'cfm', 'gpm')),

Seems to me that if values are constrained by a list a NULL cannot be
entered by the user. Is this correct?

​https://www.postgresql.org/docs/9.6/static/sql-createtable.html​

"The CHECK clause specifies an expression producing a Boolean result which
new or updated rows must satisfy for an insert or update operation to
succeed. Expressions evaluating to TRUE or UNKNOWN succeed."

NULL == "UNKNOWN"

David J.

On Fri, 30 Dec 2016, David G. Johnston wrote:

"The CHECK clause specifies an expression producing a Boolean result which
new or updated rows must satisfy for an insert or update operation to
succeed. Expressions evaluating to TRUE or UNKNOWN succeed."

NULL == "UNKNOWN"

David,

I forgot about that. Thanks for pointing it out to me.

Regards,

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/30/2016 06:46 AM, Adrian Klaver wrote:

On 12/30/2016 06:38 AM, Rich Shepard wrote:

test=> \d default_test
Table "public.default_test"
Column | Type | Modifiers
--------+-------------------+-----------
id | integer |
fld_1 | character varying |

To further illustrate this, NULL means UNKNOWN, not DEFAULT. Using
Adrian's example:

postgres=# create table default_test(id int, fld_1 varchar DEFAULT NULL);
CREATE TABLE
postgres=# INSERT into default_test VALUES(1,NULL);
INSERT 0 1
postgres=# INSERT into default_test VALUES(1,DEFAULT);
INSERT 0 1
postgres=# select * from default_test ;
id | fld_1
----+-------
1 |
1 |
(2 rows)

postgres=# alter table default_test alter column fld_1 set default now();
ALTER TABLE
postgres=# INSERT into default_test VALUES(1,DEFAULT);
INSERT 0 1
postgres=# INSERT into default_test VALUES(1,NULL);
INSERT 0 1
postgres=# select * from default_test ;
id | fld_1
----+-------------------------------
1 |
1 |
1 | 2016-12-30 09:11:11.170948-08
1 |
(4 rows)

Sincerely,

JD

--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/30/2016 08:51 AM, Rich Shepard wrote:

On Fri, 30 Dec 2016, David G. Johnston wrote:

"The CHECK clause specifies an expression producing a Boolean result
which
new or updated rows must satisfy for an insert or update operation to
succeed. Expressions evaluating to TRUE or UNKNOWN succeed."

NULL == "UNKNOWN"

David,

I forgot about that. Thanks for pointing it out to me.

What it comes down to is if you do not want NULL values in a column then
specify NOT NULL on the column.

Regards,

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general