FME restart identity sequence when using Truncate Existing in the writer table handling drop-down menu

Question

In FME 2023+, when using Truncate Existing in the Table Handling drop-down list in a PostgreSQL/PostGIS writer, is it possible to reset the numbering sequence associated to the primary key when this is an IDENTITY field? E.g. a checkbox that I missed?

I'm asking because after a large amount of trials, some labels in QGIS with IDs in their definition become incredibly large because the sequence is not reset to 0.

enter image description here

Currently, I don't see any option for that and as a workaround I have to manually execute such query in the "SQL To Run Before Write" option of the writer. It's totally fine, but imho one can do better in terms of maintainability and to lower the number of user inputs.

I cannot drop the actual database table.
I have to let PostgreSQL manage the id field - which is not a serial but an identity.

Answer 1

This isn't quite the answer you're looking for, however, as I see it there are two options.

1. On the writer Feature Type there is an option in the Advanced setting to Allow Serial Column Writing.

If you use this then you can set the value in the table to match the id value you have in FME. You could use a counter to create a sequential id starting from 1.

Note, this does not reset the sequence as far as I can tell and it also doesn't seem make the sequence count up so it's probably not the best option because there's a good chance that something will break.

2. Then next option, which might be better, is to use the Drop and Create option rather than truncate. If you set the Attribute Definition to Manual and create a serial column in FME (Type = serial) and set the index to PrimaryKey then FME will just rebuild the table. The sequence will also be removed and recreated (at least it was in my testing).

If, however, you have a fairly complicated table definition then you might have to stick with you're current approach.

I do like the idea though to be able to reset a sequence with a check box. I guess if you were to do it then you would probably need to be somewhere in the Attribute Definition table.

• nielsgerrits: Be aware you can pitch an idea on Safe Software community: community.safe.com/ideas

Answer 2

It would be not so pretty but you could put a SQLExectutor with

TRUNCATE TABLE table_name RESTART IDENTITY;

Before your writer, the table will then be truncated twice but this would fix the sequence issue without having to drop the table.