Get default values from PostGIS DB in QGIS 2.4 editor

Question

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

• Get Spatial: It would help to see screenshots from QGIS, as that is where the differences seem to be occurring.
• Stev_k: I have added the metadata from the table definitions. I notice that the layer sources are different, but that is about all..
• Get Spatial: 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.
• Stev_k: 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: 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

Answer 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