Skip to main content
Question

Can FME create a pg database tables with attribute names that have upper cases?


Forum|alt.badge.img

Can FME create a pg database tables with attribute names that have upper cases? I know that this is possible in postgres - column names are case-sensitive when double-quoted. But no matter which parameters of PostgreSQL writer I select (standard/dynamic writing, with checked or unchecked option to Lowe Case Attribute Names in Table Creation Parameter), FME keeps replacing double-quotes with underscores and output is always folded to lowercase. 

 

Is it something that can be bypassed? 

13 replies

takashi
Influencer
  • February 11, 2022

Hi @zuzanna_sz​ , I was able to create a table with uppercase column names if "Lower Case Attribute Names" is unchecked.

Retry setting column names without quoting.postgresql-writer-feature-type-user-attributes


Forum|alt.badge.img
  • Author
  • February 11, 2022

hi @Takashi Iijima​ 

simplest combination works - that's right! No double-quotes and unchecked parameter give the result with Upper Case column names.

My gole is to use Dynamic Writer with schema coming from a table (workspace resource reader is an excel with big schema table). This is not working with Attribute Names specified with double-quotes or without them in the external excel schema source table... Have you managed to output to pg table with Dynamic definition?

 


takashi
Influencer
  • February 11, 2022
zuzanna_sz wrote:

hi @Takashi Iijima​ 

simplest combination works - that's right! No double-quotes and unchecked parameter give the result with Upper Case column names.

My gole is to use Dynamic Writer with schema coming from a table (workspace resource reader is an excel with big schema table). This is not working with Attribute Names specified with double-quotes or without them in the external excel schema source table... Have you managed to output to pg table with Dynamic definition?

 

I was able to reproduce the issue you have encountered - the Dynamic PostgreSQL writer with a resource schema table seems to force column names to lowercase always, even if the "Lower Case Attribute Names" has been unchecked.

I don't know if it's an intentional behavior, hope someone from Safe to clarify this. @danatsafe​ ?

Attached a repro.


Forum|alt.badge.img
  • Author
  • February 14, 2022
takashi wrote:

I was able to reproduce the issue you have encountered - the Dynamic PostgreSQL writer with a resource schema table seems to force column names to lowercase always, even if the "Lower Case Attribute Names" has been unchecked.

I don't know if it's an intentional behavior, hope someone from Safe to clarify this. @danatsafe​ ?

Attached a repro.

@Takashi Iijima​ Thanks for your input here! that's exactly what I was trying to achieve. Let's see if this can be adjusted by Safe.

 


DanAtSafe
Safer
Forum|alt.badge.img+18
  • Safer
  • February 14, 2022
takashi wrote:

I was able to reproduce the issue you have encountered - the Dynamic PostgreSQL writer with a resource schema table seems to force column names to lowercase always, even if the "Lower Case Attribute Names" has been unchecked.

I don't know if it's an intentional behavior, hope someone from Safe to clarify this. @danatsafe​ ?

Attached a repro.

Hi @zuzanna_sz​ and @Takashi Iijima​ Unfortunately this is a known issue; for your reference FMEENGINE-47925


Forum|alt.badge.img
  • Author
  • February 18, 2022
takashi wrote:

I was able to reproduce the issue you have encountered - the Dynamic PostgreSQL writer with a resource schema table seems to force column names to lowercase always, even if the "Lower Case Attribute Names" has been unchecked.

I don't know if it's an intentional behavior, hope someone from Safe to clarify this. @danatsafe​ ?

Attached a repro.

Thank you for your feedback @danatsafe​ 

Do you know when this issue can be resolved? This fix allows me to keep the entire project within one DB system (postgreSQL). At the moment, I have to use MS SQL Server writer to enable dynamic writing - and it creates additional step in almost all of my projects.


DanAtSafe
Safer
Forum|alt.badge.img+18
  • Safer
  • February 19, 2022
takashi wrote:

I was able to reproduce the issue you have encountered - the Dynamic PostgreSQL writer with a resource schema table seems to force column names to lowercase always, even if the "Lower Case Attribute Names" has been unchecked.

I don't know if it's an intentional behavior, hope someone from Safe to clarify this. @danatsafe​ ?

Attached a repro.

Hi @zuzanna_sz Unfortunately there's no date set yet on FMEENGINE-47925, but we'll be sure to let you know when it's been resolved.


Forum|alt.badge.img
  • Author
  • June 27, 2022
takashi wrote:

I was able to reproduce the issue you have encountered - the Dynamic PostgreSQL writer with a resource schema table seems to force column names to lowercase always, even if the "Lower Case Attribute Names" has been unchecked.

I don't know if it's an intentional behavior, hope someone from Safe to clarify this. @danatsafe​ ?

Attached a repro.

hi @danatsafe​ do you have any updates on the FMEENGINE-47925?


DanAtSafe
Safer
Forum|alt.badge.img+18
  • Safer
  • June 28, 2022
takashi wrote:

I was able to reproduce the issue you have encountered - the Dynamic PostgreSQL writer with a resource schema table seems to force column names to lowercase always, even if the "Lower Case Attribute Names" has been unchecked.

I don't know if it's an intentional behavior, hope someone from Safe to clarify this. @danatsafe​ ?

Attached a repro.

Hi @zuzanna_sz​ No updates yet. Sorry.


danminneyatsaf
Safer
Forum|alt.badge.img+12
takashi wrote:

I was able to reproduce the issue you have encountered - the Dynamic PostgreSQL writer with a resource schema table seems to force column names to lowercase always, even if the "Lower Case Attribute Names" has been unchecked.

I don't know if it's an intentional behavior, hope someone from Safe to clarify this. @danatsafe​ ?

Attached a repro.

@zuzanna_sz​ FMEENGINE-47925 has been resolved. The issue you're running into with the PostGIS Writer is still being worked on and is tracked by a different ticket number.

 

The tracking number for that ticket is FMEENGINE-9157 - "PostGIS Writer: Using Dynamic schema ignores Generic Geometry / Lowercase Attributes settings"

Since the initial issue that was blocking this has been resolved, we are much closer to being able to put out a fix for the bug. We'll keep you updated for when FMEENGINE-9157 has been fixed.


Forum|alt.badge.img
  • Author
  • July 25, 2023
takashi wrote:

I was able to reproduce the issue you have encountered - the Dynamic PostgreSQL writer with a resource schema table seems to force column names to lowercase always, even if the "Lower Case Attribute Names" has been unchecked.

I don't know if it's an intentional behavior, hope someone from Safe to clarify this. @danatsafe​ ?

Attached a repro.

Dear @danminneyatsaf​ , thanks a lot for the update! My workaround is to create db schema first (I generate set of CREATE TABLE queries) and then use it in a dynamic writer - which only inserts to the existing tables. I will keep an eye on FMEENGINE-9157.


tara_he
Contributor
Forum|alt.badge.img+4
  • Contributor
  • December 13, 2023

@danminneyatsaf​ is there an update on ticket FMEENGINE-9157? Thanks.


danminneyatsaf
Safer
Forum|alt.badge.img+12
tara_he wrote:

@danminneyatsaf​ is there an update on ticket FMEENGINE-9157? Thanks.

Hi @Tara Preston​. Unfortunately I don't have any updates on FMEENGINE-9157 at the moment as it's state hasn't changed since I last posted here. I've updated the ticket with a comment mentioning that you'd like to be updated when a fix is released.


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