PostGIS – Defining Column as Serial/PrimaryKey with Pre-Existing ID Values in FME

fmepostgresqlserial

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.

enter image description here

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?

Best Answer

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.

Related Question