GeoServer – Using Custom Primary Key Values When Inserting Features via WFS

geoserverinsertwfs-t

When I use WFS to insert features, I want the primary key value to take the attribute value I set, but GeoServer automatically generates the primary key value and ignores the primary key value I set.
Here is my geometry table:

create table test(a int primary key, b int, c geometry(Point, 4326));

I've tried the following:
I am following directions GeoServer docs to create the primary key metadata table,

but, it doesn't work.
Here is my primary key metadata table:

CREATE TABLE public.gt_pk_metadata (
  table_schema VARCHAR(32) NOT NULL,
  table_name VARCHAR(32) NOT NULL,
  pk_column VARCHAR(32) NOT NULL,
  pk_column_idx INTEGER,
  pk_policy VARCHAR(32),
  pk_sequence VARCHAR(64),
  unique (table_schema, table_name, pk_column),
  check (pk_policy in ('sequence', 'assigned', 'autoincrement'))
);

INSERT INTO gt_pk_metadata (
  table_schema,
  table_name,
  pk_column,
  pk_policy
) VALUES (
  'public',
  'test',
  'a',
  'assigned'
);

The WFS request as follows:

<Transaction xmlns="http://www.opengis.net/wfs" service="WFS" version="1.1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.opengis.net/wfs http://schemas.opengis.net/wfs/1.1.0/wfs.xsd">
  <Insert>
    <test xmlns="http://geoserver/postgres">
      <c><Point xmlns="http://www.opengis.net/gml" srsName="EPSG:4326"><pos srsDimension="2">109.47051452397488 32.96654482702854</pos></Point>
      </c>
      <a>123</a>
      <b>321</b>
    </test>
  </Insert>
</Transaction>

However, the primary key field still does not take the value I set.

On the GeoServer configuration in store, "Expose primary keys" has been enabled, "Primary key metadata table" is left blank.
I tried to set "Primary key metadata table" to public.gt_pk_metadata, but still no help.
I also tried the following methods in the similar question: Getting GeoServer primary key metadata table working

I'm confused about how to set the primary key value I want.

Best Answer

I think you are out of luck. I have tested your setup and have managed to get pk_policy =sequence to work as expected. All other values for pk_policy yields in autoincrement. I draw the conclusion that my metadata table is correctly set up, but either GeoServer has a bug, or the documentation is incorrect.

Also I found this: https://sourceforge.net/p/geoserver/mailman/message/37386688/ basically what it says it that someone asks the same as you and gets a response from one of the main developers of GeoServer that it is probably not possible. But I do not agree with the argumentation that it would be because the column is not added to the insert statement, as the key columns are added later in the same function.

In this question: https://stackoverflow.com/questions/35713831/geoserver-configuring-a-primary-key-metadata-table-for-pk-assignment-using-a-po A workaround is proposed, which I have tested and it works for geoserver 2.20.2 using the following statement:

<Transaction xmlns="http://www.opengis.net/wfs" service="WFS" version="1.1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.opengis.net/wfs http://schemas.opengis.net/wfs/1.1.0/wfs.xsd">
    <Insert idgen="UseExisting">
        <sf:test xmlns:sf="http://localhost:8008/sf" fid="999">
            <sf:c>
                <Point xmlns="http://www.opengis.net/gml" srsName="EPSG:3857">
                    <pos srsDimension="2">2312896.5286310725 8111449.527554644</pos>
                </Point>
            </sf:c>
            <sf:a>909</sf:a>
            <sf:b>120</sf:b>
          </sf:test>
    </Insert>
</Transaction>

It works for at least wfs 1.1.0, but requires that you can set the idgen attribute <Insert idgen="UseExisting">and the fid attribute <sf:test xmlns:sf="http://localhost:8008/sf" fid="999">. In the example above the feature will get a=999 in the database. The statement that sets a=909 is ignored in favour of the fid statement.

Related Question