Bug report #1327
PostGIS layer default value handling is incorrect
|Affected QGIS version:||Regression?:||No|
|Operating System:||All||Easy fix?:||No|
|Pull Request or Patch supplied:||Resolution:||fixed|
|Crashes QGIS or corrupts data:||Copied to github as #:||11387|
Two problems with default values in PostGIS layers:
1. The column default value retrieved from information_schema.columns is actually the expression that would be placed in an SQL command, not the value itself. The difference is subtle for number types that are just a number, but is a problem for any text type columns or columns with a sequence attached.
<pre> which is used in the QGIS attribute entry field. <pre> <pre> Expected behavior: perhaps use the keyword DEFAULT in the SQL command sent to the database. But that leaves the problem of determining when DEFAULT should be used. I'm not in favor of using the word DEFAULT in the entry field because it is confusing to most users. Another, better way, would be to send a SELECT query using the expression for the default value to the database, then using the result in the attribute entry field. This is necessary for text types and sequences, and probably others. It may be best to ignore the whole notion of default value, except for the column used as the object id if it is defined as SERIAL. (Note that SERIAL is just shorthand for INTEGER type with a SEQUENCE attached) This leads to the second problem. 2. The default value for for the column used as the object id, when defined as SERIAL does not use the sequence. Actually, this isn't done for any column defined as SERIAL, even though the attribute entry fields display the "nextval(...)" expression. This can cause user frustration and loss of data when other applications are adding data as the sequence will generate possibly duplicate numbers, violating any UNIQUE constraint on the column. Expected behavior: use the sequence function nextval() for the object id column, at least, if a sequence is defined. Otherwise find the max value of the column and add 1 as is done now.
#1 Updated by Jürgen Fischer about 12 years ago
Replying to smizuno:
Because the expression is now the text in the entry field, the result in the
Not reproducable here. You'll see that expression for the value only while the added feature is not saved yet. After commit you'll find 'This is a test' in the database. > Another, better way, would be to send a SELECT query using the expression for > the default value to the database, then using the result in the attribute > entry field. This is necessary for text types and sequences, and probably > others. This is what happens, when the expression is left untouched for some but not all features and therefore the column needs to be a parameter in the insert statement. > 2. The default value for for the column used as the object id, when defined > as SERIAL does not use the sequence. Actually, this isn't done for any column > defined as SERIAL, even though the attribute entry fields display the > "nextval(...)" expression. Right.