Repository navigation
Export Database in SQL file : ERROR, Insert into generated column #2978
Description
Details for the issue
What did you do?
Create a table
CREATE TABLE IF NOT EXISTS TestVector (
uid INTEGER,
price REAL,
num INTEGER,
total GENERATED ALWAYS AS (price*num) STORED
);
After export the database to SQL file, I can see the following:
INSERT INTO "spectacle_Commande" ("uid","price","num","total") VALUES (1,5.5,100,550);
What did you expect to see?
the total column should be excluded from the export file
What did you see instead?
When I import the sql file, an error occurs :
Résultat : cannot INSERT into generated column "total"
SAME Issue for
total GENERATED ALWAYS AS (price*num) VIRTUAL
Useful extra information
DB4S v3.12.2 [built for x86_64-little_endian-llp64] on Windows 10 (10.0) (winnt/10.0.18363) [x86_64]
using SQLCipher Version 4.4.0 community (based on SQLite 3.34.1)
and Qt 5.12.8
Activity
chrisjlocke commentedon Feb 13, 2022
I'd probably say the reverse. You've told SQLite (or DB4S in this case) to export a table, so it should export all columns.
When you import, you decide what columns to import and how to map them to an existing table.
So when importing, just ignore the computed column.
You've created testVector, but then your SQL is 'insert into spectacle_commande' - they're different tables.
So if you're exporting from tableA to import into tableB, you have to specify what fields you want to input. This is quite common, as most tables have an auto-increment field as the ID field, and you'll encounter the same issue - you can't specify that as an input column.
Leave generated columns out of Export to SQL