public inbox for [email protected]  

help / color / mirror / Atom feed

From: hubert depesz lubaczewski <[email protected]>
To: Wiwwo Staff <[email protected]>
Cc: [email protected]
Subject: Re: On enforcing default column value, AKA "Bloody nulls"
Date: Thu, 16 Jan 2025 13:24:22 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAD1W9HW=PZMZMN7GmPWVUW8BSnvzCYJn=W8nmRXqTaUntSaTdg@mail.gmail.com>
References: <CAD1W9HW=PZMZMN7GmPWVUW8BSnvzCYJn=W8nmRXqTaUntSaTdg@mail.gmail.com>

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

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: On enforcing default column value, AKA "Bloody nulls"
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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