#5495 closed enhancement (fixed)
[pg] Explicitly list column names in COPY syntax
Reported by: | strk | Owned by: | warmerdam |
---|---|---|---|
Priority: | normal | Milestone: | 2.0.0 |
Component: | OGR_SF | Version: | unspecified |
Severity: | normal | Keywords: | |
Cc: |
Description
In presence of DDL triggers by the time COPY is called the structure of the just-created TABLE could be changed. Explicitly listing the expected column names in the COPY construct would play nicer with such scenarios.
I'll work on a patch if I find the time
Change History (10)
comment:1 by , 10 years ago
Component: | default → OGR_SF |
---|
comment:2 by , 10 years ago
Resolution: | → invalid |
---|---|
Status: | new → closed |
comment:3 by , 10 years ago
Resolution: | invalid |
---|---|
Status: | closed → reopened |
Actually, I just noticed that the problem is that the list of fields in the table area really re-extracted after lots of ALTER calls. But by specifying a null value the COPY statement does not allow using the default for the fields for which it does not have a value.
comment:4 by , 10 years ago
A behavior that might probably work better would be to just omit the fields that did not come from the original source table.
comment:5 by , 10 years ago
Same issue with INSERT ! ogr2ogr is actually querying the database catalogue to find out which fields are present. The PostgreSQL logs show:
SELECT a.attname, a.attnum, t.typname, t.typname = ANY(ARRAY['int2','int4','serial']) AS isfid FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n, pg_index i WHERE a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid AND c.relnamespace = n.oid AND c.oid = i.indrelid AND i.indisprimary = 't' AND t.typname !~ '^geom' AND c.relname = 'importer_7547520ae73d11e39bc80090f58b1ecf' AND (i.indkey[0]=a.attnum OR i.indkey[1]=a.attnum OR i.indkey[2]=a.attnum OR i.indkey[3]=a.attnum OR i.indkey[4]=a.attnum OR i.indkey[5]=a.attnum OR i.indkey[6]=a.attnum OR i.indkey[7]=a.attnum OR i.indkey[8]=a.attnum OR i.indkey[9]=a.attnum) AND n.nspname='cdb_importer' ORDER BY a.attnum
Indeed both INSERT and COPY use the real explicit names, but the problem is with passing a null to fields that do not accept one.
I'll see how to skip the fields that have a default value, given ogr2ogr does not define a default for the fields added by itself, right ?
comment:6 by , 10 years ago
Actually, we don't even need to know about the existence of a default, it would be enough to NOT mention fields that were not available in the original table (the one we came from). Maybe poFeature->IsFieldSet ?
comment:7 by , 10 years ago
comment:9 by , 10 years ago
Milestone: | → 2.0 |
---|---|
Resolution: | → fixed |
Status: | reopened → closed |
Hum, that looks a bit too risky to backport.
It turns out the code is already providing the explicit column names, since 1.4.0: http://trac.osgeo.org/gdal/browser/tags/gdal_1_4_0/ogr/ogrsf_frmts/pg/ogrpgtablelayer.cpp#L1749
Closing as invalid