Change default floating point column type from FLOAT to REAL

[simonw]

This is a bug in 3.x but it only causes problems with strict tables. In 4.0 we should switch from FLOAT to REAL in the CREATE TABLE used for all floating point columns.

Split from #644 (comment)

[chapmanjacobd]

            if strict and column_type_str == "FLOAT":
                column_type_str = "REAL"

also needs to be in Table.add_column

[simonw]

I want CLI commands like sqlite-utils transform and sqlite-utils create-table and sqlite-utils add-column to continue to accept float as an argument in addition to accepting the new real.

[simonw]

Claude Code: https://gistpreview.github.io/?78fa00ff49399001e07b5aa3f499d503

[simonw]

Manually testing this:

echo "name,age,weight
Cleo,6,45.5
Dori,1,3.5" > creatures.csv

uv run sqlite-utils insert \
  creatures.db creatures creatures.csv --csv --detect-types

uv run sqlite-utils schema creatures.db

This isn't working correctly yet:

 CREATE TABLE "creatures" (
    "name" TEXT,
    "age" INTEGER,
    "weight" FLOAT
 );

That should be REAL.