jeudi 31 mars 2016

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

Aucun commentaire:

Enregistrer un commentaire