Subject: On enforcing default column value, AKA "Bloody nulls"

From: Wiwwo Staff <[email-masked]>
Date: Thu, 16 Jan 2025 12:14:54 +0000

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?

Thanks!

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Thu, 16 Jan 2025 13:24:22 +0100

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

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