Hi guys! I'm new to FME. So far the software seems great but I'm having an issue with the data in my database persisting. I have a setup where I've created a CSV reader connected to a Postgres writer. When I run it, everything is ran fine without errors. In the visual preview it shows that the Postgres database has been written to. HOWEVER, when I do a Select * From database in a separate SQL command line, nothing is showing up in my tables. Its as if its not legitimately writing into the database. The connections are right, and this is happening on a local Postgres database I have running. I'm thinking there may be a step I missed? I did this all pretty straight-forward without tinkering with customizations. If anyone could assist or point me to a step-by-step guide for achieving this I'd really appreciate it. I have found similar guides on youtube but nothing for exactly CSV to Postgres. Thanks all!
Postgres Database not being written to
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+13- Safer
Welcome to the FME Community @adeel182. Are you able to upload your log file and/or workspace (.fmw) here? It will help us troubleshoot to see if there's anything obvious.
Can you ensure that you don't have the Writer redirecting to FME Data Inspector:
If this is enabled, I think the workspace will run successfully bring up the results in Visual Preview without writing it out to the destination.
Welcome to the FME Community @adeel182. Are you able to upload your log file and/or workspace (.fmw) here? It will help us troubleshoot to see if there's anything obvious.
Can you ensure that you don't have the Writer redirecting to FME Data Inspector:
If this is enabled, I think the workspace will run successfully bring up the results in Visual Preview without writing it out to the destination.
Thank you for the quick response! Yes I did indeed have that redirect turned on. I've turned it off and ran again and it didn't seem to fix the issue unfortunately. Sure I've attached my fmw file and will reply with log.
LOG:
Feature Caching is ON
The workspace may run slower because features are being recorded on all output ports.
Command-line to run this workspace:
/Library/FME/2020.0/fme /Users/adeelbhatti/Documents/FME/Workspaces/exxon.fmw
--SourceDataset_CSV2 "/Users/adeelbhatti/Downloads/Test.csv"
--DestDataset_POSTGRES "Test2"
Starting translation...
FME 2020.0.0.1 (20200316 - Build 20202 - macosx)
FME_HOME is '/Library/FME/2020.0/'
FME Database Edition (node locked-crc)
Serial Number: 0
Temporary License: 354 days left.
Machine host name is: Adeels-MacBook-Pro-2.local
START - ProcessID: 83453, peak process memory usage: 60120 kB, current process memory usage: 60120 kB
FME Configuration: Command line arguments are `/Library/FME/2020.0/fme' `/Users/adeelbhatti/Documents/FME/Workspaces/wb-xlate-1586907497312_77808' `LOG_STANDARDOUT' `YES' `LogCountServerName' `{f2d3e068-3682-4069-a281-e81a04308dd0}'
FME Configuration: Connection Storage: '/Users/adeelbhatti/Library/Caches/Safe Software/FME/'
Shared folders for formats are : /Library/FME/2020.0/datasources;/Users/adeelbhatti/Library/Application Support/FME/Formats
Shared folders for transformers are : /Users/adeelbhatti/Library/Caches/Safe Software/FME/Packages/20202-macosx/transformers;/Library/FME/2020.0/transformers
Shared folders for coordinate systems are : /Users/adeelbhatti/Library/Application Support/FME/CoordinateSystems
Shared folders for coordinate system exceptions are : /Users/adeelbhatti/Library/Application Support/FME/CoordinateSystemExceptions
Shared folders for coordinate system grid overrides are : /Users/adeelbhatti/Library/Application Support/FME/CoordinateSystemGridOverrides
Shared folders for CS-MAP transformation exceptions are : /Users/adeelbhatti/Library/Application Support/FME/CsmapTransformationExceptions
Shared folders for transformer categories are : /Users/adeelbhatti/Library/Application Support/FME/TransformerCategories
FME Configuration: Reader Keyword is `MULTI_READER'
FME Configuration: Writer Keyword is `MULTI_WRITER'
FME Configuration: Writer Group Definition Keyword is `MULTI_WRITER_DEF'
FME Configuration: Reader type is `MULTI_READER'
FME Configuration: Writer type is `MULTI_WRITER'
FME Configuration: No destination coordinate system set
FME Configuration: Current working folder is `/Users/adeelbhatti/Documents/FME/Workspaces'
FME Configuration: Temporary folder is in the system location `/private/tmp'
FME Configuration: FME_HOME is `/Library/FME/2020.0/'
FME Configuration: FME_BASE is 'no'
FME Configuration: FME_MF_DIR is '/Users/adeelbhatti/Documents/FME/Workspaces/'
FME Configuration: FME_MF_NAME is 'exxon.fmw'
FME Configuration: FME_PRODUCT_NAME is 'FME(R) 2020.0.0.1'
System Status: 409.66 GB of disk space available in the FME temporary folder (/private/tmp)
System Status: 16.00 GB of physical memory available
System Status: 19.00 GB of virtual memory available
Operating System: Mac OS X Version 10.14.6 (Build 18G103)
FME Platform: macosx
Locale: en_US
System Encoding: UTF-8
FME Configuration: Start freeing memory when the process exceeds 17.83 GB
FME Configuration: Stop freeing memory when the process is below 13.37 GB
Creating writer for format:
Creating reader for format:
MULTI_READER(MULTI_READER): Will fail with first member reader failure
Using Multi Reader with keyword `MULTI_READER' to read multiple datasets
Using MultiWriter with keyword `MULTI_WRITER' to output data (ID_ATTRIBUTE is `multi_writer_id')
Writer output will be ordered by value of multi_writer_id
DYLD_LIBRARY_PATH is not set
Loaded module 'LogCount_func' from file '/Library/FME/2020.0//plugins/logcount_func.so'
FME API version of module 'LogCount_func' matches current internal version (3.8 20200115)
Emptying factory pipeline
Router and Unexpected Input Remover (RoutingFactory): Tested 0 input feature(s), wrote 0 output feature(s): 0 matched merge filters, 0 were routed to output, 0 could not be routed.
Unexpected Input Remover Nuker (TeeFactory): Cloned 0 input feature(s) into 0 output feature(s)
CSV(CSV2_1) Splitter (TeeFactory): Cloned 0 input feature(s) into 0 output feature(s)
CSV_CSV2_1 Feature Counter -1 4 (TeeFactory): Cloned 0 input feature(s) into 0 output feature(s)
Restoring 1 feature(s) from FME feature store file `/private/tmp/wb-cache-Exxon-UIMyrf/Main_CSV -1 2 -1 0 e79d57ead3deb870b8ee5d7c218f729c62e67de1.ffs'
FME Configuration: Using connection values from 'Test2' for POSTGRES_1
Creating writer for format: PostgreSQL
Trying to find a DYNAMIC plugin for writer named `POSTGRES'
Loaded module 'POSTGRES' from file '/Library/FME/2020.0//plugins/postgis.so'
FME API version of module 'POSTGRES' matches current internal version (3.8 20200115)
FME Configuration: No destination coordinate system set
POSTGRES writer: Splitting bulk mode features into features
FME API version of module 'POSTGRES' matches current internal version (3.8 20200115)
Writer `POSTGRES_1' of type `POSTGRES' using group definition keyword `POSTGRES_1_DEF'
FME API version of module 'POSTGRES' matches current internal version (3.8 20200115)
Opening POSTGRES writer for dataset 'postgres'...
PostGIS client encoding: 'UTF8'
PostgreSQL 12.2 on x86_64-apple-darwin, compiled by Apple LLVM version 6.0 (clang-600.0.54) (based on LLVM 3.5svn), 64-bit
Testing for the existence of table 'public.ExxonTest'...
Using existing table 'public.ExxonTest'
POSTGRES Writer: Creating 'streaming Bulk Copy' feature type processor for feature type 'ExxonTest'
Committed transaction at feature '1000'
CSV_CSV2_1_-1_4_Player (RecorderFactory): Played back 1012 feature(s) from file `/private/tmp/wb-cache-Exxon-UIMyrf/Main_CSV -1 2 -1 0 e79d57ead3deb870b8ee5d7c218f729c62e67de1.ffs'
Destination Feature Type Routing Correlator (RoutingFactory): Tested 1012 input feature(s), wrote 1012 output feature(s): 0 matched merge filters, 1012 were routed to output, 0 could not be routed.
Final Output Nuker (TeeFactory): Cloned 0 input feature(s) into 0 output feature(s)
Successfully closed POSTGRES database writer
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Feature output statistics for `POSTGRES' writer using keyword `POSTGRES_1':
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Features Written
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
ExxonTest 1012
==============================================================================
Total Features Written 1012
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Features Read Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Read 0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Features Written Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
ExxonTest 1012
==============================================================================
Total Features Written 1012
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
-~ ~-
-~ Feature caches have been recorded at every stage of the translation. ~-
-~ To inspect the recorded features, ~-
-~ click the feature cache icons next to the ports. ~-
-~ ~-
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Translation was SUCCESSFUL with 7 warning(s) (1012 feature(s) output)
FME Session Duration: 0.7 seconds. (CPU: 0.2s user, 0.0s system)
END - ProcessID: 83453, peak process memory usage: 72136 kB, current process memory usage: 72136 kB
Translation was SUCCESSFUL
Ok I think I got it working. The fix? I just had to name the table using all lower case letters. I don't know how that works but I'm rolling with it. Postgres has some issues with capitalized letters in table names. Thank you guys!
You just have to be careful about casing when doing the selects, e.g.
select * from "TableWithMixedCase"is not the same as
select * from TableWithMixedCaseNotice the double quotes to imply that the case is literal and not just informational.
The first statement will actually look in the table TableWithMixedCase, the second one will look for a table tablewithmixedcase, as postgres defaults to lower case table names.