How to insert rows in SQLite using default values?

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.

Answer

You just leave it out of the columns being inserted:

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

Advertisement

Answer