Bug report #1327

PostGIS layer default value handling is incorrect

Added by Steven Mizuno about 12 years ago. Updated about 11 years ago.

Status:Closed
Priority:Low
Assignee:nobody -
Category:Data Provider
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

Description

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:

<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. 

History

#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.

#2 Updated by Jürgen Fischer about 12 years ago

  • Resolution set to fixed
  • Status changed from Open to Closed

fixed in

#3 Updated by Anonymous about 11 years ago

Milestone Version 1.0.0 deleted

Also available in: Atom PDF