I’m trying to write add a column to a table in SQLITE with a default string, which includes newline characters. I was under the impression that the following should work:
-- x'0a' is the literal for a new line
ALTER TABLE customer
ADD mailfooter TEXT NOT NULL DEFAULT 'With best regards' || x'0a' || 'Superdupa Company';
in order to produce the following column content:
With best regards
Superdupa Company
This though gives the following error: near "||": syntax error
I then tried adding parenthesis around the DEFAULT value like this:
ALTER TABLE customer
ADD mailfooter TEXT NOT NULL DEFAULT ('With best regards' || x'0a' || 'Superdupa Company');
Now the error is the following: Cannot add a column with non-constant default
Interestingly both variants work when used in a select statement:
SELECT 'With best regards' || x'0a' || 'Superdupa Company';
or
SELECT ('With best regards' || x'0a' || 'Superdupa Company');
both produce the expected result without errors.
How do I add a column with a linebreak in the default value?
>Solution :
The parenthesis around the DEFAULT expression are a mandatory part of the syntax.
This works for me:
sqlite> CREATE TABLE foo(bar, baz NOT NULL DEFAULT ('some' || char(0x0A) || 'text'));
sqlite> INSERT INTO foo(bar) VALUES(1);
sqlite> SELECT * FROM foo;
bar baz
--- ---------
1 some
text
as does using a blob literal in the expression like you tried (('some' || x'0A' || 'text')).
Using ALTER TABLE ... ADD COLUMN ... with the same constraint fails for me on Sqlite 3.35.5, but works on Sqlite 3.38.5. I don’t see anything in the change log between the two versions that directly references this, but apparently something changed in between to make the validity-checking of ALTER TABLE more in line with CREATE TABLE.
So, update to the latest Sqlite release and you should be okay.