How to modify spatialite table column names with QGIS?

Question

I am trying to modify column names in a SpatiaLite table. To do that, I run the following SQL statements in the DB Manager :

-- Rename existing table
ALTER TABLE compteur_routier RENAME TO compteur_routier_sav;

-- Create new table with modified column names
CREATE TABLE compteur_routier (
id INTEGER PRIMARY KEY,
geom POINT,
id_compt TEXT,
type_compt TEXT,
angle INTEGER,
id_tmp INTEGER
);

-- Insert data from renamed table
INSERT INTO compteur_routier(id, geom, id_compt, type_compt, angle, id_tmp)
SELECT id, geom, id_compteur, type_compteur, angle, id_tmp FROM compteur_routier_sav;

-- Drop renamed table
DROP TABLE compteur_routier_sav;

This works perfectly in the DB manager, column names are modified and datas are kept. However, when I load the resulting table in QGIS and open the attribute table, I get old column names and datas replaced by column names.

Table before :

Table after :

Answer 1

Have you tried renaming the columns in QGIS using the Properties dialog box? (Right click on spatialite layer > Properties > Source Fields > enable editing, double click on layer name, rename > save layer edits)

You won't be able to rename the pkey column or anything associated with a trigger, but others should be fine (tested in QGIS 3.2)

Answer 2

I tried it and there was no such thing as you said. I think it should be that your SQL is not getting it right. At the time, I found out that for SpatiaLite, if you use the method you used, the geometry will be lost after executing the "ALTER TABLE compteur_routier RENAME TO compteur_routier_sav;" statement. The SQL statement you use is only for SQLite without geometry.

You can use the Refactor fields tool. But when I tested in QGIS 3.2.1, I found that if I use the Refactor fields tool to convert to another SpatiaLite table, I get a failed to check metadata error. The generated SpatialLite table cannot be used. You can use the Refactor fields tool to generate a Shapefile and then export the Shapefile as a SpatiaLite table.I tested it in QGIS 3.2.1.

Answer 3

Thanks to QGIS and SpatiaLite devs, I finally managed to understand what was wrong in my queries : create table with spatial column without using AddGeometryColumn statement. The proper workflow is the following :

-- Create a copy of the table with data
SELECT CloneTable('main', 'compteur_routier', 'compteur_routier_sav', 1);

-- Drop old table (administration tables will be adapted)
SELECT DropGeoTable('compteur_routier');

-- Create and fill new table from cloned table
-- Create new table with modified column names, WITHOUT geom column
CREATE TABLE compteur_routier (
id INTEGER PRIMARY KEY NOT NULL,
id_compt TEXT,
type_compt TEXT,
angle INTEGER,
id_tmp INTEGER
);

-- Add geom column
SELECT AddGeometryColumn('compteur_routier', 'geom', 2154, 'POINT', 'XY', 1);
SELECT CreateSpatialIndex('compteur_routier', 'geom');

-- Insert data from renamed table
INSERT INTO compteur_routier(id, geom, id_compt, type_compt, angle, id_tmp)
SELECT id, geom, id_compteur, type_compteur, angle, id_tmp FROM compteur_routier_sav;
SELECT UpdateLayerStatitics('compteur_routier', 'geom');

-- Drop cloned table (administration tables will be adapted)
SELECT DropGeoTable('compteur_routier_sav'); -- QGIS 3.4.1

Note : DropGeoTable should be replaced by DropTable when SpatiaLite lib will be updated in QGIS. For more detailed information :

QGIS issue

SpatiaLite ticket