c - libpq 只在字符串列中插入第一个字符

Question

所以我的 psql 表中有这个名为 rooms 的结构:

                                         Table "public.rooms"
         Column          |          Type          |                     Modifiers
-------------------------+------------------------+----------------------------------------------------
 id                      | integer                | not null default nextval('rooms_id_seq'::regclass)
 room_id                 | integer                |
 room_name               | character varying(100) |
 room_secret             | character varying(255) |
 room_pin                | character varying(100) |
 room_communication_type | character varying(30)  |
Indexes:
    "rooms_pkey" PRIMARY KEY, btree (id)
    "index_rooms_on_room_id" UNIQUE, btree (room_id)
    "index_rooms_on_room_name" btree (room_name)
    "index_rooms_on_room_secret" btree (room_secret)

当我尝试从我的 C 程序中执行和插入语句时，我得到了这些结果:

janus_audiobridge=# SELECT * FROM rooms;
 id | room_id | room_name | room_secret | room_pin | room_communication_type
----+---------+-----------+-------------+----------+-------------------------
  1 |  111128 | I         | L           |          | r
  3 |  111129 | C         | c           |          | r
  4 |  111130 | 4         | b           |          | r
  6 |  111131 | 5         | b           |          | r
(4 rows)

注意 room_name、room_secret 和 room_communication_type 只包含 1 个字符，我想要插入的原始数据是完整的文本句子。

这是我的 C 代码:

  const char *paramValues[5];
  paramValues[0] = "111131";
  paramValues[1] = room->room_name;
  paramValues[2] = room->room_secret;
  paramValues[3] = room->room_pin;
  paramValues[4] = room->room_communication_type;


  JANUS_LOG(LOG_WARN, "name: %s", paramValues[1]);

  JANUS_LOG(LOG_ERR, "44444\n\n\n");
  PGresult *res = PQexecParams(conn, query,
    5,       /* one param */
    NULL,    /* let the backend deduce param type */
    paramValues,
    NULL,    /* don't need param lengths since text */
    NULL,    /* default to all text params */
    0);      /* ask for text results */

当我运行 INSERT 时，JANUS_LOG 行返回正确的名称，但仅插入第一个字符。

如何使用 libpq 将全文插入 postgres 数据库？

谢谢!

编辑:我的查询字符串:

gchar *query = "INSERT INTO rooms(room_id, room_name, room_secret, room_pin, room_communication_type) " 
"VALUES($1::int, $2::char, $3::char, $4::char, $5::char);";

Answer 1

在 INSERT 语句中移除对 char 的强制转换(与 character(1) 相同)。然后一切都应该正常工作。转换为 int 也是不必要的。

您可以在 psql 中看到这个简单实验会发生什么:

SELECT 'hello'::char;
 bpchar
--------
 h
(1 row)

转换为 character varying 会起作用，但没有必要显式转换，因为在 INSERT 期间无论如何都会执行一个赋值转换 .

作为the documentation状态:

A cast applied to an unadorned string literal represents the initial assignment of a type to a literal constant value, and so it will succeed for any type (if the contents of the string literal are acceptable input syntax for the data type).