Add column with default including newlines-CodePudding

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';

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?

CodePudding user response：

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.