QGIS GeoPackage – Meaning of Maximum Field Length of 0

fields-attributesgeopackageqgis

I have looked around for the answer, but have not found anything reliable.
When creating a new layer or new fields in a GeoPackage one sometimes has to enter the parameter Maximum Length, sometimes not. I often leave this field empty or set it to 0, which apparently works, since I can enter data longer than 0. So far I have not found any downside of setting the field length to 0.

An example of the field overview of one of my layers would look like this:
enter image description here

Is it true, that a length of 0 is equal to the maximum length, that the field is automatically set to the default length as defined in the data definition, or is there a difference between setting 0 and setting the real maximum length?

Best Answer

If you create a GeoPackage table by setting a max length for an attribute then the corresponding SQL CREATE statement will look like this:

CREATE TABLE "temp" ( "fid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "geometry" POINT, "foo" TEXT(3))

For SQLite/GeoPackage database number 3 in TEXT(3) does not have any meaning. It is still possible to store longer strings into that field. The real maximum length is defined at compile time with "SQLITE_MAX_LENGTH" and by default it allows 1 billion (1 thousand million or 1,000,000,000) single byte characters https://www.sqlite.org/limits.html.

However, QGIS does read and interpret the string length in the CREATE statement and it is impossible to store any longer strings with QGIS tools. If user edits data for example directly with SQL to contain longer strings like foo='bbbbb' QGIS shows the data as it is stored into GeoPackage. However, when I tried to edit that attribute into bbbb with QGIS it actually changed the value into NULL obviously because bbbb in longer than 3 characters. I think this is a bug in QGIS. The GeoPackage standard defines

The optional maxchar_count defines the maximum number of characters in the string. If not specified, the length is unbounded. The count is provided for informational purposes, and applications MAY choose to truncate longer strings if encountered. When present, it is best practice for applications to adhere to the character count.

So it would have been OK to truncate bbbb into bbb but not to set it into NULL.

Strings without max length do not take any more space in SQLite/GeoPackage than strings with max length. If there is no real reason to limit the attribute length I would say that it is safer to not set any limits. Some reasons to limit the length include a need to export data into shapefiles (max string length 254 characters) or if the attribute will be used in a form as a postal address or something like that.

Related Question