c# - 选择表的列名和类型

Question

我在写一个访问PostgreSQL数据库的c程序。
因为我的程序应该是通用的，所以我需要动态地获取列名和相应的数据类型。
我的代码如下：

OdbcCommand command = new OdbcCommand("SELECT column_name, data_type FROM information_schema.columns", connection);
OdbcDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\dummy5.txt"))
{
    while (reader.Read() == true)
    {                    
        s = reader.GetString(0) + "\t" + reader.GetString(1);
        file.Write(s + "\n");
    }                
}

我得到的结果是这样的：
CúPK 12
库里12
Cú版次-5
创建93
C_主页内容12
编号12
列名正确，但相应的数据类型用数字表示。这些数字是一致的（12是一个字符串，-5长，93日期时间）。
因为我对C和SQL都是新手，所以我真的不知道错误到底在哪里。查询是否已经出错，或者与C#中SQL数据类型的表示有关？

Answer 1

示例表：

create table test (
    id serial primary key,
    str text not null,
    counter int default 0,
    val numeric);

使用 pg_attribute：

select
    attnum,
    attname, 
    format_type(atttypid, atttypmod) as atttype
from pg_attribute
where attrelid = 'test'::regclass
and attnum > 0 and not attisdropped
order by attnum;

 attnum | attname | atttype 
--------+---------+---------
      1 | id      | integer
      2 | str     | text
      3 | counter | integer
      4 | val     | numeric
(4 rows)

使用 pg_attrdef获取默认表达式：

select
    attnum,
    attname, 
    format_type(atttypid, atttypmod) as atttype,
    pg_get_expr(adbin, adrelid) as defexpr,
    attnotnull
from pg_attribute
left join pg_attrdef
on adrelid = attrelid and adnum = attnum and atthasdef
where attrelid = 'test'::regclass
and attnum > 0 and not attisdropped
order by attnum;

 attnum | attname | atttype |             defexpr              | attnotnull 
--------+---------+---------+----------------------------------+------------
      1 | id      | integer | nextval('test_id_seq'::regclass) | t
      2 | str     | text    |                                  | t
      3 | counter | integer | 0                                | f
      4 | val     | numeric |                                  | f
(4 rows)