Question

Write in a PostgreSQL editable view

rbovard
Rank
Badge

Hello,

I have an editable view in PostGIS (PostgreSQL) and I'm trying to write in it with FME.

For that, I've:

  • Added my view from a PostGIS reader (to have its structure - and confirm it exists)
  • Duplicated this view in a new PostGIS writer (to keep its structure)
  • Set the table handling as `Use existing`
  • Connected a worflow to end in my view

But when I run the workspace, I'm facing this error:

POSTGIS Writer: Table 'xxx.yyy' does not exist. Please check the Table Name specified, or modify the Table Handling value under Format Parameters

So my question is: is it possible to write in an editable view?

For the record, I can write in my view with another client (QGIS) with the same credentials.

Thanks for any input.

Rémi

15 replies

M
Badge +2

@rbovard​ I couldn't reproduce your error, I've attached an example workspace that creates a table, creates a view and then writes to the view. So I suspect permissions or possibly an incorrect view name if you're using a table qualifier.

 Requirements for updateable views.

rbovard
Rank
Badge

@rbovard​ I couldn't reproduce your error, I've attached an example workspace that creates a table, creates a view and then writes to the view. So I suspect permissions or possibly an incorrect view name if you're using a table qualifier. 

 Requirements for updateable views.

Thanks @Mark Stoakes​ for your answer and your example.

I'm glad to see FME can write into views, but it still not working on my side.

It's strange because:

  • If I rename my feature type in writer, it creates a new table in my database --> Not a rights issue
  • If I set the writer action as `Create if needed` it shows me `ERROR: relation "my_view" already exists` --> Not a qualifier issue
  • If I set the writer `Use existing` (which I was initially doing), it shows me `Table 'my_view' does not exist.`

Maybe it's something with PostGIS writer? Like I said, I can update my view via other client (QGIS), it also work in pure SQL:

INSERT INTO <schema>.<view>(<geom>)
VALUES (ST_GeomFromText('...'));

Any other idea?

Best regards,

Rémi

M
Badge +2

Thanks @Mark Stoakes​ for your answer and your example.

I'm glad to see FME can write into views, but it still not working on my side.

It's strange because:

  • If I rename my feature type in writer, it creates a new table in my database --> Not a rights issue
  • If I set the writer action as `Create if needed` it shows me `ERROR: relation "my_view" already exists` --> Not a qualifier issue
  • If I set the writer `Use existing` (which I was initially doing), it shows me `Table 'my_view' does not exist.`

Maybe it's something with PostGIS writer? Like I said, I can update my view via other client (QGIS), it also work in pure SQL:

INSERT INTO <schema>.<view>(<geom>)
VALUES (ST_GeomFromText('...'));

Any other idea?

Best regards,

Rémi

@rbovard​ My first example was PostgreSQL so I thought it might be PostGIS that was the issue here. So I created a second example with a PostGIS view (attached). This seems to work OK as well. So I suspect it's the way that your view has been created that is causing FME to stumble.  

I think you'll need to share you example (SQL, workspace & sample data)  with the community, or open a support issue with your local FME Reseller. (Please post the resolution answer back here if there is one)

rbovard
Rank
Badge

Thanks @Mark Stoakes​ for your answer and your example.

I'm glad to see FME can write into views, but it still not working on my side.

It's strange because:

  • If I rename my feature type in writer, it creates a new table in my database --> Not a rights issue
  • If I set the writer action as `Create if needed` it shows me `ERROR: relation "my_view" already exists` --> Not a qualifier issue
  • If I set the writer `Use existing` (which I was initially doing), it shows me `Table 'my_view' does not exist.`

Maybe it's something with PostGIS writer? Like I said, I can update my view via other client (QGIS), it also work in pure SQL:

INSERT INTO <schema>.<view>(<geom>)
VALUES (ST_GeomFromText('...'));

Any other idea?

Best regards,

Rémi

Thanks a lot @Mark Stoakes​ for this second example.

In fact you're still using a PostgreSQL writer and features with lat/lng attributes, but - if I understand it right - they aren't spatial features and you're not writing in a spatial view.

My use case is writing spatial features, in a spatial view via a PostGIS writer.

Is it clearer for you or should I try to give you a basic workspace to illustrate that?

Anyway, thanks again for trying to solve my issue ;)

Best regards,

Rémi

M
Badge +2

Thanks @Mark Stoakes​ for your answer and your example.

I'm glad to see FME can write into views, but it still not working on my side.

It's strange because:

  • If I rename my feature type in writer, it creates a new table in my database --> Not a rights issue
  • If I set the writer action as `Create if needed` it shows me `ERROR: relation "my_view" already exists` --> Not a qualifier issue
  • If I set the writer `Use existing` (which I was initially doing), it shows me `Table 'my_view' does not exist.`

Maybe it's something with PostGIS writer? Like I said, I can update my view via other client (QGIS), it also work in pure SQL:

INSERT INTO <schema>.<view>(<geom>)
VALUES (ST_GeomFromText('...'));

Any other idea?

Best regards,

Rémi

@rbovard​ Sorry, that was my error. I forgot to switch the last, and important, writer to PostGIS. When I do that I reproduce your error. My guess is that there is additional SQL that needs to be called using the GDAL driver to support writing to an editable view (or perhaps when you create the view). 

At the moment it looks like FME doesn't support writing to an editable view that has a spatial column.

M
Badge +2

Revised workspace that reproduces @rbovard​ 's error when writing to a spatial editable view.

rbovard
Rank
Badge

Hi @Mark Stoakes​ 

Do you have any update on this issue?

Thanks in advance for your feedback.

Rémi

M
Badge +2

@rbovard​ this hasn't changed. I believe that it is the way the view is created. In the attached workspace, , as far as I can tell, the view created is not recognised in QGIS. It returns the error;

Layer is not valid: The layer dbname='ORYX' host=localhost port=5432 sslmode=disable key='"longitude"' srid=4326 type=Point table="public"."cellsignalsGEOM" (geom) is not a valid layer and can not be added to the map. Reason: 

If you know how to modify the example in the workspace to create a valid view that QGIS can read then I can try and determine why FME can't read it.

rbovard
Rank
Badge

@rbovard​ this hasn't changed. I believe that it is the way the view is created. In the attached workspace, , as far as I can tell, the view created is not recognised in QGIS. It returns the error;

Layer is not valid: The layer dbname='ORYX' host=localhost port=5432 sslmode=disable key='"longitude"' srid=4326 type=Point table="public"."cellsignalsGEOM" (geom) is not a valid layer and can not be added to the map. Reason: 

If you know how to modify the example in the workspace to create a valid view that QGIS can read then I can try and determine why FME can't read it.

Thanks a lot for your feedback.

I'm not sure it's related to the view creation, since here I can load my view in QGIS but not write in it via FME (it works from QGIS or even pure SQL).

I'll try to create a very basic workspace to demonstrate it.

rbovard
Rank
Badge

Thanks a lot for your feedback.

I'm not sure it's related to the view creation, since here I can load my view in QGIS but not write in it via FME (it works from QGIS or even pure SQL).

I'll try to create a very basic workspace to demonstrate it.

@Mark Stoakes​ Please find here an attached workspace wich creates a table, then a view and try to write in it.

For the record, the view is editable from QGIS or pure SQL.

rbovard
Rank
Badge

Thanks a lot for your feedback.

I'm not sure it's related to the view creation, since here I can load my view in QGIS but not write in it via FME (it works from QGIS or even pure SQL).

I'll try to create a very basic workspace to demonstrate it.

@Mark Stoakes​ Is it better with my script? Are you able to load this view into QGIS and edit it?

takacstamas
Rank
Badge +1

@rbovard​ did you find a solution to this problem? That's exactly what I'm facing right now and i cannot find a solution.

rbovard
Rank
Badge

@rbovard​ did you find a solution to this problem? That's exactly what I'm facing right now and i cannot find a solution.

Hi @takacstamas​ 

 

Unfortunately not.

 

The workaround I used was to write to a temp table with the same structure as my editable view, and then using a SQL Executor, insert the data with a query of type

INSERT INTO <my_view> SELECT * FROM <my_temp_table>

And then drop my temp table also with SQL.

 

It wasn't the most straightforward, but it worked. After that, I didn't try with the latest versions of FME, so maybe there have been improvements in that area.

 

Rémi

jovitaatsafe
Rank
Userlevel 1
Badge +11

Hi @rbovard and @takacstamas,

I’m happy to share that an update to FME Form 2024.1 has added the ability to write to a view with a spatial column for PostGIS/PostgreSQL formats thanks to a problem report Mark had filed for the team identified internally as (FMEENGINE-70047).

So far the functionality is just available in FME 2024.1 betas. You can access the beta install here: https://engage.safe.com/support/downloads/#beta

If you have a chance to give it a try, please let us know if you have any feedback on it!

 

Happy FME’ing!

Jovita

takacstamas
Rank
Badge +1

Hi @jovitaatsafe!

I’m installed the latest version of FME Form 2024.1 Beta (Build 24553) and it work perfectly!
Thank you for resolving this issue, the FME team is the Best!

Tamás

Reply


Community Stats

28,696
Posts
109,442
Replies
36,546
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.