QGIS – 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)