Search code examples
sqlsql-serverpostgresqldatabase-migrationcsv-import

Postgres copy error extra data after last expected column from SQL Server BCP file

I am migrating a database from SQL Server 2016 hosted on Windows to Postgres 11 hosted on Debian.

I am exporting data with the BCP utility from SQL Server 2016 and am importing it in Postgres 11 with the COPY command.

For a lot of tables it works, but for some, I keep getting the "extra data after last expected column" error, even if my file contains the same amount of columns. It seems that COPY command has trouble with lines that contains empty strings, showned as "NUL" in Notepad++.

Here is the definition of my table in SQL Server. (table and column names changed)

Create table test (
    TypeId  int not null,
    Name    nvarchar(50) not null,
    License nvarchar(50) not null,
    LastChanged timestamp not null,
    Id1 uniqueidentifier not null,
    Id2 uniqueidentifier not null,
    DescriptionCol  nvarchar(256) not null default '',
    ConditionCol    bit not null default 0,
    ConditionCol2   bit not null default 0,
    ConditionCol3   bit not null default 1,
    DescriptionCol2 nvarchar (2) not null default ''
)

And here is the table definition in Postgres.

CREATE TABLE test (
    typeid integer NOT NULL,
    name citext COLLATE pg_catalog."default" NOT NULL,
    license citext COLLATE pg_catalog."default" NOT NULL,
    lastchanged bytea NOT NULL,
    id1 uuid NOT NULL,
    id2 uuid NOT NULL DEFAULT uuid_generate_v4(),
    descriptioncol text COLLATE pg_catalog."default" NOT NULL DEFAULT ''::text,
    conditioncol boolean NOT NULL DEFAULT false,
    conditioncol2 boolean NOT NULL DEFAULT false,
    conditioncol3 boolean NOT NULL DEFAULT true,
    descriptioncol2 text COLLATE pg_catalog."default" NOT NULL
)

I extract the data that way: 

bcp Database.Schema.test out E:\MyFile.dat -S ServerName -U User -P Password -a65535 -c -C 65001

And then I connect to the remote Postgres server and import data that way:

\copy Schema.test FROM 'E:\MyFile.dat' (DELIMITER E'\t', FORMAT CSV, NULL '', ENCODING 'UTF8');`

Now if I open the file that was generated in Notepad++, I will see "NUL" characters and that seems to be the problem that the COPY command cannot take.

File in notepad

If I try to put some data in the "NUL" caracter on the first row, then the copy command gives me the "extra data after last expected column" on the third row instead of the first row. I cannot edit the file and replace the "NUL" character with something else as I have hundreds of tables to migrate with some very big tables.

I need to either specify an option to SQL Server BCP utility or to Postgres COPY command in order to make this work.

Solution

  • As it is stated by @Tometzky,

    bcp utility represents an empty string as a null and a null string as an empty string.

    this explains the cause of unwanted behavior.

    As an alternative to , you may consider to use (Microsoft SQL Server Integration Services) for this manner. It is easy to use and has wide range of compatibility between DBMS Systems.