SQLite Forum

FEATURE REQUEST: use of DEFAULT in INSERTs
Login

FEATURE REQUEST: use of DEFAULT in INSERTs

(1) By anonymous on 2020-11-23 09:57:58

Hello sqlite community

I would like to be able to specify the use of DEFAULT values within INSERT statements (on column level):

Here is an example:

~~~
CREATE TABLE t1 (
      a   INTEGER DEFAULT 1 NOT NULL
    , b   INTEGER DEFAULT 2 NOT NULL
    , c   INTEGER DEFAULT 3 NOT NULL
);
~~~
~~~
INSERT INTO t1 
  (a      , b      , c      ) VALUES 
  (10     , DEFAULT, DEFAULT)
, (DEFAULT, 20     , DEFAULT)
, (DEFAULT, DEFAULT, 30     )
;
~~~

would be working with this feature.

I know that 3 different INSERT statements would work:

~~~
INSERT INTO t1 (a) VALUES (10);
INSERT INTO t1 (b) VALUES (20);
INSERT INTO t1 (C) VALUES (30);
~~~

or as well this statement:

~~~
INSERT INTO t1 DEFAULT VALUES;
~~~

I found this thread [here], which explains more.
(but is wrong in some parts)

Is there a chance to include this feature?

Thanks a lot!

Philipp

[here]: https://stackoverflow.com/questions/8777362/how-to-insert-default-values-in-sql-table

(2) By Gunter Hick (gunter_hick) on 2020-11-23 11:57:26 in reply to 1 [link]

Naming a column in the INSERT statement is a promise that you will provide a value and a request that SQL provide default values for the columns not mentioned; or an error message stating that such defaults are missing in the table declaration. You cannot just change your mind.

(3.2) By Keith Medcalf (kmedcalf) on 2020-11-23 12:12:14 edited from 3.1 in reply to 1 [link]

Do you mean perhaps to define the table as follows:

```
CREATE TABLE t1 
(
    a    INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 1,
    b    INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 2,
    c    INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 3
);
```

so that if you try to insert a NULL value you get a default instead?

```
sqlite> CREATE TABLE t1
   ...> (
   ...>     a    INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 1,
   ...>     b    INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 2,
   ...>     c    INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 3
   ...> );
sqlite> insert into t1 values (58,null,null);
sqlite> insert into t1 values (null,36,null);
sqlite> insert into t1 values (null,38,1748);
sqlite> select * from t1;
┌────┬────┬──────┐
│ a  │ b  │  c   │
├────┼────┼──────┤
│ 58 │ 2  │ 3    │
│ 1  │ 36 │ 3    │
│ 1  │ 38 │ 1748 │
└────┴────┴──────┘
sqlite>
```

(4) By anonymous on 2020-11-23 12:20:14 in reply to 3.2 [link]

Exactly, this allows to write one INSERT statement and change the DEFAULTs with NULLs:

~~~
INSERT INTO t1 
  (a      , b      , c      ) VALUES 
  (10     , NULL   , NULL)
, (NULL   , 20     , NULL)
, (NULL   , NULL   , 30     )
;
~~~

I didn't know about the "NOT NULL ON CONFLICT REPLACE" part.
Thank you very much!

(5) By anonymous on 2020-11-23 12:27:46 in reply to 2 [link]

I agree with you.
I see it less about changing my mind, more about having another option.

Using the word DEFAULT would allow me to specify the column for the insert, but choosing the default value from the table definition.

Mention col and a value -> use it

Mention col and DEFAULT OR
not Mentioning col
-> use the default from the CREATE TABLE definition

It helps to write bigger INSERTS, instead of several INSERTS.

In the example it seems silly, but that's due to its simplicity.

Thank you very much!

(6) By Richard Hipp (drh) on 2020-11-23 14:20:21 in reply to 1 [link]

I see that all of PostgreSQL, MySQL, SQL Server, and Oracle support this.
So SQLite probably should too.

However, due to technical reasons, this is a substantial change to SQLite.
It would introduce unnecessary risk to add such an enhancement this close to a
scheduled release.  Maybe something can be done during the next release cycle.

(7) By cj (sqlitening) on 2023-06-21 14:55:24 in reply to 4 [link]

Thank you! Makes entering default entries easy with or without binding.

create table t1(c1 integer primary key,c2 text not null on conflict replace default 'Me');

insert into t1(c2) values(null);
insert into t1 values(null,null);
insert into t1 values(?,?)
insert into t1(c2) values(?)
insert into t1 values(null,?)

(8) By Keith Medcalf (kmedcalf) on 2023-06-21 21:20:41 in reply to 4 [link]

Technically there are three parts.

NOT NULL is a constraint.  
ON CONFLICT REPLACE is a "conflict resolution method"  
DEFAULT value specifies the default value.

The ON CONFLICT REPLACE is applied to resolve the NOT NULL conflict.  It says to "REPLACE" the value (with the default) if the NOT NULL constraint is violated.

You could declare the column as:

`col datatype utterance on conflict replace not null default 7`

for example.  Each phrase stands by itself.  You could also do something like:

`col datatype utterance on conflict rollback not null default 7`

In which case if you did not provide a value for `col` then the default value of `7` would be stored.  If you specified NULL you would have a conflict and the conflict resolution method is `rollback` so the `insert into ... (col) values (null)` would cause the transaction to be cancelled and rolled back.

(9) By anonymous on 2023-06-21 22:53:08 in reply to 8 [link]

>You could declare the column as:

>`col datatype utterance on conflict replace not null default 7`

>for example.  Each phrase stands by itself.

Nope. As [the docs](https://www.sqlite.org/syntax/column-constraint.html) clearly show, a conflict clause can only appear _after_ the keyword(s) specifying what kind of column constraint it is.

(10) By Keith Medcalf (kmedcalf) on 2023-06-22 01:35:13 in reply to 9 [link]

Yes, that is so.

(11) By Holger J (holgerj) on 2023-06-22 19:34:03 in reply to 3.2 [link]

This (pseudo) solution would always replace NULL values by some default value, but in practice it might happen that within in INSERT statement one sometimes wants to really have NULL values and sometimes the default value. Of course this is only possible when there is no NOT NULL constraint.

Therefore, the practice proven solution with DEFAULT as a similar keyword as NULL would be prefereable. Clearer, cleaner and more versatile.