SQLite: create table

No datatypes necessary

When a table is created in SQLite, it is not necessary to specify a data type for its columns (although SQLite supports five data types).

The SQLite documentation calls this feature flexible typing.

Because a data type is not required for columns, a column can store any of the five SQLite data types, as shown in the following example:

create table table_abc (col_1, col_2, col_3);

insert into table_abc values ('foo', 'bar', 'baz');
insert into table_abc values (   1 ,   42 ,   20 );

.mode   column
.width  10 10 10
.header on
select * from table_abc;
--
-- col_1       col_2       col_3     
-- ----------  ----------  ----------
-- foo         bar         baz       
-- 1           42          20        
--

select
   typeof(col_1) t_1,
   typeof(col_2) t_2,
   typeof(col_3) t_3
from
   table_abc;
--
-- t_1         t_2         t_3       
-- ----------  ----------  ----------
-- text        text        text      
-- integer     integer     integer   
--

Github repository about-sqlite, path: /tables/no-data-types.sql

Although no datatypes are necessary when the table is created, the columns do have a type affinity that determines in what type the inserted value is actually stored.

SQLite 3.37.0 (2021-11-27) added strict tables which enforce the data type that is defined in the create table statement. The data types that are allowed in such tables are restricted to integer (or int), real, text, blob, any:

create table T (
  … 
) STRICT;

Create table if it doesn't exist

create table if not exists tq84_table (
  col_1 text,
  col_2 int
);

.schema tq84_table
--  
-- CREATE TABLE tq84_table (
--   col_1 text,
--   col_2 int
-- );

create table if not exists tq84_table (
  col_3 date,
  col_4 text,
  col_5 int
);

.schema tq84_table
--  
-- CREATE TABLE tq84_table (
--   col_1 text,
--   col_2 int
-- );


drop table tq84_table;

Github repository about-sqlite, path: /tables/create_if_not_exists.sql

Rolling back create table statements

In SQLite, the creation of a table can be rolled back.

Specifying collations for columns

SQLite allows to specify a collation for columns when a table is created. This influences the case sensitiveness when querying the table:

create table tab (
  col_txt_default    text,
  col_txt_binary     text   collate binary,
  col_txt_nocase     text   collate nocase
);

insert into tab values ('foo', 'foo', 'foo');
insert into tab values ('Foo', 'Foo', 'Foo');
insert into tab values ('FOO', 'FOO', 'FOO');

.print --
select * from tab where col_txt_default = 'Foo';
-- 
-- Foo|Foo|Foo

.print --
select * from tab where col_txt_binary  = 'Foo';
-- 
-- Foo|Foo|Foo

.print --
select * from tab where col_txt_nocase  = 'Foo';
-- 
-- foo|foo|foo
-- Foo|Foo|Foo
-- FOO|FOO|FOO

Github repository about-sqlite, path: /tables/create-collate.sql

SQLite: create table

No datatypes necessary

Create table if it doesn't exist

Rolling back create table statements

Specifying collations for columns

See also