SQLite default value if null

Question

Let's say I have a table called "table"

So

Create Table "Table" (a int not null, b int default value 1)

If I do a "INSERT INTO "Table" (a) values (1)". I will get back 1 for column a and 1 for column b as the default value for column b is 1.

BUT if I do "INSERT INTO "Table" (a, b) values (1, null)". I will bet back 1 for column a and an empty value for column b. Is there a way to set a column's default value if a null was given?

Answer 1

1. If the column with the default value can have the NOT NULL constraint, then you can:

   • create the table using this syntax:

     CREATE TABLE "Table" (a INT NOT NULL, b INT NOT NULL ON CONFLICT REPLACE DEFAULT 1);

     and insert as usual

   • create the table as in your question

     and insert using this syntax:

     INSERT OR REPLACE INTO "Table" (a, b) VALUES(1, null);

https://database.guide/convert-null-values-to-the-columns-default-value-when-inserting-data-in-sqlite/

2. If the column with the default value can not have the NOT NULL constraint (allowing NULL to be inserted), as in your question:

   you will have to omit the column with the default value from the insert query so that it gets its default.

Ideal would be:

INSERT INTO "Table" (a, b) VALUES(1, COALESCE(NULL, DEFAULT))

, as is in other sql dialects, which might be supported in future release: https://sqlite.org/forum/info/d7384e085b808b05

Answer 2

No, if you are doing:

INSERT INTO my_table (a, b) values (1, null) 

You are explicitely asking for a null value on b column.

In a RDBMS you could technically use a trigger to override that behavior. But in SQLite you can't.