Skip to main content

Adding PrimaryKey (PostgreSQL PostGIS database) - Best Practices

  • January 21, 2025
  • 1 reply
  • 50 views

9val
Contributor
Forum|alt.badge.img+5
  • Contributor

Hello all,

 

I would like to create a simple FME workspace in which PostGIS writer automatically reads attributes from the reader and adds an attribute with PrimaryKey (there are 30+ feature types in total). Is it possible to do it somehow? 

 

I have seen and tested this method:

postgresql - FME PostGIS writer define column as serial/PrimaryKey with pre-existing id values - Geographic Information Systems Stack Exchange


However, the goal is to set up a straightforward process with automatic attribute definition.

 

I would really appreciate it if you could share what methods you use.

1 reply

hkingsbury
Celebrity
Forum|alt.badge.img+53
  • Celebrity
  • January 21, 2025

I’ve attached an example workspace built in 2024.1. It’s built as a dynamic workspace so you should be able to substitute the database connection with your own, then run it. It will add a new field called ‘guid’ to every table the connect has access to.

Please don’t run it blindly. Have a look at it first and understand what it does before trying on a local/dev env where data loss will not be an issue.

It is a bit of a ‘sledgehammer’ approach where it drops the existing table and recreates it with a new field.

 

A safer, yet slightly more complex approach would be to make use of the ALTER TABLE function in PostgreSQL and get the database to populate the guids. This doesn’t drop and recreate tables, but is a database level action rather than an FME one. That being said you could run it from an SQLExecutor. An example of that is something along the lines of:
https://chatgpt.com/share/67900e19-c224-8005-a8d9-dae8ddc2dcc0


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings