Bug report #13755

Copy/paste PostGIS layer: date not autofill ( ('now'::text)::date)

Added by Stephan Wagner about 4 years ago. Updated over 3 years ago.

Status:Closed
Priority:Severe/Regression
Assignee:Jürgen Fischer
Category:Data Provider/PostGIS
Affected QGIS version:2.10.1 Regression?:No
Operating System:Linux Easy fix?:No
Pull Request or Patch supplied:No Resolution:duplicate
Crashes QGIS or corrupts data:No Copied to github as #:21782

Description

Postgres/PostGIS versions involved:
PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.8.3-2) 4.8.3, 64-bit
POSTGIS="2.1.3 r12547" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012"
GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="UNKNOWN" TOPOLOGY RASTER

Simplified example table:
gid integer not null Vorgabewert nextval('lz_alp_gid_seq'::regclass)
id double precision
datum date not null Vorgabewert ('now'::text)::date
geom geometry(MultiPolygon)

When I select and copy a polygon from one layer to paste it in an other, the 'date' field is not auto-filled, but replaced with NULL value. The filed counter in 'gid' works fine. This is just an example. I cam observe an equivalent behavior on other tables with functions as filed values. Some work, some not.

The solution worked with QGIS 2.6 and older. I may provide DB-access, if needed.


Related issues

Related to QGIS Application - Bug report #9489: Copy/Paste issue with explicit primary keys Closed 2014-02-04
Related to QGIS Application - Bug report #13857: Default values on a PG table: type cast appended Closed 2015-11-19

History

#1 Updated by Giovanni Manghi about 4 years ago

  • Target version set to Future Release - High Priority
  • Priority changed from Normal to Severe/Regression

as it worked in previous qgis releases tagging this as a blocker.

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

  • Subject changed from Copy/past PostGIS layer: date not autofill ( ('now'::text)::date) to Copy/paste PostGIS layer: date not autofill ( ('now'::text)::date)

#3 Updated by Jürgen Fischer about 4 years ago

  • Status changed from Open to Feedback

Do the tables you copy features from and to have the same structure?

Are you copying new features before they are committed? Otherwise the defaults of the source layer should not matter.

If it's about the defaults of the default layer those are only considered for primary key columns (see also #9489)

#4 Updated by Giovanni Manghi almost 4 years ago

Jürgen Fischer wrote:

If it's about the defaults of the default layer those are only considered for primary key columns (see also #9489)

Hi Jürgen,
did I understand correctly, any default defined in a postgresql table is ignored in QGIS unless the column is a primary key, right?

about this issue:
the above wold explain the described behavior, but then this mean that is something that changed (on purpose) compared to older QGIS releases?

#5 Updated by Jürgen Fischer almost 4 years ago

Giovanni Manghi wrote:

If it's about the defaults of the default layer those are only considered for primary key columns (see also #9489)

did I understand correctly, any default defined in a postgresql table is ignored in QGIS unless the column is a primary key, right?

No, the opposite. The values are copied as is, except for primary key columns. Those are replaced with their default value (expression from the database), unless there is none - in which case the given value from the source feature is used. The fields of the original layer are mapped by name to the paste layer's, unavailable fields are skipped and not given fields are not handled explicitly (ie. not replaced by the default value). Hence the question if the original feature has a date column and which value it has.

about this issue:
the above would explain the described behavior, but then this mean that is something that changed (on purpose) compared to older QGIS releases?

The primary key handling was changed at some point - to avoid key clashes when copying features. It might be that the handling of default value implicitly changed, when we switched from using maps of values to array of values to store feature attributes. Before it was clear that a value was not given (because it was missing from the map) and now there always is a value in the array (ie. NULL when none was given).

A fix might be to initialize the target feature with all default values and then fill in the pasted features attribute values (still skipping the primary key columns). Although that would still produce a NULL value in the target table, if there is a NULL value in the source table.

#6 Updated by Giovanni Manghi almost 4 years ago

Jürgen Fischer wrote:

Giovanni Manghi wrote:

If it's about the defaults of the default layer those are only considered for primary key columns (see also #9489)

did I understand correctly, any default defined in a postgresql table is ignored in QGIS unless the column is a primary key, right?

No, the opposite.

then there is something wrong, at least on 2.8.4:

I just tested and if I have a default in a column (say now() for example) then if I add a new record with pgadmin the default is taken into account and the cell is filled, if I do it with QGIS the default is ignored and the cell is left with a NULL. Bug?

#7 Updated by Jürgen Fischer almost 4 years ago

Giovanni Manghi wrote:

then there is something wrong, at least on 2.8.4:

I just tested and if I have a default in a column (say now() for example) then if I add a new record with pgadmin the default is taken into account and the cell is filled, if I do it with QGIS the default is ignored and the cell is left with a NULL. Bug?

Doing what in QGIS exactly?

#8 Updated by Giovanni Manghi almost 4 years ago

Jürgen Fischer wrote:

Doing what in QGIS exactly?

see #13857-11

#9 Updated by Giovanni Manghi almost 4 years ago

('now'::text)::date

do not work on 2.8.*, not sure on 2.10, and not sure if it has any importance for this report.

#10 Updated by Giovanni Manghi almost 4 years ago

was this fixed by the same fix for #13857 ?

#11 Updated by Giovanni Manghi over 3 years ago

is this issue fixed?

#12 Updated by Giovanni Manghi over 3 years ago

one more try, was this fixed by the same fix of #13857 ?

#13 Updated by Jürgen Fischer over 3 years ago

Giovanni Manghi wrote:

one more try, was this fixed by the same fix of #13857 ?

Maybe this is a duplicate of #8800. All fields were set to NULL and not only pasted fields and caused the default values to be ignored even if the fields didn't exist in the source layer. Now the feature is initialized with the default values and only the pasted attributes are overwritten.

#14 Updated by Giovanni Manghi over 3 years ago

  • Status changed from Feedback to Closed
  • Resolution set to duplicate

please reopen of necessary.

Also available in: Atom PDF