spatial database - Need a vector format that is editable in QGIS and supports >10 character column names

For reasons, I need to create a new column in a vector shapefile with a name longer than 10 characters, and export to mapinfo TAB format. I know .shp's don't support >10 character column names, so I'm looking for an intermediate format that's both editable in QGIS, allows addition of columns after features have been added, and supports >10 character UPPERCASE column names. All of the vector formats seem to support some combination of these, but not all three.

Anybody have this problem in the past, and/or know of a format that supports this use case?

Answer

Any SQL-compliant spatial database should work, including PostGIS (if you happen to be running a PostGIS server) or SpatiaLite (but note quirks about behavior mentioned below).

Either one will satisfy your criteria:

1. Identifiers (including column names) can be up to 63 characters in a default PostGIS installation (and this can be increased by changing the server's NAMEDATALEN constant). I can't find any limit to column name length in SQLite docs, and blog posts seem to confirm that there aren't any.



2. Fields are editable in QGIS.


3. New columns can be added at any time.


4. Uppercase column names (UGGH!) can be used, but the behavior is different and complex. See following.

In general, SQL developers will try to avoid using case-sensitive identifiers. However, case-sensitive identifiers can be created using quoting. Without quoting, some databases (Oracle) will fold identifiers to UPPERCASE, some (PostgreSQL/PostGIS) will fold identifiers to lowercase, and some (SQLite/SpatiaLite, SQL Server) are case-preserving but not case-sensitive.

In PostGIS you can force uppercase by using quoted identifiers:

CREATE TABLE foo (
  "COLUMN_NAME_1" int
);

Note that since I never do this kind of table creation in QGIS DB Manager, I don't know whether you can force uppercase column names that way, but you can do so using other management tools or writing the SQL yourself (which you can submit using DB Manager's SQL Editor) as demonstrated above.

This will require you to use quoting in any SQL that you write, as SELECT COLUMN_NAME_1... will be folded to SELECT column_name_1... internally, and column_name_1 does not equal COLUMN_NAME_1.

For SpatiaLite, the behavior is a little strange. SQLite will preserve the case of the column as created (whether or not you quote the identifier), and will also preserve it on export, but in any SQL that you write the column name will be treated in a case-insensitive manner. Any combination of upper and lower case letters will be accepted regardless of quoting, and SQLite will treat COLUMN_NAME_1 and column_name_1 as a conflicting names and will not let you create both columns in the same table. (PostGIS, OTH, will allow names that differ only in case.)

For either PostGIS or SpatiaLite, the case will be preserved if you load the layer in QGIS and export to TAB using the Save As dialog.

More info on identifier case-sensitivity is available in this extremely informative blog post: http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html

NOTE: Original answer only discussed PostGIS. Based on answer by @user30184, I have significantly changed answer to include information about SpatiaLite.