Exporting bit data type data from SQL Server to PostgreSQL

Question

I have table tbl_mak in SQL Server which contain's the following data:

Table:

CREATE TABLE tbl_mak
(
   col_bit bit 
);

insert into tbl_mak values(1);
insert into tbl_mak values(0);

After exporting into Flat File (.txt) i got the following value's:

col_bit
True
False

And I have the following table in PostgreSQL 9.5:

CREATE TABLE tbl_mak
(
   col_bit bit 
);

Now i want to import the exported data from SQL Server into PostgreSQL.

Script:

\COPY tbl_mak FROM 'D:\mak.txt'

Getting an error:

ERROR:  bit string length 5 does not match type bit(1)

Answer 1

Create a table like below; Use boolean type for the field

CREATE TABLE tbl_mak
(
   col_bit boolean
);

and import data from text file like below

COPY tbl_mak FROM 'D:\mak.txt' WITH CSV HEADER DELIMITER AS ' '

Answer 2

In Postgresql the bit datatype is different from SQL-Server in postgres it is

bit [ (n) ]   fixed-length bit string

but in SQL-server its a

integer data type that can take a value of 1, 0, or NULL

so in the file its TRUE/FALSE and when you are trying to import the data in postgresql its getting an error for more please go through the link

Answer 3

From Postgres docs

Writing bit without a length is equivalent to bit(1)

So mention the length

CREATE TABLE tbl_mak
(
   col_bit bit(5)
);

I think you can use Boolean Type instead of BIT

Valid literal values for the "true" state are:

TRUE
't'
'true'
'y'
'yes'
'1'

For the "false" state, the following values can be used:

FALSE
'f'
'false'
'n'
'no'
'0'