CODEWITHSUNDEEP
sqlitequoting
Phi
Phi

Reputation: 814

sqlite column name quoting (feature or bug)

I bumped into something that looks odd to me, but may be I misuse sqlite3.

create table t (v[0] text);
insert into t values('aa');
select * from t;
v
--------
aa
pragma table_info('t')
cid      name     type   no dfl pk
-------- -------- ------ -- --- ----------
0        v        0      0      0
.schema t;
CREATE TABLE t (v[0] text);

Looks like thought the column name is correctly entered into the schema, it is 'wrongly' handled by create/select who silently truncate the column name without any reports.

Now we could think that such odd column name should be entered quoted into the schema, but I can't find a way to do it.

create table u ('v[0]' text);
insert into u values('aa');
select * from u;
v[0]
--------
aa
$ q pragma table_info('u')
cid      name     type   no dfl pk
-------- -------- ------ -- --- ----------
$        v[0]     text   0      0
$ q .schema u
CREATE TABLE u ('v[0]' text);

Hurray, the schema looks good, SELECT * is happy, but now I get stuck about how to use this column.

select v[0] from u;
SQL error 1 : no such column: v
select 'v[0]' from u
'v[0]'
----------
v[0]
select [v[0]] from u
SQL error 1 : unrecognized token: "]"

I really to to have [] in the column name and I don't see any docco saying we can't (columns names are generated in my case)

Thanx for any advise. Cheers Phi

Upvotes: 1

Views: 3009

Answers (1)

CL.
CL.

Reputation: 180080

SQLite supports several ways of quoting table/column names, but [name] and 'name' are supported only for compatibility with other databases, and single quotes can be misinterpreted as a string literal. The correct way of quoting is to use double quotes:

SELECT "v[0]" FROM u;

Upvotes: 4

Related Questions