CODEWITHSUNDEEP
sqlpython-3.xsqlitedatecreate-table
Pro Q
Pro Q

Reputation: 5048

How to add column to database with default

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.)

Upvotes: 10

Views: 7526

Answers (2)

GMB
GMB

Reputation: 222672

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;

Upvotes: 16

Shawn
Shawn

Reputation: 52579

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;

Upvotes: 5

Related Questions