FME PostGIS writer define column as serial/PrimaryKey with pre-existing id values

Question

With FME I read a table that has an id column with pre-existing values, like

id       | some_attributes...
--------------------------------
56784645 | ...
56478687 | ...
...

I would like to write these into a PostgreSQL/PostGIS database and for further data input I define the id column as serial / PrimaryKey. Because of joins to other tables, the id values need to be preserved.

In the resulting table my id values are replaced by the serials:

id       | some_attributes...
--------------------------------
1        | ...
2        | ...
...

QUESTION: How can I set up FME PostGIS writer in a way that defines a column as serial, but preserves my pre-existing values for this column?

Answer 1

As mentioned by @ThingumaBob I don't think a SERIAL will work like that.

In FME I would write the data and then add some SQL to alter the data type to a serial.

So either...

1. Use the SQL To Run After Write parameter in the writer itself
2. Write with a FeatureWriter transformer and follow up with a SQLExecutor transformer

As to the SQL, the one thing I know is that you can't just use:

ALTER COLUMN "column-name" TYPE serial

...because Postgres returns that as serial type unknown. You would need to create the sequence, alter the column to use that sequence for its next value, and tag the sequence as owned by that column.

It would be something like...

DROP SEQUENCE marks_test_sequence;
CREATE SEQUENCE marks_test_sequence;
ALTER TABLE "public"."MarksTest"
ALTER COLUMN "parkid" TYPE integer;
ALTER TABLE "public"."MarksTest"
ALTER COLUMN "parkid" SET NOT NULL;
ALTER TABLE "public"."MarksTest"
ALTER COLUMN "parkid" SET DEFAULT nextval('marks_test_sequence');
ALTER SEQUENCE marks_test_sequence OWNED BY "public"."MarksTest"."parkid"

That ran successfully for me in a SQLExecutor. I say "something like" because obviously your scenario may be slightly different. Still, the point is that you would need to create an integer column, write the data, and then use either of those FME options to alter the column type.

Answer 2

With @Mark Ireland 's hints I succeeded in creating a suitable workspace:

Explanation / Steps:

(1) Determine maximum id value using StatisticsCalculator

(2) Since this _max is important to create the sequence in the last step, we need to add this to Additional Summary Attributes of the FeatureWriter in order to be accessed by the SQLExecutor:

(3) The SQLExecutor creates a sequence with MINVALUE set to _max + 1 and sets the default value for the id column to nextval(...seq):

SQL in plain text:

DROP SEQUENCE IF EXISTS test_id_maxvalue_seq;
CREATE SEQUENCE IF NOT EXISTS test_id_maxvalue_seq MINVALUE @add(@Value(_feature_type{0}._max), 1);
ALTER TABLE grunddaten.test ALTER COLUMN id SET DEFAULT nextval('test_id_maxvalue_seq');