How to add column to database with default

Question

I have a database that I'm trying to add a column to. This column should hold information of the type timestamp, and I want every row to have the same timestamp (the current time) when I'm done.

I currently have tried:

cursor.execute('''ALTER TABLE my_table ADD COLUMN time timestamp DEFAULT ?''', (datetime.datetime.utcnow(),))

Which results in sqlite3.OperationalError: near "?": syntax error.

So then I tried:

cursor.execute(f'''ALTER TABLE my_table ADD COLUMN time timestamp DEFAULT {datetime.datetime.utcnow()}''')

Which results in sqlite3.OperationalError: near "-": syntax error.

Also, doing

cursor.execute(f'''ALTER TABLE my_table ADD COLUMN time timestamp DEFAULT CURRENT_TIMESTAMP''')

results in sqlite3.OperationalError: Cannot add a column with non-constant default.

How can I add the new column and set the values in that column? (Either through DEFAULT, or some other mechanism.)

Does this answer your question? [sqlite database default time value 'now'](https://stackoverflow.com/questions/200309/sqlite-database-default-time-value-now) — sticky bit, May 23 '20 at 02:58

GMB · Answer 1 · 2023-02-24T19:32:30.820

8

SQLite does not allow adding a new column with a non-constant value. So this:

alter table my_table add column my_time timestamp default current_timestamp;

... generates error:

Cannot add a column with non-constant default

A simple option would be to recreate the table. Assuming that you have single column called id, that would look like:

create table my_table_new(
    id int primary key, 
     my_time timestamp default current_timestamp
);

insert into my_table_new(id) select id from my_table;
drop table my_table;  -- back it up first!
alter table my_table_new rename to my_table;

edited Feb 24 '23 at 19:32

answered May 23 '20 at 03:13

GMB

216,147
25
84
135

but, data will be gone if drop. – Eric Sep 09 '22 at 10:30
@Eric The second query copies all data from the old table to the new data, so no data is lost. – FWDekker Dec 07 '22 at 18:50
Note that this procedure may trigger foreign key constraints and triggers! If you want a foolproof method, read the section "7. Making Other Kinds Of Table Schema Changes" at https://www.sqlite.org/lang_altertable.html – FWDekker Dec 07 '22 at 18:51

score 5 · Accepted Answer · edited May 24 '20 at 04:50

5

You can first add the new column and then update every existing row in the table to the desired value:

ALTER TABLE my_table ADD COLUMN time;
UPDATE my_table SET time = CURRENT_TIMESTAMP;

edited May 24 '20 at 04:50

Pro Q

4,391
4
43
92

answered May 23 '20 at 03:33

Shawn

47,241
3
26
60

I learned the "timestamp" from [this](https://pynative.com/python-sqlite-date-and-datetime/) which was apparently wrong and I should be using the `TEXT` value for my purposes. – Pro Q May 24 '20 at 04:27
Hmm... when I change it to `TEXT`, my formatting no longer works like it used to - I'll have to look into this. It's very odd that an undocumented type would work but the documented type would not. – Pro Q May 24 '20 at 04:43
I figured it out - SQLite does have a ["timestamp" type](https://docs.python.org/2/library/sqlite3.html#default-adapters-and-converters). It is used for the converter to Python's `datetime.datetime`. – Pro Q May 24 '20 at 04:48
@ProQ Small correction: SQLite does not have a "timestamp" type. "timestamp" is an unknown type and therefore is defined as "numeric" in SQLite. Instead of "timestamp" you could also write "kmsnfrwtx" with the same result. Also, those are not types, but type affinities. Check this out: https://www.sqlite.org/datatype3.html#determination_of_column_affinity – Toxiro Aug 11 '22 at 09:40
3

This still doesn't solve the problem of setting value as default to the column – Taosif7 Sep 01 '22 at 06:25

How to add column to database with default

2 Answers2