I made some progress over the weekend so feeling b...
# troubleshootingw
will_johnson
06/27/2022, 4:12 PMI made some progress over the weekend so feeling better today. Question, I'm using tap-postgres and target-postgres and a date column in the source database is becoming a timestamp column in the target database. Is that expected behavior? If so, is there a way to override? (I can work around this by explicitly casting the column to data in my model but trying to do things the standard way as much as possible.
e
eric_boucher
06/27/2022, 5:03 PMcan you copy paste a sample record sent by the tap?
w
will_johnson
06/27/2022, 5:43 PMHere is a record exported as json:
[
{
"id": 74602573,
"entity_id": 1234678,
"data_date": "2022-05-23",
"value_adjusted": -468.08,
"net_cash_flow_adjusted_month_to_date": 0,
"total_return_adjusted_month_to_date": 0,
"created_at": "2022-05-25 144645.924790 +00:00",
"updated_at": "2022-06-02 003040.100810 +00:00",
"value_billable": 0,
"annual_billing_rate": 0
}
]
will_johnson
06/27/2022, 5:43 PMThe data_date column is being created as timestamp by target_postgres
t
thomas_briggs
06/27/2022, 8:05 PMThe answer is probably in the SCHEMA record. The target must be interpreting whatever type the tap is sending for that field to be something that should be stored as a timestamp. Since you're going PG -> PG they should be the same but there must be some disconnect between the tap and the target. Hard to say which though.
w
will_johnson
06/27/2022, 8:24 PMIt appears to be an issue with tap-postgres. Looking at --dump=catalog and it shows date-time even though it definitely is a date column.
c
christoph
06/27/2022, 9:55 PMFWIW, I tried specifying
"format":"date"timestampe
eric_boucher
06/28/2022, 7:00 AMWhich target are you using? Happy to review PRs on https://github.com/datamill-co/target-postgres
w
will_johnson
06/28/2022, 2:50 PMI am also using the pipelinewise postgres target
will_johnson
07/11/2022, 4:31 PMI am indexing this "data_date" column using schema mapping so having it be a timestamp rather than date is further an issue.
will_johnson
07/11/2022, 4:50 PMThis thread seems to indicate that the best practice is to convert date to datetime which is a headscratcher. I don't understand the logic of that.
https://meltano.slack.com/archives/C01UW1W4D5Y/p1632114774010300
will_johnson
07/11/2022, 5:13 PM@eric_boucher It appears that the known limitations also are only datetime (rather than date) for the Datamill target-postgres. But that is the second half of the issue. First is on the tap-postgres side.
e
eric_boucher
07/11/2022, 5:28 PMThanks for the update. Can't help you on the tap side unfortunately but happy to review a PR on the target side or an issue to explain what should happen :)
c
christoph
07/11/2022, 9:47 PMThis thread seems to indicate that the best practice is to convert date to datetime which is a headscratcherAJ and Edgar are recommending exactly the opposite in that thread you reference.
w
will_johnson
07/12/2022, 12:27 AM@christoph I did see that but it didn't find anything that addresses that in practice.
@eric_boucher I can definitely do an issue and will see if I can find time this week for a pull request. Haven't looked at any of this code before but happy to help how I can if it results in a better tap. It doesn't appear the pipelinewise taps are getting updates recently.