How to insert rows in SQLite using default values?

Question

Update:

Here is what I have learned from the answers below...

SQLite supports defining default values on columns. However, it does not support any form of DEFAULT keyword or function in queries. So, in effect, it is not possible to do a MySQL style bulk insert where a column is set explicitly in some rows, and the column default is used in other rows.

There are several creative workarounds listed below, but for simplicity's sake I most prefer Shawn's suggestion of using an INSERT statement per row, possibly all wrapped inside a BEGIN TRANSACTION statement. Only this will allow you to utilize column defaults.

Original Question:

I have a simple SQLite table named todo with this schema...

I can insert rows into this table with this syntax...

INSERT INTO 'todo' ('title', 'complete', 'notes') VALUES
  ('Pickup dry cleaning', true, 'They close at 7pm'),
  ('Clean apartment', false, 'Floors have already been swept'),
  ('Finish homework', true, 'Chapters 3 and 4');

However,

I can't figure out how to omit certain values and let the database use the default for that column.

For example, the complete column has a default of 0. It would be nice to be able to pass undefined as one of the values in my query, thus signaling to SQLite that I'd like to use whatever the column default is, but I get a Result: no such column: undefined error when I try this.

null would not work in this case either, because sometimes you want to explicitly set the value of a cell to null. You can't just plain omit the value either because the parameters are positional.

FYI PostgreSQL, Microsoft SQL and Oracle, as well as MySQL and MariaDB all support the DEFAULT keyword. It’s not called SQLite for nothing. I suppose the hosting application is expected to sort that out. — Manngo
– Manngo, Commented Mar 18, 2023 at 2:19

Shawn · Accepted Answer · 2019-11-04 14:26:34Z

8

You just leave it out of the columns being inserted:

INSERT INTO todo(title, notes) VALUES ('Music practice', 'Not Elvis');

answered Nov 4, 2019 at 14:26

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

J. Munson Over a year ago

Shawn, I guess the issue with this solution is that I don't think it would work with multiple rows (i.e. bulk insert) where in some rows you want to explicitly set the value of a column, but in other rows you want to use the default.

Shawn Over a year ago

@J.Munson Use different insert statements for different combinations of values being inserted.

LovelyHanibal Over a year ago

For other people searching this you can enter all values as default like this: INSERT INTO todo DEFAULT VALUES;

peak · Accepted Answer · 2023-03-19 05:52:18Z

1

I can't figure out how to omit certain values and let the database use the default for that column.

As of this writing, SQLite does not support the default(_) function, but you could use triggers to achieve the effect you want, though doing so is a bit cumbersome.

Here's an example:

CREATE TRIGGER IF NOT EXISTS onnull
AFTER INSERT ON mytable
FOR EACH ROW
WHEN NEW.column = 'undefined'
BEGIN
    UPDATE mytable 
    SET column = 'mydefault' 
    WHERE rowid=NEW.rowid ;
END;

——

Footnote: one can determine the DEFAULT value by:

SELECT dflt_value
FROM pragma_table_info('mytable')
WHERE name = 'column';

So (still using 'column' and 'mytable' to represent respectively the column name and table of interest) you could write:

CREATE TRIGGER IF NOT EXISTS onnull
AFTER INSERT ON mytable
FOR EACH ROW
WHEN NEW.column = 'undefined'
BEGIN
    UPDATE mytable 
    SET column = (SELECT dflt_value
      FROM pragma_table_info('mytable')
      WHERE name = 'column')
    WHERE rowid=NEW.rowid ;
END;

edited Mar 19, 2023 at 5:52

answered Nov 4, 2019 at 16:56

peak

119k21 gold badges185 silver badges218 bronze badges

5 Comments

Manngo Over a year ago

Good solution. I don’t want to tamper with your answer, but I think it would make sense to use the string 'default' to resemble the behavior other DBMS.

peak Over a year ago

'mydefault' is only meant as a placeholder for whatever default value is appropriate.

Manngo Over a year ago

No, I meant the undefined string. In other SQLs the dummy value is DEFAULT.

peak Over a year ago

The Q specifically asked for a trigger on undefined: “ It would be nice to be able to pass undefined as one of the values in my query”

Michael Feb 5 at 9:08

out of curiousity why is it necessary to do a full UPDATE query just to find the field to update, when we already have the new record in NEW? Why not just do: "NEW.column='mydefault'"? It seems valid syntax as I don't get any errors when I try this, but afterwards the column value isn't properly set even if the trigger is BEFORE INSERT.

Osei-Owusu · Accepted Answer · 2021-08-30 04:45:16Z

0

this works if you are using better-sqlite3, and I think it will equally work for sqlite3.

db.prepare("INSERT INTO bugs (title, notes) VALUES (?, ?)").run(["Good Book", "nice test"]);

answered Aug 30, 2021 at 4:45

Osei-Owusu

3013 silver badges7 bronze badges

Comments

Lalati · Accepted Answer · 2019-11-04 14:39:01Z

-1

What Shawn said plus the bulk issue can be solved with DEFAULT value, that is if you are inserting all records manually, if you want it dynamically through a loop for example you must declare a variable as default and change to the specific value if the specific value exists, and use this variable in the query.

String var='DEFAULT';
if (Value exists) {var=Value;}

sql=inset into x(x,y,z) values (a,b,var);

answered Nov 4, 2019 at 14:39

Lalati

3263 silver badges16 bronze badges

Collectives™ on Stack Overflow

How to insert rows in SQLite using default values?

4 Answers 4

3 Comments

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related