Valid characters for indentifiers

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

Valid characters for indentifiers

Bart Smissaert
What are the exact rules for valid identifier names (tables, columns and
indexes)?
This is both for names enclosed in square brackets ([]) or double quotes
(") and also for names
that are not enclosed within square brackets or double quotes.
Had a good look for this, but couldn't find a clear answer.

RBS
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Valid characters for indentifiers

Simon Slavin-3


On 11 Nov 2017, at 11:04am, Bart Smissaert <[hidden email]> wrote:

> What are the exact rules for valid identifier names (tables, columns and
> indexes)?

There is no documentation for this.  Which means that even if you find that, say, macrons are allowed in this version, they might not be allowed in the next version.

> This is both for names enclosed in square brackets ([]) or double quotes
> (") and also for names
> that are not enclosed within square brackets or double quotes.

Please note this distinction:

A) The names Col1 and "Col1" are both allowed and refer to the same entity.

B) The names Col1 and "Col1" are both allowed and refer to different entities.

Some SQL engines do one, some do the other.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Valid characters for indentifiers

Kees Nuyt
In reply to this post by Bart Smissaert
On Sat, 11 Nov 2017 11:04:37 +0000, Bart Smissaert
<[hidden email]> wrote:

> What are the exact rules for valid identifier names (tables, columns and
> indexes)?
> This is both for names enclosed in square brackets ([]) or double quotes
> (") and also for names
> that are not enclosed within square brackets or double quotes.
> Had a good look for this, but couldn't find a clear answer.

It conforms to the SQL standard, you can use the Postgresql docs
as a reference.

https://www.postgresql.org/docs/7.1/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS

If you want to use a keyword (or an otherwise invalid word) as
an identifier, you have to quote it. SQLite has its own lost of
keywords.
https://sqlite.org/lang_keywords.html

--
Regards,

Kees Nuyt
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Valid characters for indentifiers

Simon Slavin-3


On 11 Nov 2017, at 4:40pm, Kees Nuyt <[hidden email]> wrote:

> It conforms to the SQL standard, you can use the Postgresql docs
> as a reference.
>
> https://www.postgresql.org/docs/7.1/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS

"the SQL standard will not define a key word that contains digit"

Does that mean that column names like "column1" shouldn’t work ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Valid characters for indentifiers

R Smith
In reply to this post by Bart Smissaert
On 2017/11/11 1:04 PM, Bart Smissaert wrote:
> What are the exact rules for valid identifier names (tables, columns and
> indexes)?
> This is both for names enclosed in square brackets ([]) or double quotes
> (") and also for names
> that are not enclosed within square brackets or double quotes.

The question is phrased quite generally, but if you perhaps meant w.r.t.
SQLite specifically - It's quite straight forward:

An Identifier can be any text, including non-standard characters
(Unicode Chinese etc.) and including no text at all (empty string) as
long as it is quoted with Identifier-compatible quotation marks.

Valid Identifier Quotation characters are:  [, ], ` and "

Further to this, an Identifier can remain unquoted (plain text), except:
- when it is empty text or
- when it contains any characters outside the ASCII Alpha-numeric
characters and Underscore, or
- when it starts with a number (digit), or  (Suddenly not 100% sure of
this, may need to check it)
- when it is the same as an SQLite Keyword.

Further to this, SQLite will be very forgiving if you use the wrong type
of quotes in some circumstances (where the intention is obvious), or
have an identifier that is the same as a keyword but you only use it in
a phrase where the keyword can't possibly be found.

Read more about it here:
http://sqlite.org/lang_keywords.html


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Valid characters for indentifiers

R Smith
In reply to this post by Simon Slavin-3


On 2017/11/11 6:43 PM, Simon Slavin wrote:

>
> On 11 Nov 2017, at 4:40pm, Kees Nuyt <[hidden email]> wrote:
>
>> It conforms to the SQL standard, you can use the Postgresql docs
>> as a reference.
>>
>> https://www.postgresql.org/docs/7.1/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS
> "the SQL standard will not define a key word that contains digit"
>
> Does that mean that column names like "column1" shouldn’t work ?

No, it simply means that the folks designing the SQL standard promised
they would never make a new Keyword as part of the spec that will
contain number digits and force it upon us. It has nothing to do with
what you call your identifiers.

>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Valid characters for indentifiers

Bart Smissaert
In reply to this post by R Smith
Yes, that this is mainly for SQLite and thanks for explaining and that is
what I thought.
I think square brackets are better than double quotes as it looks better
and parsing SQL
gets easier as the opening and closing character are not the same. But then
for compatibility
with other DB's double quotes are better.

RBS

On Sat, Nov 11, 2017 at 4:52 PM, R Smith <[hidden email]> wrote:

> On 2017/11/11 1:04 PM, Bart Smissaert wrote:
>
>> What are the exact rules for valid identifier names (tables, columns and
>> indexes)?
>> This is both for names enclosed in square brackets ([]) or double quotes
>> (") and also for names
>> that are not enclosed within square brackets or double quotes.
>>
>
> The question is phrased quite generally, but if you perhaps meant w.r.t.
> SQLite specifically - It's quite straight forward:
>
> An Identifier can be any text, including non-standard characters (Unicode
> Chinese etc.) and including no text at all (empty string) as long as it is
> quoted with Identifier-compatible quotation marks.
>
> Valid Identifier Quotation characters are:  [, ], ` and "
>
> Further to this, an Identifier can remain unquoted (plain text), except:
> - when it is empty text or
> - when it contains any characters outside the ASCII Alpha-numeric
> characters and Underscore, or
> - when it starts with a number (digit), or  (Suddenly not 100% sure of
> this, may need to check it)
> - when it is the same as an SQLite Keyword.
>
> Further to this, SQLite will be very forgiving if you use the wrong type
> of quotes in some circumstances (where the intention is obvious), or have
> an identifier that is the same as a keyword but you only use it in a phrase
> where the keyword can't possibly be found.
>
> Read more about it here:
> http://sqlite.org/lang_keywords.html
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Valid characters for indentifiers

Richard Hipp-3
In reply to this post by R Smith
On 11/11/17, R Smith <[hidden email]> wrote:
>
> Further to this, an Identifier can remain unquoted (plain text), except:
> ....
> - when it is the same as an SQLite Keyword.

Correct.

Unfortunately, we do occasionally add new keywords.  The most recent
example is version 3.8.3 (2014-02-03) when we added support for common
table expressions, which required two new keywords:  WITH and
RECURSIVE.

In order to make sure that new keywords do not break legacy
applications that might be using those keywords as identifiers, the
parser is rather forgiving of the misuse of keywords as identifiers.
Whenever a keyword token is encounter in a context where an identifier
would make sense but the keyword would be a syntax error, the token
can be used as an identifier.  This kind of thing is discouraged,
since it can result in SQL that is confusing to human readers, but it
does have the virtual of preserving backwards compatibility.

So, for example, even though WITH and RECURSIVE are now keywords, you
can still say:

    CREATE TABLE t1(with,recursive);
    SELECT with FROM t1 WHERE recursive=1;

I repeat: Even though you can do this, you should not.

I have observed that identifiers in SQLite databases on Macs and
iPhones always start with the letter Z.  There are no SQL keywords
that begin with Z, so I'm guessing the initial Z in Mac/iOS
identifiers is to avoid the possibility of any future keyword
collisions.

I also observe that many programmers familiar with SQL-Server put all
identifiers inside [...].  As far as I know, SQL-Server and SQLite are
the only database engines that support this syntax.  Putting all
identifiers inside [...] helps to prevent problems in the case that
new keywords get added in the future, just alike prepending Z to all
identifiers does on Mac/iOS.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Valid characters for indentifiers

R Smith
In reply to this post by Bart Smissaert


On 2017/11/11 7:23 PM, Bart Smissaert wrote:
> Yes, that this is mainly for SQLite and thanks for explaining and that is
> what I thought.
> I think square brackets are better than double quotes as it looks better
> and parsing SQL
> gets easier as the opening and closing character are not the same. But then
> for compatibility
> with other DB's double quotes are better.

I agree, the square brackets can be very easy on the eyes when trying to
decipher SQL from ASCII soup, and no doubt this was M$'s reasoning when
introducing it, but these days with syntax highlighting available near
everywhere, the point is a bit moot.

The beauty of double-quotes is that it is the SQL standard and is
acceptable by all 90% of DB engines (except of course notoriously where
[ and ] is a must) which make multi-platform SQL easier if you keep to
it - except, non-quoted identifiers IS actually accepted 100%
universally (unless there's an edge case I am unaware of).

I design everything to be non-Keyword, non-funny so that it NEVER needs
quoting. The possible permutations of using only Alphanumerics is more
than all the words you have ever spoken in your lifetime, if not more
than all the words that have ever been spoken in totality... There is
just no reason to have to go funny-char++ (unless perhaps you are
programming in Russian/Chinese).

That said, as Richard pointed out, it can be beneficial to use those
quotes in protecting SQL code from all future SQLite Keyword additions,
but even so, the chances of adding a keyword I've used as an identifier
is infinitesimally small, so I'll be a Maverick and keep going lite. :)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Valid characters for indentifiers

Clemens Ladisch
In reply to this post by Kees Nuyt
Kees Nuyt wrote:
> It conforms to the SQL standard, you can use the Postgresql docs
> as a reference.

Actually, neither SQLite nor PostgreSQL conform to the SQL standard.

The SQL standard requires that delimited identifiers are case sensitive
and can contain double quotes, and that undelimited identifiers are
folded to upper case.

SQLite's identifiers never are case sensitive.

PostgreSQL folds undelimited identifiers to lower case, and does not
allow double quotes inside a delimited identifier.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Valid characters for indentifiers

KlaasV
In reply to this post by Bart Smissaert
Bart Smissaert wrote on Sat, 11 Nov 2017 11:04:37>What are the exact rules for valid identifier names (tables, columns and indexes)?
Enclosed in double quotes practically anything, but using special characters like /,@,;,#,+,*,% are not really recommended, just as using key-words ("TABLE", "COLUMN" etc see SQLite Query Language: SQLite Keywords )


|
|
|  |
SQLite Query Language: SQLite Keywords


 |

 |

 |


More to it you may find on Stack Overflow


|
|
|
|  |  |

 |

 |
|
|  |
What SQLite column name can be/cannot be?

Is there any rule for the SQLite's column name? Can it have characters like '/'? Can it be UTF-8?
 |

 |

 |




Kind regards | Vriendelijke groeten | Cordiali saluti,
Klaas `Z4us` van Buiten V, Experienced Freelance ICT-Guy
https://www.linkedin.com/in/klaas-van-buiten-0325b2102
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Klaas "Z4us" V, MetaDBA at InnocentIsArt.EU
Reply | Threaded
Open this post in threaded view
|

Re: Valid characters for indentifiers

Kees Nuyt
In reply to this post by Clemens Ladisch
On Sun, 12 Nov 2017 10:19:03 +0100, Clemens Ladisch
<[hidden email]> wrote:

> Kees Nuyt wrote:
>> It conforms to the SQL standard, you can use the Postgresql docs
>> as a reference.
>
> Actually, neither SQLite nor PostgreSQL conform to the SQL standard.
> [...]

Thanks for the heads-up!

--
Regards,
Kees Nuyt
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Valid characters for indentifiers

Jens Alfke-2
In reply to this post by Simon Slavin-3


> On Nov 11, 2017, at 6:31 AM, Simon Slavin <[hidden email]> wrote:
>
> There is no documentation for this.  Which means that even if you find that, say, macrons are allowed in this version, they might not be allowed in the next version.

Um, really? That sounds bad for compatibility. Why isn’t it documented, or stable?

(My situation: my library generates SQL queries that sometimes use identifiers created by the library’s client, i.e. some 3rd party app. It’s very useful to be able to use these identifiers as-is by prefixing some sort of namespace (like “fts::”) and wrapping the token in quotes. (Of course I have to check that the identifier doesn’t itself contain an ASCII double-quote.))

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Valid characters for indentifiers

Simon Slavin-3


On 13 Nov 2017, at 7:33pm, Jens Alfke <[hidden email]> wrote:

> On Nov 11, 2017, at 6:31 AM, Simon Slavin <[hidden email]> wrote:

>
>> There is no documentation for this.  Which means that even if you find that, say, macrons are allowed in this version, they might not be allowed in the next version.
>
> Um, really? That sounds bad for compatibility. Why isn’t it documented, or stable?

One for the Dev team to answer.  If it was documented, it would be stable, at least within what the documentation admits to.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Valid characters for indentifiers

Clemens Ladisch
In reply to this post by Jens Alfke-2
Jens Alfke wrote:
> On Nov 11, 2017, at 6:31 AM, Simon Slavin <[hidden email]> wrote:
>> There is no documentation for this.  Which means that even if you find
>> that, say, macrons are allowed in this version, they might not be allowed
>> in the next version.
>
> Um, really? That sounds bad for compatibility. Why isn’t it documented,
> or stable?

The documentation claims ANSI SQL 1992 compatiblity; all identifiers
conforming to the standard are supported.

Extensions (e.g., dollar signs in the middle of identifiers; underscore
at the start; Unicode characters that are not letters, syllables, or
ideographs) are undocumented but will probably never be removed to avoid
breaking backwards compatibility.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Valid characters for indentifiers

Simon Slavin-3


On 14 Nov 2017, at 7:56am, Clemens Ladisch <[hidden email]> wrote:

> The documentation claims ANSI SQL 1992 compatiblity; all identifiers
> conforming to the standard are supported.

SQLite is case-insensitive for entity names.  SQL92 says that case matters.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Valid characters for indentifiers

Clemens Ladisch
Simon Slavin wrote:
> On 14 Nov 2017, at 7:56am, Clemens Ladisch <[hidden email]> wrote:
>> The documentation claims ANSI SQL 1992 compatiblity; all identifiers
>> conforming to the standard are supported.
>
> SQLite is case-insensitive for entity names.  SQL92 says that case matters.

What I meant to say: all characters valid in SQL92 identifiers are also
valid in SQLite identifiers.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users