BIT Data Type - SQL Server to PostgreSQL Migration

In SQL Server, the BIT data type stores 0, 1 or NULL values.

In PostgreSQL, you can use the BOOLEAN data type but you need to cast 0 and 1 values, and output representation for BOOLEAN is t and f.

SQL Server:

  -- Sample table 
  CREATE TABLE specs 
  (
     data BIT
  );
 
  -- Inserting sample values
  INSERT INTO specs VALUES (0);
  INSERT INTO specs VALUES (1);
 
  -- SQL Server allows using 'FALSE' and 'TRUE' string literals
  INSERT INTO specs VALUES ('FALSE');
  INSERT INTO specs VALUES ('TRUE');
 
  SELECT * FROM specs;

Result:

data

0

1

0

1

PostgreSQL:

    -- Sample table 
  CREATE TABLE specs 
  (
     data BOOLEAN
  );
 
  -- Inserting sample values
  INSERT INTO specs VALUES (0);
  /* ERROR:  column "data" is of type boolean but expression is of type integer */
 
  INSERT INTO specs VALUES (0::BOOLEAN);
  /* Ok */
 
  INSERT INTO specs VALUES (1);
  /* ERROR:  column "data" is of type boolean but expression is of type integer */
 
  INSERT INTO specs VALUES (1::BOOLEAN);
  /* Ok */
 
  -- PostgreSQL allows using 'FALSE' and 'TRUE' string literals as well as FALSE and TRUE keywords
  INSERT INTO specs VALUES ('FALSE');
  INSERT INTO specs VALUES (TRUE);
 
  SELECT * FROM specs;

Result:

data

t

f

t

f

Note that the output result in PostgreSQL contains values t and f rather than 1 and 0.

For more information, see SQL Server to PostgreSQL Migration.