3

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?

  • 2
    not an answer since I am not familiar with FME and specific possible workarounds, but: PostgreSQLs SERIAL 'data type' cannot simply continue existing ids and will always restart with 1 when initialized. on DB level you could then ALTER SEQUENCE <seq_name> RESTART WITH <min_val>, given that your ids are in order and gap-less. if that is not suitable...I fear that having another dedicated primary key column will be among the few options here. – ThingumaBob Oct 22 '18 at 14:25
  • oh, I wrote 'dedicated primary key column', but that should have been 'dedicated sequence column'. btw., you could very well create a composite primary key on (serial, id) or (id, serial) columns...with different effects on index performance. does your id represent row count? – ThingumaBob Oct 22 '18 at 15:39
  • or SELECT nextval('foo."id"') for the next id in an update statement – Mapperz Oct 23 '18 at 4:30
2

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.

  • You might also be able to use: ALTER TABLE "public"."MarksTest" ALTER COLUMN "parkid" TYPE integer, ALTER COLUMN "parkid" SET NOT NULL, ALTER COLUMN "parkid" SET DEFAULT nextval('marks_test_sequence') ...though I haven't tried that myself. – Mark Ireland Oct 22 '18 at 15:53
0

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

enter image description here

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:

enter image description here

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

enter image description here

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');

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.