I am using FME 2013 SP2 to bulk load data into PostGIS 9.2 from Oracle 10 using a very simple workbench. I can set the attribute names to lower case in the PostGIS writer (as well as create the GIST index, sequence and primary key) but I also need to set the table names / feature type name to lowercase. I have several hundred tables to copy over and it would be nice to change case automagically rather than manually editing the workbench.
Any suggestions?
Thanks
Ross
The BulkAttributeRenamer only works on All Attributes or Selected Attributes and not on the table name / feaure type name.
In PostGIS after loading my tables look like: schemaname.TABLENAME and the columns all have lowercase names.
I'd like my table names to be lowercase.
Ross
yes, I just realised that I had misunderstood your question :-)
There is no very easy way to to this, as far as I know, although it is possible to modify the .fmw file directly, since they are just regular text files. You could then do a search/replace of some sort to convert the table names.
You could even do it with FME, using a TextFile Reader/Writer and a couple of transformers, such as the StringSearcher and the CaseChanger.
David
+13
And if you change the fme_feature_type attribute into lowercase? what happens then?
Arnaud
Not the BulkAttributeRenammer nor the StringCaseChanger work on the $(FEATURE_TYPES) parameter :
Here's a possible solution:
The trick is to use the SchemaSetter and set the output feature type schema definition to "Schema from schema feature" (the technique is documented here). The SchemaSetter also has a handy setting to exclude certain attributes starting with a prefix, you can e.g. set it to "SHAPE_" to exlcude SHAPE_Length, SHAPE_Area, etc.
Also use a StringCaseChanger to convert fme_feature_type to lower case.
Tested with FME 2016.1.3.2 and Postgresql 9.4. Both table names and attribute names were written in lower case.
I solved my case using simple SQL queries to have it modified afterwards.
If you have many tables have a look at :
http://www.postgresonline.com/journal/archives/141-Lowercasing-table-and-column-names.html
Some sql queries to construct the sql instructions: (2 steps)
For attributes :
-- As David Fetter kindly pointed out, this looks cleaner, returns one record per ddl and is more psql friendly
SELECT 'ALTER TABLE ' || quote_ident(c.table_schema) || '.'
|| quote_ident(c.table_name) || ' RENAME "' || c.column_name || '" TO ' || quote_ident(lower(c.column_name)) || ';' As ddlsql
FROM information_schema.columns As c
WHERE c.table_schema NOT IN('information_schema', 'pg_catalog')
AND c.column_name <> lower(c.column_name)
ORDER BY c.table_schema, c.table_name, c.column_name;And for tables :
-- lower case table names -- the psql friendly and more reader-friendly way
SELECT 'ALTER TABLE ' || quote_ident(t.table_schema) || '.'
|| quote_ident(t.table_name) || ' RENAME TO ' || quote_ident(lower(t.table_name)) || ';' As ddlsql
FROM information_schema.tables As t
WHERE t.table_schema NOT IN('information_schema', 'pg_catalog')
AND t.table_name <> lower(t.table_name)
ORDER BY t.table_schema, t.table_name;Generates sql actions like :
ALTER TABLE public."SPRINT" RENAME TO sprint;Can be run with pgAdmin (my way) or in a SQL executor (assumption) on FME afterwards.
Did the job for me.
I solved my case using simple SQL queries to have it modified afterwards.
If you have many tables have a look at :
http://www.postgresonline.com/journal/archives/141-Lowercasing-table-and-column-names.html
Some sql queries to construct the sql instructions: (2 steps)
For attributes :
-- As David Fetter kindly pointed out, this looks cleaner, returns one record per ddl and is more psql friendly
SELECT 'ALTER TABLE ' || quote_ident(c.table_schema) || '.'
|| quote_ident(c.table_name) || ' RENAME "' || c.column_name || '" TO ' || quote_ident(lower(c.column_name)) || ';' As ddlsql
FROM information_schema.columns As c
WHERE c.table_schema NOT IN('information_schema', 'pg_catalog')
AND c.column_name <> lower(c.column_name)
ORDER BY c.table_schema, c.table_name, c.column_name;And for tables :
-- lower case table names -- the psql friendly and more reader-friendly way
SELECT 'ALTER TABLE ' || quote_ident(t.table_schema) || '.'
|| quote_ident(t.table_name) || ' RENAME TO ' || quote_ident(lower(t.table_name)) || ';' As ddlsql
FROM information_schema.tables As t
WHERE t.table_schema NOT IN('information_schema', 'pg_catalog')
AND t.table_name <> lower(t.table_name)
ORDER BY t.table_schema, t.table_name;Generates sql actions like :
ALTER TABLE public."SPRINT" RENAME TO sprint;Can be run with pgAdmin (my way) or in a SQL executor (assumption) on FME afterwards.
Did the job for me.
+6
Because SchemaSetter is not able to properly maintain the original type and order of the attribute, so I think the use of Schema Reader is a better choice.
?Here, assume that your attribute's count will not exceed 101. For the sake of simplicity I have specified attribute.name{0}~attribute.name{100} in StringCaseChanger.
+6
Here's a possible solution:
The trick is to use the SchemaSetter and set the output feature type schema definition to "Schema from schema feature" (the technique is documented here). The SchemaSetter also has a handy setting to exclude certain attributes starting with a prefix, you can e.g. set it to "SHAPE_" to exlcude SHAPE_Length, SHAPE_Area, etc.
Also use a StringCaseChanger to convert fme_feature_type to lower case.
Tested with FME 2016.1.3.2 and Postgresql 9.4. Both table names and attribute names were written in lower case.
?Here, assume that your attribute's count will not exceed 101. For the sake of simplicity I have specified attribute.name{0}~attribute.name{100} in StringCaseChanger.
Because SchemaSetter is not able to properly maintain the original type and order of the attribute, so I think the use of Schema Reader is a better choice.
?Here, assume that your attribute's count will not exceed 101. For the sake of simplicity I have specified attribute.name{0}~attribute.name{100} in StringCaseChanger.
+6
Because SchemaSetter is not able to properly maintain the original type and order of the attribute, so I think the use of Schema Reader is a better choice.
?Here, assume that your attribute's count will not exceed 101. For the sake of simplicity I have specified attribute.name{0}~attribute.name{100} in StringCaseChanger.
In fact, In the current PostGIS Writer (FME 2016.1.3.1), Whether or not to choose "Lower Case Attribute Names", as long as it is in Dynamic mode, all the attribute name is converted to lowercase.
I was in FME 2016.0.1.2 found this problem, And talked to @takashi about it, I tested the FME 2016 1.3.1 and FME 2017 Build 17254, But it seems that the problem is still not resolved. So now it is more difficult to change all attribute names to uppercase. Currently to achieve it, @nono said is a good way.
4 years later a much nicer way to do it fully on FME side :
Using feature reader and feature writer :
Select desired tables with a feature reader, expose fme_feature_type, change case in the schema AND in the features, use a postgreSQL or postgis feature writer.
With this fully dynamic approach you can get data and data structure from A to a datastructure of your own B following some of your customizations (schema modification, table lowercase, ...)
My lonely regret is the way to embed connection parameters which is much easier in standard readers and writers.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.