I have a postgres database linked to SQL Server. I have found you can use OpenQuery to query postgres from the Sql Server side, but one caveat I have found is that when creating views all fields are converted to char despite the actual data type from postgres. For example, if you look at my query below,
userid is an int, and
userlogindate & userlastlogindate are date fields in postgres but when the view is created in Sql Server if I expand the plus sign beside the view, and expand the plus sign beside columns, all of the columns are listed as char()?
What do I need to do in order for the Sql Server view to keep the same field type as what postgres has them set-up to be? Cast/Convert each one?
SELECT * FROM OPENQUERY(LinkedServer, 'Select userid As "userid" ,userlogin As "userlogin" ,userlogindate As "userlogindate" ,userlastlogindate As "Lastlogindate" From syscatalog')