samedi 30 janvier 2016

Insert default value for single field with SQLite

Given the following table in SQLite:

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR NOT NULL,
    hair_color VARCHAR NOT NULL DEFAULT 'Green'
)

I'm attempting to perform a batch insert, using the default value for hair_color for only some of the rows. For example, in PostgreSQL or MySQL, it might look like:

INSERT INTO users (name, hair_color) VALUES
    ('Sean', 'Black'),
    ('Tess', DEFAULT)

However, SQLite does not provide a DEFAULT keyword in any other context beyond DEFAULT VALUES. I know that I can do this in two queries as

INSERT INTO users (name, hair_color) VALUES ('Sean', 'Black');
INSERT INTO users (name) VALUES ('Tess');

However, I can't imagine that SQLite doesn't provide some mechanism to execute this in a single query. Is there any equivalent to the DEFAULT keyword from other backends for SQLite?

Aucun commentaire:

Enregistrer un commentaire