Skip to main content

Hello,

Is there any way to assign Index “Primary key” to the field and Type “serial” somehow in another way rather than in Feature writer - User attributes? These options are not present in AttributeManager transformer, unfortunately.

 

That would help me to reduce number of manual adjustments and use “Automatic” attribute definition. Because I have many FeatureWriter transformers in my workspace and I need to replicate it to read another data. 

 

 

The short answer is, this isn’t something that can be set in AttributeManager.

SERIAL is not an actual data type.  It is a pseudo-type specific to PostGreSQL, where the real data type PostGreSQL uses is actually an INTEGER field.   What actually happens is that a CREATE TABLE or ALTER TABLE that defines a field as “SERIAL” will trigger behind-the-scenes instead an Integer Sequence counting upwards and constrain what is really an INTEGER field to self-populate with the Sequence generated values.   So in pure SQL terms “SERIAL” is not a data type but rather a form of  a Table Constraint.

Similarly PRIMARY KEY is not a data type, but a constraint that stops any input processes from writing  non-Unique or Null values into the field, and will generally also create a table index for that field.   It is also not a data type.

FME does not know the Constraints to put on the Table(s) until it reaches the Writer(s) and what SQL options will specifically exist until the Write Format is specificied.  The backend SQL for a SQL Server Writer in creating constraints is very different from the backend SQL for a PostGreSQL Writer setting the same Table Constraints, and there is no “SERIAL” pseudo-type in SQL Server where instead the SQL commands to call are the equivalent Create Sequence, Create Table Integer-constrained-to-sequence-values commands.

The way this can be more flexibly done is to write the CREATE SEQUENCE, CREATE/ALTER TABLE SQL commands etc. instead into the “SQL to run After” or “SQL to run Before” Writer Parameters which allow the FME user to do this more dynamically.  Setting “Serial” or “Primary Key” in the sample Manual Attribute mapping Dialog above actually causes FME to create and run its own “SQL to run Before” commands that are mostly invisible to the user, but instead can be alternatively run by writing the statements directly into the “SQL to Run Before/After” Parameters.

Can further refer to the PostGreSQL documentation here:
https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL

 

 


Reply