Skip to main content
  • EN

Hi,

Is it possible to have FME modify the schema of an existing table? Say I have a table with name mytable and attributes attr1, attr2 and attr3, coming from the import of an external dataset. A new version of the dataset contains a new attribute, or the data type of an attribute changes from char to int or so. We do not want to drop and recreate the table, since quite a load of triggers, user rights and stuff is dependent on the table. Basically we want to truncate the table to the new schema. Is that doable in FME, has anyone experimented with that?  I guess I could use something like 

SELECT 
  *
FROM 
  information_schema.columns
WHERE 
  table_name = 'mytable' 

(we use Postgis), check the results and compare them to the new version of the dataset, and if differences, generate and execute the necessairy ddl statements by FME, but I'm not sure if we should go that way.

For what it's worth, what you're describing is pretty much what I'd do.

@Helmoet de Zwijger​ FME can't modify the table schema, so you have to construct the DDL statements and run those using SQlexecutor, as you suggest.

But... in FME 2022 we have added a new transformer - SchemaScanner that might make the comparison of the new and old schemas a little easier. Available in FME 2022 betas if you want to kick the tires. Should replace the FME HUB SchemaSetter

Thanks for the answers!

Reply


Community Stats

30,176
Posts
114,544
Replies
37,836
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.