I'm trying to do some slow migration from a MS SQL Server 2008 R2 to a PostgreSQL 9.5 + PostGIS.
I created a FDW in PostgreSQL to access my MSSQL data and I imported most of my tables, but a few didn't make It.
Here is an exemple:
CREATE FOREIGN TABLE ms_table_name(
col1 varchar (64), col2 date, geometry geometry
) SERVER mssql_db_server OPTIONS (query 'SELECT col1, col2, cast(geometry.STAsText() as varchar(max)) "geometry" FROM ms_table_name',
row_estimate_method 'showplan_all');
CREATE TABLE pg_table_name AS (select * from ms_table_name);
ALTER TABLE pg_table_name ADD oid serial PRIMARY KEY;
CREATE INDEX pg_table_name_gix ON pg_table_name USING GIST (GEOMETRY);
I can't use binary because it returns two invalid characters, that's why I need to convert it to text.
As I said, it works for most geometries but for long/big geometries that have a large number of coordinates, it gets truncated.
ERRO: parse error - invalid geometry SQL state: XX000 Hint: "...65032851836 8268588.7680116147, 6129" <-- parse error at position 64513 within geometry
This geometry has 138280 chars, I confirmed all chars are being sent from MSSQL but PosgreSQL only reads 64512. I tried varchar, text, but still I get this 64512 char limit!
How do I work around it?
