public inbox for [email protected]  

help / color / mirror / Atom feed

On enforcing default column value, AKA "Bloody nulls"
2+ messages / 2 participants
[nested] [flat]

* On enforcing default column value, AKA "Bloody nulls"
@ 2025-01-16 12:14 Wiwwo Staff <[email protected]>
  2025-01-16 12:24 ` Re: On enforcing default column value, AKA "Bloody nulls" hubert depesz lubaczewski <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Wiwwo Staff @ 2025-01-16 12:14 UTC (permalink / raw)
  To: [email protected]

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!


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: On enforcing default column value, AKA "Bloody nulls"
  2025-01-16 12:14 On enforcing default column value, AKA "Bloody nulls" Wiwwo Staff <[email protected]>
@ 2025-01-16 12:24 ` hubert depesz lubaczewski <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: hubert depesz lubaczewski @ 2025-01-16 12:24 UTC (permalink / raw)
  To: Wiwwo Staff <[email protected]>; +Cc: [email protected]

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







^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2025-01-16 12:24 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-16 12:14 On enforcing default column value, AKA "Bloody nulls" Wiwwo Staff <[email protected]>
2025-01-16 12:24 ` hubert depesz lubaczewski <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox