On enforcing default column value, AKA "Bloody nulls"

On Thu, Jan 16, 2025 at 12:14:54PM +0000, Wiwwo Staff wrote:

Hi all!
In a scenario like this:

=# create table tab1(text1 text default 'from table');

=# create procedure ins_tab1(p_text1 text default 'from proc') language

sql as

-# $$
$# insert into tab1(text1) values (p_text1);
$# $$;

=# call ins_tab1 (null);

=# select * from tab1 ;
text1
--------
[nUlL]

Is there a way (apart from triggers) to force at least one of the default
values?

If you want default value, then don't provide null as value.

insert into tab1 (other, columns) values (1,2) - will put default value
in text1.

Since you did provide/force value of null, default isn't used.

If you want to list the column, then provide "value" of DEFAULT. like:

insert into tab1(text1) values (DEFAULT);

But, if you want to "insert NULL value, and have NULL get automagically
converted to something else" - trigger is your only sane option.

Defaults are used *only* in case of "no value for column was provided".

Best regards,

depesz