[QGIS-trac] [Quantum GIS] #1327: PostGIS layer default value handling is incorrect

[Quantum GIS]

#1327: PostGIS layer default value handling is incorrect
---------------------------------------------------------+------------------
   Reporter:  smizuno                                    |              Owner:  nobody       
       Type:  bug                                        |             Status:  new          
   Priority:  critical: causes crash or data corruption  |          Milestone:  Version 1.0.0
  Component:  Data Provider                              |            Version:  HEAD         
   Keywords:                                             |   Platform_version:               
   Platform:  All                                        |           Must_fix:  Yes          
Status_info:  0                                          |  
---------------------------------------------------------+------------------
 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: [[BR]] column definition: {{{... textcol CHARACTER VARYING
 DEFAULT 'This is a test' ...}}}[[BR]]default value is retrieved as:
 {{{'This is a test'::character varying}}} [[BR]] which is used in the QGIS
 attribute entry field.[[BR]] 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.

-- 
Ticket URL: <https://trac.osgeo.org/qgis/ticket/1327>
Quantum GIS <http://qgis.org>
Quantum GIS is an Open Source GIS viewer/editor supporting OGR, PostGIS, and GRASS formats