1

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')
0

I found the solution to this. Below is how I achieved such:

SELECT      
CAST(userid As Int) As userid
,CAST(userlogin As varchar(500)) As userlogin
,CAST(userlogindate as date) As userlogindate
,CAST(userlastlogindate as date) As lastlogindate
FROM OPENQUERY(LinkedServer, 
                 'Select 
                  userid As "userid"
                  ,userlogin As "userlogin"
                  ,userlogindate As "userlogindate"
                  ,userlastlogindate As "Lastlogindate"
                  From syscatalog')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.