We are trying to pull data from external source (mssql) to postgres. But when i checked for invoicedate column entries are getting blank at the same time mssql is showing invoicedate values for those entries.
ie We tried following query on both the DBMS:
When query executed in SQL Server:
select * from tablename where salesorder='168490'
getting 12 rows where invoicedate column is '2015-10-26 00:00:00.000'
But same query is executed on Postgres
select "InvoceDt" from tablename where salesorder='168490'
Getting 12 rows where the column invoicedate is null.
Question is why?
Postgres InvoiceDt column is coming null rather than we can see that SQL Server is showing appropriate data values.
Why is the data different between SQL Server and Postgres for this particular column?
Vicps, you aren't using Postgres and that is why a_horse_with_no_name is having such a hard time trying to understand your question. You are using Pivotal HDB (formally called HAWQ). HAWQ is now associated with the incubator project, "Apache HAWQ" and the commercial version is "Pivotal HDB".
Pivotal HDB is a fork of Pivotal Greenplum database which is a fork of PostgreSQL 8.2. It has many similarities to Postgres but it is most definitely not Postgres.
You are also using Spring-XD to move the data from SQL Server to HDFS which is critical in understanding what the true problem is.
You provided this example:
CREATE TABLE tablename ( "InvoiceDt" timestamp )
LOCATION ('pxf://hostname/path/to/hdfs/?profile=HdfsTextSimple')
FORMAT 'csv' ( delimiter '^' null 'null' quote '~');
Your file only has one column in it? How is this possible? Above, you mention the salesorder column. Secondly, have you tried looking at the file written by Spring-XD?
hdfs dfs -cat hdfs://hostname:8020/path/to/hdfs | grep 168490
I bet you have an extra delimiter, null character, or an escape character in the data which is causing the problem. You also may want to tag your question with spring-xd too.