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

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!

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>

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!

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.

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 clearly show, a conflict clause can only appear after the keyword(s) specifying what kind of column constraint it is.

Yes, that is so.

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.

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.

Hi, I would like to mention this would indeed be a nice-to-have feature.

++

It's more than just a nice-to-have, AFAICT there's no principled way right now to construct new record that is partially filled in and otherwise defaults based on a SELECT clause. The REPLACE conflict resolution strategy approach doesn't work for nullable columns or when a different strategy is required. COALESCE(NULL, DEFAULT) or similar would address these edge cases.

Btw I'd love to be wrong. I would love for this to already be possible.

there's no principled way right now to construct new record that is partially filled in

1. Why insert into a column that has a default value only for it to inherit that value? (Substituting 100 for DEFAULT in the SQL Statement for column f in my example)

2. What would be the value of a column that has no default value but whose insert value is specified as DEFAULT?

In order to get records that are partially filled - that is, inherit the default value - I insert only into columns that have non-default values. Something like this:

sqlite> create table tblxy(f int not null default 100,g text not null default 'sqlite',thisrec int);
sqlite> insert into tblxy(thisrec) Values(23),(24);
sqlite> select * from tblxy;
f    g       thisrec
---  ------  -------
100  sqlite  23
100  sqlite  24

What would be the value of a column that has no default value

Every column has a default value, usually NULL with the exception of an INTEGER PRIMARY KEY column where the default is the next available integer.

Your example does not work if f or g can sensibly be null. Perhaps someone has a date column with a default of CURRENT_TIMESTAMP, which could be null if rows are added for something that happened at an unknown time.

Edit: it took me a bit to figure out the usefulness of this, and it doesn't (currently) seem to work in the default-in-values branch. If you want to conditionally use the default while maintaining the ability to use null, you're out of luck.

CREATE TABLE t (
  DefaultableColumn TEXT DEFAULT 'Whatever' -- can be null
);
INSERT INTO t (DefaultableColumn) SELECT IIF(<condition>, 'Some Value', DEFAULT);
Parse error: near "DEFAULT": syntax error
INSERT INTO t (DefaultableColumn) VALUES (IIF(<condition>, 'Some Value', DEFAULT));
Parse error: near "DEFAULT": syntax error

INSERT INTO t (DefaultableColumn) SELECT IIF(<condition>, 'Some Value', DEFAULT);

If the INSERT statement specified several columns, the SQL parser will have to be pretty smart to determine the appropriate DEFAULT) value.

Assume that table t has, say, 3 columns; consider:

INSERT INTO t select 'arbitraryValue', IIF(<condition>, 'Some Value', DEFAULT¹,IIF(<condition>, 'Other Value', DEFAULT²)); -- column names not enumerated

¹ Comes from the 2nd column -- being the 2nd value being selected

² Comes from the 3rd column -- being the 3rd value being selected

Sure it is possible (probably at the cost of slowing the queries) but:

1. There is nothing that keeps the SQL statement (probably buried in application code) in-line with the database schema (specifically the table definition) in the event that the table definition is altered.
2. If the column names are enumerated i.e. INSERT INTO t(cx,xy,cz) (and the table had n, say 50, columns), the second and third values in the select statement will correspond to different ordinals depending on how the table is defined.

In short, won't this feature introduce a whole set of likely run time errors?

Will this apply to CTEs?

I think both points 1 and 2 are covered by statements automatically recompiling themselves when the schema changes (provided sqlite_prepare_v2/v3 was used).

As for the first and last sentences, I'm with you on both. It might be fine though.

As for CTEs, I don't think there'd be sense in that?

I only seldomly had the need for that feature.

If I really need that, there is a simple work-around:

BEGIN TRANSACTION;
INSERT INTO table1 DEFAULT VALUES;
UPDATE table1 SET (col1,col2) = (val,val2);
COMMIT TRANSACTION;

Where val1 and val2 can be expressions of course.

DRH has been working on this, and it apparently seems to be working now. You'll have to build from the default-in-values branch if you would like to test it yourself.

The first commit on the branch says "There is a lot of extra code space consumed for such an obscure feature" so it could quite easily not make it into trunk. You may want to bring out your best, most convincing, arguments if you actually want this (although I know nothing, so maybe it'll be added no matter what for one reason or another).

My changes are on a branch. I'm reluctant to merge them, yet. Concerns:

1. The current implementation follows Postgres syntax, which means that the DEFAULT keyword can only occur as an isolated keyword in the VALUES clause of an INSERT. In particular, DEFAULT can not be part of a larger expression, such as suggested in post 19 above, for example.

2. I have only just now discovered that Postgres also allows the DEFAULT keyword on the RHS of the "fieldname=" operation in an UPDATE statement. My implementation does not currently support that.

3. I still don't see how this extra DEFAULT capability is especially useful. But I am acutely aware of the extra complication it adds to the code, which inclines me to leave it out.

Regarding item 1 above: An alternative implementation, which would actually be somewhat simpler to implement, it to say that DEFAULT can be used anywhere that NULL can appear, and it means the same as NULL, except that if it ends up being used as the value to insert into or update a column, the NULL magically changes into the default value of that column. This would allow DEFAULT to be used in lots of crazy places. But that is not Postgres compatible.

It also seems like people will soon be clamoring for a new "sqlite3_bind_default()" API at the C-language level. When will the madness end?

When will the madness end?

For me this is a brilliant workaround and will be using that instead of this sort of construction.

• It is more robust.
• Eliminates the possibility of errors arising from the underlying table being re-factored.

The original branch for this has been closed and will be abandoned. I found a new way to implement the DEFAULT behavior that is cleaner (read: easier to maintain long-term) and faster. And as far as I can see, it sticks closer to Postgres syntax.

On the default-in-values-2 branch there is code that allows the DEFAULT keyword to appear in following places:

• As a complete term in a VALUES clause on an INSERT or REPLACE statement.
• On the right-hand side of a SET term in an UPDATE statement.

Hence you can say things like:

INSERT INTO t1(e,a,x) VALUES(1,2,3),(4,DEFAULT,5);
UPDATE t2 SET e=1, a=DEFAULT, x=random() WHERE id=22;

If the DEFAULT keyword appears anywhere else (other than in the column definition of a CREATE TABLE or ALTER TABLE ADD COLUMN statement of course), then it is a syntax error. DEFAULT cannot be part of a larger expression. This is how Postgres behaves, so I'm guessing that is what the SQL standards require. Correct me if you think the previous sentence is wrong.

I feel like the new branch has a much greater chance of landing on trunk someday. Meanwhile, y'all are all encouraged to try to break it, or to find differences in what the branch does and what PostgreSQL does.

Thanks for the quick brainstorming, much appreciated!

Regarding use-case, I'm trying to model conditional inserts, something akin to:

INSERT INTO t1 SELECT DEFAULT VALUES WHERE <condition>;

or more selectively

INSERT INTO t1 SELECT (DEFAULT AS c1, 42 AS c2) WHERE <condition>;

the condition could for example be an ACL check, e.g. in the same transaction check a user id parameter against a user/permissions table and only then insert. I don't control the conflict resolution strategy, since it's user defined. Today, my program first does an ACL check and then the insert. Doing it in the same transaction would avoid a race.

Compiled successfully on Windows 11

Your first thing

SQLite version 3.50.0 2025-02-15 23:47:25
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> drop table if exists t1;
sqlite> .mode columns
sqlite> .headers on
sqlite> CREATE TABLE t1 (e INTEGER DEFAULT 1 NOT NULL, a INTEGER DEFAULT 2 NOT NULL, x INTEGER DEFAULT 3 NOT NULL);
sqlite> INSERT INTO t1(e,a,x) VALUES(1,2,3),(4,DEFAULT,5);
sqlite> select * from t1;
e  a  x
-  -  -
1  2  3
4  2  5

Your second thing

sqlite>CREATE TABLE t2 (e INTEGER DEFAULT 1 NOT NULL, a INTEGER DEFAULT 2 NOT NULL, x INTEGER DEFAULT 3 NOT NULL);
sqlite> insert into t2 select * from t1;
sqlite> UPDATE t2 SET e=1, a=DEFAULT, x=random() WHERE rowid=2;
sqlite> select * from t2;
e  a  x
-  -  --------------------
1  2  3
1  2  -5184498451065793091

Why is the random() value negative?

Why is the random() value negative?

See random() which says:

The random() function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.