Question

What happens between FME PostgreSql writer and postgreSql running a query?

  • 24 February 2023
  • 5 replies
  • 0 views

Badge

Hi,

 

In my workspace I work with coordinates in different projections. I store them as numeric values. E.G. Geolat: 52.0324234.

 

I want to write this to a PostgreSql database, which has the same type as is required for the value. Seems all good. But.

 

When I start writing I get an error where it complaining about the input syntax is not of type 'numeric'. I see a query inside the log, Where it changes the value 5.2023617 from Longitude to " E'5.2023617' ". Which is a string. Which I dont require. 

 

Does anybody know where to look at or how to solve it?


5 replies

Userlevel 2
Badge +9

Hi @hendriksdaan​, when you write your data to the destination Postgresql table, what is the data type set to in the Writer Feature Type? Do you have it set to match the schema of the existing table?

 

Additionally, if you're able to share your workspace with us and the full log file from running the workspace, this will help us to investigate the issue more.

Badge

Hi @danminneyatsaf​ ,

 

Thanks for responding!

The Writer feature type of all the attributes is set to 'numeric(1000,500)'. All the data comming into that writer is also in the format and type I require.

The database table itself is for the relevant attributes also set to 'nummeric'. There is already existing data in the database table which is also in the correct type and format.

So its all the exactly the same.

Perhaps usefull to add, I say attributes, because its about 6 coordinates in 3 different projects. Wgs84(geolat, geolon), UTM(UTMN, UTME) and RDX , RDY.

They all seem to add the 'E' at the front and make it a string. E would suggest Easting. But thats weird for latitude coordinates.

 

Sadly I cant provide you with the workspace. But Can provide a full log when writing to the writer. I could also provide some extra about the writer itself if that would be helpfull.

See the attachment

 

So your comment made me check an extra item. I checked the incoming data. Which comes from a CSV file. Which I saw it gives all the attributes a type of String. So I changed it to correct types 'Real64'.

I also have a reader to read a View from the Database. Which has the correct types in. But FME makes them also string. There is probally a autoconversion in the background because I cant execute statistics and what not on the numbers. But writing that eventually to the prostgresSQl writer makes the error with strings, which should be numbers.

I used MSQL before using Postgress. That writer then did work. But for some reason not with PostgreSQL.

 

But sporadic/random information, hope it makes it clear :)

Userlevel 2
Badge +9

Hi @danminneyatsaf​ ,

 

Thanks for responding!

The Writer feature type of all the attributes is set to 'numeric(1000,500)'. All the data comming into that writer is also in the format and type I require.

The database table itself is for the relevant attributes also set to 'nummeric'. There is already existing data in the database table which is also in the correct type and format.

So its all the exactly the same.

Perhaps usefull to add, I say attributes, because its about 6 coordinates in 3 different projects. Wgs84(geolat, geolon), UTM(UTMN, UTME) and RDX , RDY.

They all seem to add the 'E' at the front and make it a string. E would suggest Easting. But thats weird for latitude coordinates.

 

Sadly I cant provide you with the workspace. But Can provide a full log when writing to the writer. I could also provide some extra about the writer itself if that would be helpfull.

See the attachment

 

So your comment made me check an extra item. I checked the incoming data. Which comes from a CSV file. Which I saw it gives all the attributes a type of String. So I changed it to correct types 'Real64'.

I also have a reader to read a View from the Database. Which has the correct types in. But FME makes them also string. There is probally a autoconversion in the background because I cant execute statistics and what not on the numbers. But writing that eventually to the prostgresSQl writer makes the error with strings, which should be numbers.

I used MSQL before using Postgress. That writer then did work. But for some reason not with PostgreSQL.

 

But sporadic/random information, hope it makes it clear :)

Hi @hendriksdaan​ thanks for the additional information. So far I haven't been able to reproduce the issue on the same version of FME. Without the workspace on hand, it's hard to tell what might be causing the issue. After taking a look at your log file, I have a few follow-up questions:

  • It looks like you might be using a ChangeDetector transformer and then the fme_db_operation attribute in the FME Writer. Is this correct? If you were to try writing to a test table with just an Insert function, does the issue still occur? This will tell us if something is happening due to the Update operation.
  • Would you be able to share a screenshot of your Writer Feature Type on the Parameters tab and the User Attributes tab?
  • If you download the latest version of FME 2022 and re-test this issue (after ensuring to update you Readers and Writers) do you still run into this issue?
Badge +2

@hendriksdaan​ We're pretty sure we've encountered this before but I can't track down where. It might be related to some of the UTF8 encoding changes we've made. It looks like you're using FME 2022.0. Are you able to upgrade to a more recent version of FME 2022 and see if that helps? (you can have multiple versions of FME Desktop installed - just use a different install folder i.e. c:\\apps\\fme2022.2

Badge

Hi,

 

Sorry for the late response. In the meanwhile I solved it by writing the data to a .CSV and then reading it again and writing that to the database. That worked. Bit of a work arround.

 

@Mark Stoakes​ I'll check if a newer version will work. Not sure when, but i'll try to give a update this week somewhere.

 

@danminneyatsaf​  I'll try a newer version of FME. Otherwise I'll let it be. Because I found a work arround. Fortunately it is a onetime execution workspace, so its not really important for me.

Reply