SQLite/SpatiaLite : wrong datatypes with CREATE TABLE .. AS-CodePudding

With SQLite 3.35.2 and SpatiaLite (actually, the setup is the DBManager of QGIS 3.16), I have the following problem :

When creating a table using a CREATE TABLE .. AS SELECT ... , the created table has different datatypes than the original table from which the data is selected.

The original table is data imported from the IGN BD TOPO (free French topographic database), downloaded as geopackage and imported into SQLite using ogr2ogr :

# Name Type Null Default   
0 fid INTEGER Y   
1 cleabs VARCHAR(24) Y   
2 nature VARCHAR Y   
3 usage_1 VARCHAR Y   
4 usage_2 VARCHAR Y   
5 construction_legere INTEGER_BOOLEAN Y 
6 etat_de_l_objet VARCHAR Y  
7 date_creation TIMESTAMP Y  
8 date_modification TIMESTAMP Y  
9 date_d_apparition DATE Y  
10 date_de_confirmation DATE Y 
11 sources VARCHAR Y  
12 identifiants_sources VARCHAR Y  
13 methode_d_acquisition_planimetr VARCHAR Y  
14 precision_planimetrique FLOAT Y  
15 methode_d_acquisition_altimetri VARCHAR Y  
16 precision_altimetrique FLOAT Y  
17 nombre_de_logements INTEGER Y  
18 nombre_d_etages INTEGER Y  
19 materiaux_des_murs VARCHAR(2) Y  
20 materiaux_de_la_toiture VARCHAR(2) Y  
21 hauteur FLOAT Y  
22 altitude_minimale_sol FLOAT Y  
23 altitude_minimale_toit FLOAT Y  
24 altitude_maximale_toit FLOAT Y  
25 altitude_maximale_sol FLOAT Y 
26 origine_du_batiment VARCHAR Y  
27 appariement_fichiers_fonciers VARCHAR(32) Y  
28 geometrie GEOMETRY Y

The query to create the table is :

CREATE TABLE test12 AS 
    SELECT 
        batiments.* , ST_Area(batiments.geometrie) AS "emprise au sol bâtiment (m²)",
        parcelles.fid as parcelle_fid , parcelles.geo_parcel AS parcelle_geo_parcel ,  parcelles."rne_ref ca" AS "identifiant lycée" ,
        (ST_Area(ST_Intersection(parcelles.geom , batiments.geometrie)) / ST_Area(batiments.geometrie))  AS "% emprise bâtiment contenue dans parcelle"
    FROM  'parcelles lycées raura' LIMIT 10 as parcelles
    JOIN "bâtiments bd topo" AS batiments
        ON ST_Intersects(parcelles.geom , batiments.geometrie );
SELECT RecoverGeometryColumn("test12", 'geometrie', 2154, 'MULTIPOLYGON', 'XY');

The resulting table is :

#  Name  Type  Null  Default  
 0  fid  INT  Y    
 1  cleabs  TEXT  Y    
 2  nature  TEXT  Y    
 3  usage_1  TEXT  Y    
 4  usage_2  TEXT  Y    
 5  construction_legere  INT  Y    
 6  etat_de_l_objet  TEXT  Y    
 7  date_creation  NUM  Y    
 8  date_modification  NUM  Y    
 9  date_d_apparition  NUM  Y    
 10  date_de_confirmation  NUM  Y   
 11  sources  TEXT  Y    
 12  identifiants_sources  TEXT  Y    
 13  methode_d_acquisition_planimetr  TEXT  Y    
 14  precision_planimetrique  REAL  Y    
 15  methode_d_acquisition_altimetri  TEXT  Y    
 16  precision_altimetrique  REAL  Y    
 17  nombre_de_logements  INT  Y    
 18  nombre_d_etages  INT  Y    
 19  materiaux_des_murs  TEXT  Y    
 20  materiaux_de_la_toiture  TEXT  Y    
 21  hauteur  REAL  Y    
 22  altitude_minimale_sol  REAL  Y    
 23  altitude_minimale_toit  REAL  Y    
 24  altitude_maximale_toit  REAL  Y    
 25  altitude_maximale_sol  REAL  Y    
 26  origine_du_batiment  TEXT  Y    
 27  appariement_fichiers_fonciers  TEXT  Y    
 28  geometrie  NUM  Y    
 29  emprise au sol bâtiment (m²)    Y   
 30  parcelle_fid  INT  Y    
 31  parcelle_geo_parcel  TEXT  Y    
 32  identifiant lycée  TEXT  Y    
 33  % emprise bâtiment contenue dans parcelle    Y

So, all VARCHAR have become TEXT ; DATE and TIMESTAMP have become NUM ; FLOAT have become REAL ; etc .. Most annoying, the GEOMETRY field also has become NUM.The calculated fields ("emprise au sol bâtiment (m²)" and "% emprise bâtiment contenue dans parcelle") have no datatypes.

Is there a way to keep the original datatypes ?

PS : also, is there a way to have a primary key when using a CREATE TABLE .. AS statement ?

CodePudding user response：

SQLite only has five datatypes. You can indicate other datatypes in the basic CREATE TABLE statement and SQLite will accept those. But it will still interpret each datatype name according to its mapping scheme during processing of the CREATE TABLE statement. When you use CREATE TABLE <> AS statement, SQLite does not copy original DDL definitions, but sets datatypes of the new columns according to previously interpreted and mapped datatype. If you want your new datatype definitions to match originals, you have to use CREATE TABLE with explicit datatypes specified for each column. You can also include explicit schema definition in the CREATE TABLE <> AS statement.

You cannot specify the primary key outside of the schema definition statement either. For both requirements you need to include your schema definition either as a separate statement and then use INSERT INTO / SELECT or as part of your CREATE TABLE <> AS statement.

CodePudding user response：

I found one workaround, albeit cumbersome :

create the table with a regular CREATE TABLE statement, specifying every field datatype.

Then use an INSERT INTO table ( fields list ) SELECT .. statement.

However, this loses the whole elegancy of the CREATE TABLE .. AS statement.