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 [source]

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

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

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.

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

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!

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

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] [source]

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!

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

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.