SQLite: Small, Fast, Reliable – Choose any three

Re:SQLite: Small, Fast, Reliable – Choose any three by est on Monday July 14, 2014 @10:14PM (#8034372)

Things I dislike about sqlite:

1. Can not rename column, in a fast iteration project, renaming schema is quite common

2. No easy way to upsert data. There should be an easy equivalent to MySQL's "INSERT ... ON DUPLICATE KEY UPDATE". If you use REPLACE you got PK changed.

[ Parent | on HN ]

Re:SQLite: Small, Fast, Reliable – Choose any three by kennywinker on Tuesday July 15, 2014 @02:51AM (#8035134)

Just today I encountered an "insert or update" situation and handled it like this:

    INSERT OR IGNORE INTO table_name (item_key, item_count) VALUES (?, 0);
    UPDATE table_name SET item_count = item_count+1 WHERE item_key = ?;

I'm not familiar with "upsert" so maybe there is a difference, but this worked well for my use case (which was "increment or insert then increment"). You could even reverse the operations (by adding OR IGNORE to the UPDATE statement), if you wanted to not do two operations if the pk didn't exist yet.

[ Parent | on HN ]

Re:SQLite: Small, Fast, Reliable – Choose any three by ianamartin on Tuesday July 15, 2014 @12:56AM (#8034860)

Postgres still doesn't have upsert either. Neither does sql server or Oracle. I hardly see that as a knock against SQLite. All of the above have various levels of hackey workaround so for this though.

[ Parent | on HN ]

Re:SQLite: Small, Fast, Reliable – Choose any three by aratob on Tuesday July 15, 2014 @02:14AM (#8035050)

Oracle actually has upsert functionality. It is called Merge and has been available for quite a long time: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statem...

[ Parent | on HN ]