ALTER TABLE after adding data

Here we have a table with two fields (id and name) and with some data in it. Then we inserted some data.

Then we execute ALTER TABLE to add another column.

After that we can add new rows, this time already supplying the new field as well.

The old rows will have NULL in the new field.

We can also update the old rows to have some value in the field using the UPDATE command.

CREATE TABLE person (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

INSERT INTO person (name) VALUES ('Joe');
INSERT INTO person (name) VALUES ('Jane');

SELECT * FROM person;


ALTER TABLE person ADD COLUMN phone TEXT;

SELECT * FROM person;

INSERT INTO person (name, phone) VALUES ('Marcus', '1234565');

SELECT * FROM person;

sqlite3 < exmples/alter-table-after-adding-data.sql

1|Joe
2|Jane
1|Joe|
2|Jane|
1|Joe|
2|Jane|
3|Marcus|1234565

╭────┬──────╮
│ id │ name │
╞════╪══════╡
│  1 │ Joe  │
│  2 │ Jane │
╰────┴──────╯
╭────┬──────┬───────╮
│ id │ name │ phone │
╞════╪══════╪═══════╡
│  1 │ Joe  │ NULL  │
│  2 │ Jane │ NULL  │
╰────┴──────┴───────╯
╭────┬────────┬───────────╮
│ id │  name  │   phone   │
╞════╪════════╪═══════════╡
│  1 │ Joe    │ NULL      │
│  2 │ Jane   │ NULL      │
│  3 │ Marcus │ '1234565' │
╰────┴────────┴───────────╯