Any column can still store any type of data but the preferred storage class for a column is called its affinity. SQLite supports the concept of type affinity on columns. The INTEGER storage class, for example, includes 6 different integer datatypes of different lengths. SQLite storage class is slightly more general than a datatype.

The value is a blob of data, stored exactly as it was input. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE) The value is a floating point value, stored as an 8-byte IEEE floating point number. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. SQLite Storage ClassesĮach value stored in an SQLite database has one of the following storage classes − Sr.No. In SQLite, the datatype of a value is associated with the value itself, not with its container. SQLite uses a more general dynamic type system. You would use these data types while creating your tables. Each column, variable and expression has related data type in SQLite. The big question is if whether this will be slower than other methods on a large table, but I'm lazy to benchmark now.SQLite data type is an attribute that specifies the type of data of any object. Perhaps this will be the best method if you need anything slightly more flexible. So 1 and 01 both appear to be the correct numerical 1 as desired as far as I can tell.Īnything else becomes random binary noise that I don't fully understand or care about right now. Sqlite3 tmp.sqlite '.mode line' 'select x, x+1, x*2, x=0, x=1 from t' Sqlite3 tmp.sqlite '.import -csv tmp.csv t' Then import: sqlite3 tmp.sqlite 'create table t(x integer)' but I wanted to make some quick edge case tests to understand better what works and what doesn't: import -csv with pre-existing integer column Other methods might require you delete the header either before or after importing the csv file. If you do it this way, you don't have to worry about headers in csv file being imported as data. SELECT 'employee_id', 'last_name', 'first_name', 'hire_date'įinally, we can execute SQL in alterTable.sql and drop the old renamed table $ sqlite3 mydb.sqliteĪt this point, the imported employee data should have the correct data types instead of the default text field. INSERT INTO employees ('employee_id', 'last_name', 'first_name', 'hire_date') Let's now create a SQL filed called alterTable.sql that would rename the table, create a new table, and copy the data into the new table.ĪLTER TABLE employees RENAME TO _employees_old You should now have employees.sql with the following schema: CREATE TABLE employees( Let's first get the employees schema from the database and save it to can use this to create a new script that would rename the table, create a new table, and copy the data into the new table. # unfortunately, sqlite assumes all fields are text fieldsĪt this point, the data is imported as text.

# import data from 'employees.csv' into a SQLite table called 'employees' # create sqlite database called mydb.sqlite employee_id,last_name,first_name,hire_dateįirst, create a SQLite database called mydb.sqlite and import employees.csv into a SQLite table called employees. So suppose I have an employees.csv file I want to import into SQLite database with the correct data types. Instead, you will need to rename the table, create a new table, and copy the data into the new table. However, it is my understanding that you cannot use the ALTER TABLE statement to modify a column in SQLite. So you need to perform some extra steps in order to set the correct data types. When importing csv files, SQLite assumes all fields are text fields.

Sqlite data types