Unable to alter table in SQLite

Question

I have the following fundamental problem in my first approach to SQLite.
I create a table like this:

CREATE TABLE test(ID INTEGER);

I can insert data

INSERT INTO test VALUES(1);

But I am not able to rename

ALTER TABLE test RENAME TO new_test;

Or to add a column

ALTER TABLE test ADD COLUMN year INTEGER;

In both cases I get the following error:

SQL error: near "ALTER": syntax error

Answer 1

SQLite does not permit much DDL to be done on a table after the table has been created. This includes renaming or adding new columns. To do what you want above, create a new table and populate it using the old table's data:

CREATE TABLE newtest (ID INTEGER, year INTEGER);

-- can also populate the year column here
INSERT INTO newtest (ID)
SELECT ID
FROM test;

DROP TABLE test;  -- no need for this table anymore