3

One of my postgis tables tables automatically includes the nextval(xxx) in the edit feature form in qgis(and I think will also update ids of multiple features automatically). The other seems to have the same definition in postgis and qgis, but does not. Is there a key setting that I am missing? This is the definition of both my tables in postgis:

Property    Value
Name    id
Position    1
Data type   integer
Collation   
Default nextval('seq1'::regclass)
Sequence    seq1
Not NULL?   Yes
Primary key?    Yes
Foreign key?    No
Storage PLAIN
Inherited   No
Statistics  -1
System column?  No
ACL 
Comment 

-- Column: id

 ALTER TABLE x ADD COLUMN id integer;
 ALTER TABLE x ALTER COLUMN id SET NOT NULL;
 ALTER TABLE x ALTER COLUMN id SET DEFAULT nextval('seq1'::regclass);

And the other

 Property   Value
 Name   id
 Position   1
 Data type  integer
 Collation  
 Default    nextval('seq2'::regclass)
 Sequence   seq2
 Not NULL?  Yes
 Primary key?   Yes
 Foreign key?   No
 Storage    PLAIN
 Inherited  No
 Statistics -1
 System column? No
 ACL    
 Comment    

-- Column: id

ALTER TABLE y ADD COLUMN id integer;
ALTER TABLE y ALTER COLUMN id SET NOT NULL;
ALTER TABLE y ALTER COLUMN id SET DEFAULT  nextval('seq2'::regclass);

And QGIS metadata definitions:

Doesn't work Metadata

General Storage type of this layer PostgreSQL database with PostGIS extension Description of this provider PostgreSQL/PostGIS provider PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit PostGIS 2.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 Source for this layer dbname='x' host=y port=5432 user='z' sslmode=disable key='id' table="xx" (geom) sql= Geometry type of the features in this layer Polygon Primary key attributes id The number of features in this layer 249 Editing capabilities of this layer Add Features, Delete Features, Change Attribute Values, Add Attributes, Delete Attributes, Fast Access to Features at ID, Change Geometries, Simplify Geometries, Simplify Geometries with topological validation

Does work metadata

General Storage type of this layer PostgreSQL database with PostGIS extension Description of this provider PostgreSQL/PostGIS provider PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit PostGIS 2.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 Source for this layer dbname='a' host=b port=5432 sslmode=disable key='id' srid=4326 type=MULTIPOLYGON table="c" (geom) sql= Geometry type of the features in this layer Polygon Primary key attributes id The number of features in this layer 128 Editing capabilities of this layer Add Features, Delete Features, Change Attribute Values, Add Attributes, Delete Attributes, Fast Access to Features at ID, Change Geometries, Simplify Geometries, Simplify Geometries with topological validation

Below is the editor view when I add a feature to the working layer - I get nothing in either box when adding to the other layer.

Editor image

  • It would help to see screenshots from QGIS, as that is where the differences seem to be occurring. – Get Spatial Aug 11 '14 at 16:48
  • I have added the metadata from the table definitions. I notice that the layer sources are different, but that is about all.. – Stev_k Aug 11 '14 at 17:01
  • 1
    Are these tables in the same database? If they are, then there is something funky with the source, as that should be the same. Did you connect using the same connection string? The user, srid and geometry types are different. This leads me to believe it may be a permissions problem. It might help to show a screenshot of the editor window for each one to see what is different there. – Get Spatial Aug 11 '14 at 17:33
  • They are in different databases - not sure why the connection strings are different - can't seem to change that. Have added my screenshot. Permissions are the same - the superuser owns both table and sequence. – Stev_k Aug 11 '14 at 17:54
  • 1
    I might file a bug on this - the only thing I have got so far is that if I open the postgres layer via the Add Vector Layer -> Database ->Postgres option I get no ID form, whereas if I do Add PostGIS layer I get an empty ID form with the same database connection. This is a little inconsistent to say the least. Thanks for your help Get Spatial – Stev_k Aug 11 '14 at 18:31
1

Doesn't work Metadata sslmode=disable key='id' .......................... table="xx" (geom)

Does work metadata sslmode=disable key='id' srid=4326 type=MULTIPOLYGON table="c" (geom)

The only difference between the two that matters is that the nonworking table has no srid and no polygon type. I could not get PostGIS raster data to work in qgis until I had enough of the raster definition complete. I am guessing that you are seeing is a similar problem with vector data because of the two pieces of missing information.


I noticed that your Postgresql server is on Red Hat Linux. If you have access to the server via putty, ssh or a direct GUI logon, then there are some tools to pick this needle out of the hay stack of metadata.

  1. Create two files. In each file put the information from each of the metadata information from the tables that you want to compare.
  2. diff workmdatano.txt workmdata.txt is useful to find quick differences but may be a challenge to read at first.
  3. Use vim -d workmdatano.txt workmdata.txt, if you have shell access only. Use gvim -d workmdatano.txt workmdata.txt, if you have shell with some sort of gui access.
  4. The answer is not just for *nix and Mac. Install gvim for MS Windows.
  5. Click on the two files in MS File Explorer that you want to compare. The limit appears to be four files.
  6. Right click on two files and select "Diff with Vim".

The image below is from MS Windows. I have modified the files to play up the differences. vim difference operation on MS Windows

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.