Page 1 of 1

Which quotes should I use in SQLite for column names?

Posted: Wed Jan 05, 2011 5:03 am
by Technical
1. Double quotes (") are for identifiers, oddly, when used in ON ... clause, text inside double quotes is considered as literal value.
2. Single quotes (') are for literals, in some cases text inside single quotes can be considered as identifier.
3. Grave accents (`) and Brackets ([]) are for identifiers, but this is not standard SQL and included in SQLite for for compatibility.

I used no quotes, then I decided to use single quotes. I read on SQLite website, that single quotes are meant for literal values, so I changed to double quotes. But today I found that double quotes in JOIN ... ON ... clause case text inside them considered as literal.

What quotes should I use?

P.S. Shame there is no strict rules.

Re: Which quotes should I use in SQLite for column names?

Posted: Wed Jan 05, 2011 5:58 am
by SteveA
I had this same question when I first started with sqlite3.

<rant>
It seems a shame that there's no consistent underpinning, C or something. It just seems that someone just said "make a mysql clone" to a VERY young programmer.

I don't see any other way except ad-hoc. ie, make it work, write it down, try the same thing again next time.

</rant>

ps. I've found that backquotes for fieldnames and single quotes for field values is what I try first. And then go from there wherever you have to.

Re: Which quotes should I use in SQLite for column names?

Posted: Wed Jan 05, 2011 6:07 am
by Technical
Oh, sorry, that was my fault.
"content.id" didn't work, should be content."id".

So, for everyone who has same question:
1) Use double quotes for identifiers
1) Use single quotes for literals

This is SQL standard and must be compatible with all SQL databases.