c# - Postgres : ArgumentException for bit_string(1) column in datatable

Question

我正在尝试通过简单的选择来填充数据表:

 string queryTableData = string.Format("select * from {0}", fullTableName);

 var command = new NpgsqlCommand(queryTableData, connection);
 DataTable table = new DataTable();
 table.Load(command.ExecuteReader());

但是如果表有位列:

column_name: column1
data_type: bit
character_maximum_length: 1
udt_name: bit

我得到 ArgumentException:

Type of value has a mismatch with column typeCouldn't store <True> in column1 Column.  Expected type is BitString.

有什么解决办法吗？我在谷歌搜索解决方案中失败了。

Answer 1

在 PostgreSQL 方面，true 是一种数据类型，bit 是另一种数据类型。他们不兼容。

值 true 是 bool 值；你不能将 bool 值转换为 bit。

select cast(true as bit)

ERROR:  cannot cast type boolean to bit

You also can't cast a bit as Boolean.

select cast(b'1' as boolean);

ERROR:  cannot cast type bit to boolean

If you must use bit columns as if they were Boolean, use b'0' and b'1'. But you'll have to use the same values in comparisons. Expressions like where column1 or where column1 = true won't work. You have to use expressions like where column1 = b'1'.

You can cast integers and strings as Boolean. These all return the Boolean value true.

select cast(1 as boolean);
select cast(42 as boolean);
select cast('1' as boolean);  -- But cast('42' as boolean) throws a syntax error
select cast('y' as boolean);
select cast('t' as boolean);
select cast('true' as boolean);
select cast('yes' as boolean);

---

如果我对你的错误理解正确，你在数据库端输入了 Boolean，在 DataTable 端输入了 bit(n)。如果您无法以某种方式使这些类型兼容(即不同)，则可以使用 CASE 表达式在 SQL 中转换类型。不过，我不确定这是否适用于您的代码。

select case when column1 = true then b'1'
            when column1 = false then b'0'
            -- Think about what to do with NULL.
       end as boolean_to_bit
from your_table_name;

boolean_to_bit"bit"--10(null)