Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Modify a Column's Type in sqlite3

Tags:

sqlite

alter-table

People also ask

Can you modify any table in any way or are there limits in SQLite?

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows these alterations of an existing table: it can be renamed; a column can be renamed; a column can be added to it; or a column can be dropped from it.

How do I edit a table in SQLite?

Summary. Use the ALTER TABLE statement to modify the structure of an existing table. Use ALTER TABLE table_name RENAME TO new_name statement to rename a table. Use ALTER TABLE table_name ADD COLUMN column_definition statement to add a column to a table.

How do I change the primary key in SQLite?

In SQLite, you can not use the ALTER TABLE statement to drop a primary key. Instead, you must create a new table with the primary key removed and copy the data into this new table.

SQLite doesn't support removing or modifying columns, apparently. But do remember that column data types aren't rigid in SQLite, either.

See also:

  • SQLite Modify Column

If you prefer a GUI, DB Browser for SQLite will do this with a few clicks.

  1. "File" - "Open Database"
  2. In the "Database Structure" tab, click on the table content (not table name), then "Edit" menu, "Modify table", and now you can change the data type of any column with a drop down menu. I changed a 'text' field to 'numeric' in order to retrieve data in a number range.

DB Browser for SQLite is open source and free. For Linux it is available from the repository.


It is possible by recreating table.Its work for me please follow following step:

  1. create temporary table using as select * from your table
  2. drop your table, create your table using modify column type
  3. now insert records from temp table to your newly created table
  4. drop temporary table

do all above steps in worker thread to reduce load on uithread


There is a much simpler way:

ALTER TABLE your_main_table ADD COLUMN new_column_name new_column_data_type
UPDATE your_main_table SET new_column_name = CAST(old_column_name as new_data_type_you_want)

Related questions

Get a list of field values from Python's sqlite3, not tuples representing rows
Sqlite convert string to date
Parameter substitution for a SQLite "IN" clause
Escaping chars in Python and sqlite
Android Room compile-time warning about column in foreign key not part of an index. What does it mean?
Aggregating save()s in Django?
Faster bulk inserts in sqlite3?
Variable table name in sqlite
Best Cocoa/Objective-C Wrapper Library for SQLite on iPhone [closed]
SQLite Performance Benchmark -- why is :memory: so slow...only 1.5X as fast as disk?
Find duplicate column value in sqlite
Android P - 'SQLite: No Such Table Error' after copying database from assets
Multiple level ordering
Does a multi-column index work for single column selects too?
android - CursorLoader & SQLite without Content Provider
Create a foreign key in SQLite database browser
No such table android_metadata, what's the problem?
UNIQUE constraint failed: sqlite database : android
Unable to import sqlite3 using Anaconda Python
SELECT INTO statement in sqlite

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!