2

Could anybody tell me how I can change the datatype in SQLite from android?

For example, I want to change varchar(2000) of a column diary_content in table diary to TEXT. How it is possible?

Mangesh
  • 5,491
  • 5
  • 48
  • 71
Frank
  • 2,738
  • 2
  • 14
  • 19
  • http://stackoverflow.com/questions/2083543/modify-a-columns-type-in-sqlite3 – Amit Vaghela Feb 25 '16 at 04:04
  • `varchar(2000)` and `TEXT` have exactly the same [affinity](http://www.sqlite.org/datatype3.html#affinity), so you do not need to change the table at all. – CL. Feb 25 '16 at 08:48
  • Hi ok so you think if i go over 2000 chars and want to save the text it will still work too ? – Frank Feb 25 '16 at 16:44

1 Answers1

0

Trick that might solve your issue,

You can try something like

Ex:

To change the datatype of the last_name field to VARCHAR, could do the following:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE employees RENAME TO _employees_old;

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE
);

INSERT INTO employees (employee_id, last_name, first_name, hire_date)
  SELECT employee_id, last_name, first_name, hire_date
  FROM _employees_old;

COMMIT;

PRAGMA foreign_keys=on;

Rename existing employees table to _employees_old. Then it will create the new employees table with the last_name field defined as a VARCHAR datatype. Then it will insert all of the data from the _employees_old table into the employees table.

Check SQLite for more details.

Amit Vaghela
  • 22,772
  • 22
  • 86
  • 142
  • OK thanx ...have to make some brainstorming about this :) I think you have to be sure 100 % in advance which data type you want to choose in sqlite before ! Seems not to be very easy to change it afterwards – Frank Feb 25 '16 at 04:34