使用Haskell的postgres-simple库进行多行插入时，如何解决“operator does not exist: text = uuid”错误？

Question

我正在使用postgres-simple库向eligible_class_passes表中插入数据。该表本质上是表示多对多关系的连接表。

我正在使用postgres-simple中的executeMany函数进行多行插入操作。

updateEligibleClassPasses :: Connection -> Text -> Text -> [Text] -> IO Int64
updateEligibleClassPasses conn tenantId classTypeId classPassTypeIds =
  withTransaction conn $ do
    executeMany
      simpleConn
      [sql| 
      INSERT INTO eligible_class_passes (class_type_id, class_pass_type_id)
      SELECT upd.class_type_id::uuid, upd.class_pass_type_id::uuid
      FROM (VALUES (?, ?, ?)) as upd(class_type_id, class_pass_type_id, tenant_id)
      INNER JOIN class_types AS ct
      ON upd.class_type_id::uuid = ct.id
      INNER JOIN subscription_types AS st
      ON class_pass_type_id::uuid = st.id
      WHERE ct.tenant_id = upd.tenant_id::uuid AND st.tenant_id = upd.tenant_id::uuid
      |]
      params
 where
  addParams classPassTypeId = (classTypeId, classPassTypeId, tenantId)
  params = addParams <$> classPassTypeIds

当使用正确的参数执行此函数时，我会得到以下的运行时错误。

SqlError {sqlState = "42883", sqlExecStatus = FatalError, sqlErrorMsg = "operator does not exist: text = uuid", sqlErrorDetail = "", sqlErrorHint = "No operator matches the given name and argument type(s). You might need to add explicit type casts."}

然而，当将查询翻译成SQL时，不带参数替换符（?），在psql中执行时，该查询可以正常工作。

INSERT INTO eligible_class_passes (class_type_id, class_pass_type_id)
SELECT upd.class_type_id::uuid, upd.class_pass_type_id::uuid
FROM (VALUES ('863cb5ea-7a68-41d5-ab9f-5344605de500', 'e9195660-fd48-4fa2-9847-65a0ad323bd5', '597e6d7a-092a-49be-a2ea-11e8d85d8f82')) as upd(class_type_id, class_pass_type_id, tenant_id)
INNER JOIN class_types AS ct
ON upd.class_type_id::uuid = ct.id
INNER JOIN subscription_types AS st
ON class_pass_type_id::uuid = st.id
WHERE ct.tenant_id = upd.tenant_id::uuid AND st.tenant_id = upd.tenant_id::uuid;        

我的架构如下：

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE tenants (
  id UUID NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY, 
  name text NOT NULL UNIQUE, 
  email text NOT NULL UNIQUE, 
  created_at timestamp with time zone NOT NULL default now(), 
  updated_at timestamp with time zone NOT NULL default now()
);

CREATE TABLE class_types (
  id UUID NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY, 
  FOREIGN KEY (tenant_id) REFERENCES tenants (id), 
  created_at timestamp with time zone NOT NULL default now(), 
  updated_at timestamp with time zone NOT NULL default now()
);

CREATE TABLE class_pass_types (
  id UUID NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY, 
  name TEXT NOT NULL, 
  tenant_id UUID NOT NULL, 
  price Int NOT NULL, 
  created_at timestamp with time zone NOT NULL default now(), 
  updated_at timestamp with time zone NOT NULL default now(), 
  FOREIGN KEY (tenant_id) REFERENCES tenants (id)
);

-- Many to many join through table.
-- Expresses class pass type redeemability against class types.
CREATE TABLE eligible_class_passes (
  class_type_id UUID, 
  class_pass_type_id UUID, 
  created_at timestamp with time zone NOT NULL default now(), 
  updated_at timestamp with time zone NOT NULL default now(), 
  FOREIGN KEY (class_type_id) REFERENCES class_types (id) ON DELETE CASCADE, 
  FOREIGN KEY (class_pass_type_id) REFERENCES class_pass_types (id) ON DELETE CASCADE, 
  PRIMARY KEY (
    class_type_id, class_pass_type_id
  )
);

- therewillbecode

Answer 1

为了帮助您调试问题，请使用formatQuery函数，这样您就可以看到postgresql-simple向服务器发送的最终查询类型是什么。

此外，我建议您使用来自uuid-types包的UUID类型，而不是Text类型来存储uuid。使用Text很可能会隐藏一些问题，而使用formatQuery后您将能够看到这些问题。

- phadej