Datetime gets converted to Timestamp with timezone
bit gets converted to character(1)
decimal gets converted to character(40)
I was able to get the datetime and bit get converted correctly by adding the mapping explicitly to the 'msdb.dbo.MSDatatype_mappings' table by using the following stored procedures.
- Code: Select all
exec dbo.sp_add_datatype_mapping 'PostgreSQL', 'datetime', 'timestamp without time zone', 26, 0, 1
exec dbo.sp_add_datatype_mapping 'PostgreSQL', 'bit', 'boolean', 1, 0, 1
For the bit to boolean conversion, I also had to ensure that Postgres did an implicit cast from int to boolean as SQL Server treats bit as 0 & 1 and Postgres treats it as true and false. So, I did the following.
- Code: Select all
UPDATE pg_cast SET castcontext = 'i' WHERE oid IN (
SELECT c.oid
FROM pg_cast c
inner join pg_type src ON src.oid = c.castsource
inner join pg_type tgt ON tgt.oid = c.casttarget
WHERE src.typname LIKE 'int%' AND tgt.typname LIKE 'bool%'
)
The only datatype I still have an issue with is decimal to numeric conversion. By default the datatype created on Postgres is character. I tried the same approach as above, by forcing it to both numeric and to decimal.
- Code: Select all
exec dbo.sp_add_datatype_mapping 'PostgreSQL', 'decimal', 'numeric, 255, 3, 1
I had some success where in the table being created in Postgres had the correct data type (numeric (precision, scale)), however, when the distributor tries to copy the data, it throws the following error:
"Invalid Parameters"
Any help on solving this would be greatly appreciated.
