Question

Update existing data in postgres

  • 19 September 2016
  • 6 replies
  • 23 views
A
Badge

Hello,

I think there is a problem with the update of postgres data. I have a table with uuids and dates. The statements for those types are not constructed correctly.

FME throws an error for the date like this:

'ERROR: syntax error at or near "2009"

LINE 1: ...NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, E'2009-10-26...

^

You can see that it is used like a string. Same happens usually to the uuid - there no quotes are inserted. Postgres usually expects quotes around the uuids.

I also tried the DatabaseUpdater, but the same error occured.

6 replies

mark2atsafe
Rank
Userlevel 4
Badge +25

Hi @aleho

I think this might be a known issue, but am not sure. Can you contact our support team (safe.com/support) and ask them. Tell them I think it might be related to PR#51142 ("POSTGRES: UPDATES with column type UUID are failing"). They will be able to confirm, increase the priority on the issue, and maybe provide a workaround.

Apologies for any inconvenience this is causing.

Mark

J
Badge +7

Hi @aleho,

Is it possible to keep us informed about this? I stumbled upon this issue to and end up 'solving' it by using an SQLExecutor instead.

Thanks in advance!

E
Badge

I've had the same issue trying to update a PostGIS database with UUID fields - I also had to use an SQLExecutor (plus a GeometryExtractor set to 'OGC Well Known Binary' to access the geometry) as a work around. I've contacted support too - hopefully this will be resolved soon.

J
Badge

Hi @aleho

I think this might be a known issue, but am not sure. Can you contact our support team (safe.com/support) and ask them. Tell them I think it might be related to PR#51142 ("POSTGRES: UPDATES with column type UUID are failing"). They will be able to confirm, increase the priority on the issue, and maybe provide a workaround.

Apologies for any inconvenience this is causing.

Mark

Problem still exists in 2019 beta, so I contacted support. Apparently the fix is being worked on, but I requested for it's priority to be escalated. No workarounds are currently suggested by Safe.

I've just written my UUID's to a CSV, and will be updating my data through sql statements. (using a SQLExecutor in FME triggered the same error for me.)

Thanks for the suggestion Mark!
R
Badge

This problem still appears in FME 2019.1. I am unable to update values in a postgres uuid column. An alernative workaround is to place an AttributeManager before the writer and add quotes to the uuid values (e.g. change attribute value of the uuid column to '@Value(uuid)' ). I have contacted support to advise on the status of this longstanding issue.

nampreetatsafe
Rank
Userlevel 1
Badge +10

This problem still appears in FME 2019.1. I am unable to update values in a postgres uuid column. An alernative workaround is to place an AttributeManager before the writer and add quotes to the uuid values (e.g. change attribute value of the uuid column to '@Value(uuid)' ). I have contacted support to advise on the status of this longstanding issue.

@rhartley, I'm sorry you ran into this issue. I see your support case has been attached to the existing ticket for this issue (internal reference FMEENGINE-10424).

Reply


Community Stats

28,724
Posts
109,517
Replies
36,563
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.