PostGIS layer default value handling is incorrect

[smizuno]

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.

An example:
column definition: ... textcol CHARACTER VARYING DEFAULT 'This is a test' ...
default value is retrieved as: 'This is a test'::character varying
which is used in the QGIS attribute entry field.
The intended value is: This is a test

Because the expression is now the text in the entry field, the result in the database table is: 'This is a test'::character varying

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.

[jef]

Replying to smizuno:

Because the expression is now the text in the entry field, the result in the database table is: 'This is a test'::character varying

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.

[jef]

fixed in r9382

[(none)]

Milestone Version 1.0.0 deleted