Question

How to update PostgreSQL existing schema with any new data with additional columns ?

P
Badge

Hi,

I am new for using FME to create PostgreSQL database and having trouble updating schema or adding columns to the existing table. so to explain in detail:

1) I have different councils 3 waters pipe data which I want to add to my database

2) I have already done extracting, and transforming of data but now I want to load data coming from different councils into a single PostgreSQL database

3) So I am using FME writer format "PostgreSQL" and table (say Table1) definition as "Automatic" and ok till I have added the data from one council having 52 columns

4) Now when I am trying to add data into same postgresql database table (Table1) from other council having 79 columns in it ... my database is not accepting data from those new columns came from new council 79-52= 27 columns into my existing database table

Any comment or recommendation on this will be great!

7 replies

M
Badge +2

@ppp19​ FME writers cannot perform an ALTER TABLE. To do this you can use the SQLExecutor with the appropriate ALTER TABLE statement

The SchemaScanner transformer might help you determine the new columns and help you formulate the SQL. More on SchemaScanner here and here

P
Badge

@ppp19​ FME writers cannot perform an ALTER TABLE. To do this you can use the SQLExecutor with the appropriate ALTER TABLE statement

The SchemaScanner transformer might help you determine the new columns and help you formulate the SQL. More on SchemaScanner here and here

Hi @Mark Stoakes​,

Could you please help me build the SQL statement to alter the table using SQLExecutor?

My Database has say 5 columns: NetworkType, Council_ID, Population, Unique_ID and Comm_Date and new coming table has 10 columns: NetworkType, Counil_ID, Population, Unique_ID, Comm_Date, Column 1, Column 2, Column 3, Column 4 and Column 5 (Where Column 1 to 5 are new columns to add in above generated database)

I have already used SchemaScanner to get details of all new coming columns from this new table.

geomancer
Rank
Userlevel 4
Badge +36

@ppp19​ FME writers cannot perform an ALTER TABLE. To do this you can use the SQLExecutor with the appropriate ALTER TABLE statement

The SchemaScanner transformer might help you determine the new columns and help you formulate the SQL. More on SchemaScanner here and here

Read and learn: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-add-column/

Also be VERY careful with this kind of database operations!

P
Badge

Read and learn: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-add-column/

Also be VERY careful with this kind of database operations!

Hi @geomancer​,

this is more adding new column and data type using postgreSql …however, is there any direct transformation function available in FME…that can add such columns reading from coming new data files? …I am asking this…because I have data coming from 32 files …out of which I want to creat database ….and add just new columns data into already existing database.

geomancer
Rank
Userlevel 4
Badge +36

Read and learn: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-add-column/

Also be VERY careful with this kind of database operations!

Hi @ppp19​ ,

Like @Mark Stoakes​ says, first use  SchemaScanners to get all information of the existing columns in the database table and the columns you want to add (you get a list attribute with all the attribute names and their data types), analyse the attributes to determine which columns you want to add, then use an SQLExecutor to add the new columns to the existing table.

Maybe something like this (untested):

Alter_Table

P
Badge

Read and learn: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-add-column/

Also be VERY careful with this kind of database operations!

Hi @geomancer​ ,

I have reached up to FeatureJoiner transformer as you have shown in the workbench template above... However, I am stuck at the AttributeManager transformer. My question: how do you translate FME data type into PostgreSQL data type using attribute manager? Can you please tell me the formula for this?

geomancer
Rank
Userlevel 4
Badge +36

Read and learn: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-add-column/

Also be VERY careful with this kind of database operations!

Hi @ppp19​ ,

There is no formula, you will have to figure it out yourself.

The fme_data_types are listed here, for PostgreSQL you can find them here.

So for example fme_int64: Integer data that occupies 8 bytes corresponds with bigint (alias int8): signed eight-byte integer

Reply


Community Stats

28,709
Posts
109,481
Replies
36,554
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.