[Feature Request] Case sensitive columns

(1) By pipeng on 2022-09-28 15:59:20 [link] [source]

Hi all, I'm here because I think would be great support case sensitive in column names, this like a follow from:

https://github.com/opengeospatial/geopackage/issues/637

SQLite is very common now, and great for portability, I'll suppose you didn't read the post above.

A way that is used this is for move data, like in geopackage, this one is very powerful now, we can move maps from one db to other without a lot of limitations of shp files.

The feature requested here, is a way to use SQLite with case sensitive columns, why? for compatibility, actually there is other dbs that handle maps, but we can't move the data from the db to SQLite due to this:

This dbs are case sensitive:

Postgres (great for spatial data)
MySQL in Linux (windows is not case sensitive as I read)
OpenStreetMap database (OSM)

In cases like geopackage, have case sensitivity can be great, it really helps to have full compatibility over several dbs! without the need of change the original data to can store it helps to keeps traceability of it.

Maybe would be great have a flag in the DB to able or disable the case sensitive columns.

Well, is not like all is fixed just with case sensitive support, but at least, we would be able to export a lot of data in a easier way from the dbs above to SQLite.

(2) By Chris Locke (chrisjlocke1) on 2022-09-28 16:40:59 in reply to 1 [link] [source]

So Postgres has table a and table A, which contain different data?

Table B has field a and field A which contain different data?

(3.1) By David Raymond (dvdraymond) on 2022-09-28 17:15:35 edited from 3.0 in reply to 2 [source]

For Postgres, you can have this if you really want it, yes.

But take note that any non-quoted identifier in a statement gets folded to lower case before it gets looked up.

So if you had tables "t" and "T", both of which had columns "a" and "A", then the statement

select A from T;

would actually be equivalent to

select "a" from "t";

because you didn't quote the identifiers, even though there's a "T" table and an "A" column in "T".

You'd have to write

select "A" from "T";

to get the capital A field in the capital T table.

Similarly, "drop table T;" would drop the lowercase "t" table.

And if you only had a "T" table, then "select * from T;" would give you this error:
=> select * from T;
ERROR:  relation "t" does not exist
LINE 1: select * from T;
                      ^

(4) By Holger J (holgerj) on 2022-09-29 13:09:58 in reply to 1 [link] [source]

Yes, case sensitivity is required by the SQL standard if identifiers (for any kind of object) are quoted in double quotes.

Actually, I had expected SQLite to respect this, but it doesn't.

OTOH, quoted names (which also allow spaces and non-ASCII characters including emojis) are totally unloved and hardly ever used.

MS SQL is the only other DBMS which doesn't respect the standard and identifiers remain case insensitive even when quoted. (And MS SQL allows – and encourages – identifiers to be quoted with brackets instead of quotes, which is another nuisance.)